I often use DLookup on forms, to display a value in a recordset other than the one the form is bound to.

I sometimes find that it randomly doesn't work - by which I mean that generally it will work, then maybe I switch to design view, make some completely unrelated change (like centering some text in a different control, for example) and when I switch back to form view, it isn't working.

Anyone got any ideas? It's very annoying!

You've self-diagnosed this to a certain extent: "I switch to design view, make some completely unrelated change "
If the DLookup depends on other values in the form, it may not be able to retrieve them correctly in such cases.

However, since we can't see the form and how it works, it's not easy to do more than guess what interactions are happening.

Thanks for the suggestion, but it really is a completely unrelated change, like centering text in a label, adding a line, or something really unrelated like that.

I've had this behaviour on a few occasions, and the general situation is that I have a control (usually not visible) on the form, holding a PK numerical value, which I might refer to in the DLookup, and this I don't mess with.

Example: =DLookup("fldFieldName","tblTableName","fldPKField = " & Me.txtPKFieldControlOnForm)

It sometimes happens that I open the form, everything's fine, I close it, reopen it with no design or data changes, and the DLookup isn't working. Interestingly, it doesn't report #Name or anything like that, it's just blank.

Thanks for your hep.

Hi,


You can't use the "Me" keyword in an expression like that because it only works in VBA.



Actually, your DLookup() is working; however, it is not finding anything - that's why it's blank. Try wrapping in inside an Nz() function and see what happens. For example:

=Nz(DLookup(...), "I didn't find anything.")

Just my 2 cents...

Sorry about the "Me.", it wasn't a copy/paste, I just typed that in & obviously had my brain on autopilot. It wouldn't be there in reality.

I do use the nz function where a null might appear, but in the situations where I've found this behaviour happening, I know there's data there that should be showing but isn't - like for example when closing & immediately reopening a form with no design or relevant data changes, and it was working but then it isn't...

Thanks for your suggestions.

Is it possible to make a small copy of your database, delete all private info and upload a zipped copy of this dbase? Then someone can take a look at everything, etc.

i have a form where i use a dlookup to a table other than the one that sources the form. At times, the dlookup can take a little extra time to populate. When it shows as blank is there anything in the status bar going on ?

Thanks, but I hesitate to upload a small version of this particular db, because it would mean stripping so much out that I'm not sure if that itself would cause similar problems because of missing data. I have had this issue with other dbs though, so I'll see if I can find one to take a look at.

Regarding the status bar - I must admit I have the status bar switched off, but I'll re-enable it and have a look, thanks for the suggestion.

Okay.

Have you run Compact and Repair with this Dbase? Or just tried importing everything into a new File/Database?

RAZMaddaz

Good suggestion, I have run compact/repair but that was last week - I'll do it again and see if it helps.

As for importing into a new file, so far it's just an irritation so I haven't thought about it but it's another good suggestion, thanks.

Is there any initialization variables that are setup on form open? If you switch multiple times without closing the form between design and form view, you may be losing the reference to the variable value...

Not in this case, but it's the sort of thing that can trip you up! Thanks for the suggestion.

So Compact and Repair, as well as import to a New Dbase didn't work?

My apologies for the late response, RAZMaddaz.

I have tried importing into a blank new database and as far as I can tell, it's all ok - but then it always has been an intermittant problem so maybe it's just waiting to jump out at me when I'm not looking!

While importing all the objects into the new DB though, I came across a bit of a puzzle... should I also import all the hidden & system tables? I did so, and they appeared in the new DB with a "1" appended to their names, alongside the ones that were already there. This presumably means they'll be ignored by Access and the original ones will be used, even though whatever data they contain isn't from my database, whilst the imported, renamed ones presumably do contain data that I need. Any suggestinos about how I should go about this? I'm sure I do need the ones from my original database, as there are some there that aren't there at all in the new, blank one, and not importing them would surely cause a loss of something or other?

Thanks for your help.

Hi, please ignore my last posting, I've realised I've gone off the subject with it. Instead, I've started a new thread "Importing all objects into a new, blank DB" in Access Tables + Relationships. Please take a look (here), thanks.