DLookup Stops Working (#Error/Calculating)

[JoJoRly]

This happens from time to time during form design. My DLookup fields, which were previously working, suddenly display "#Error" and the message "Calculating ..." appears in the lower left of the screen. I try several things to fix it: closing the form/database sometimes works; sometimes I delete the fields and start again.

Here is an example of one of my lookups. I have four similar DLookups on my form. Can anyone explain what causes this to happen and offer some suggestions for fixing it or preventing it from happening in the first place? Much appreciated!

=DLookUp("[Xref_Plan Code]![Plan Description]","Xref_Plan Code","[Find Billing Issues to Research]![PLAN-CODE] =[Xref_Plan Code]![PLAN-CODE]")

 

[PHV]

What is "Find Billing Issues to Research" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JoJoRly]

[Find Billing Issues to Research]![Plan-Code]" is the reference to the Query Name and field from the query on which this form is based. When the Plan Code on the Query matches the Plan Code from the XREF table, I display the corresponding description from the XREF table.

 

[PHV]

And what about something like this ?
=DLookUp("[Plan Description]", "[Xref_Plan Code]","[PLAN-CODE]=[tt]'"[/tt] & Me![PLAN-CODE] & [tt]"'"[/tt])

If PLAN-CODE is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JoJoRly]

PHV - Thank you for your response. I did try it but my #Error changed to a #Name error. In the end, I deleted and started over and took some other precautions:
1. I renamed each control that I added to the form from the Access Default name to a new name with each dlookup control I was adding.
2. I used a slightly different formula to address NULL field values:

=Nz(DLookUp("[XWALK_Plan Codes]![Pres Plan Code Desc]","XWALK_PLAN CODES","[Find Billing Issues to Research]![PLAN-CODE] = [XWALK_Plan Codes]![Pres Plan Code]"),"N/A")

3. I made a copy of my database as all lookup fields on the form are currently working.

What is strange is that the dlookup fields were all working before I got the #error the first time. Then something happened where the lookup fields no longer worked (and I was not doing any changes to those fields either) and I got that "flickering" message in the lower left corner "calculating..." as if Access had gotten stuck or something as it tried to do the lookups. Like I said, this has happened inexplicably before in other databases whre my lookup fields just stop working and I get the "calculating" message. I hope the precautions that I took will help keep things "clear" for my form so it doesn't get confused again and stop working.

I appreciate your response.

Jo