Dlookups

[funkygoorilla]

Hi,
I have two forms, with the same dlookup values on it. In form A, the lookups work just fine. On form B, the lookups work so inconsistently I don't know how to fix it. Is there a setting for lookups or something?
Here is my example;
=DLookUp("[First Name]","tblEmployees","[Seniority ID] = Form![SeniorityIDResult]"

I am wanting the lookup to find the first name of the employee by looking at the field called SeniorityIDResult on the current form. It will work and sometimes wont, I don't know what else to check. Could it be because I have a piece of code to clear all the fields on load? And if so, I use the same code on another form, and the Dlookups work perfectly. Any help or suggestions would be awesome!
FunkyG

[RuralGuy]

Assuming the [Seniority ID] is numeric, try:
=DLookUp("[First Name]","tblEmployees","[Seniority ID] = " & Me.[SeniorityIDResult]

[funkygoorilla]

Hi RuralGuy thanks for the reply. Yes the field is numeric, but I tried as you suggested and it still does not take/ Now I have the #NAME? error in my field.
This is what I am trying;
=DLookUp("[First Name]","tblEmployees","[Seniority ID] = " & [Me].[SeniorityIDResult])

Funkyg

[RuralGuy]

This indicates to me that there is *no* control on the current form named SeniorityIDResult.

[funkygoorilla]

Ok, so I got it. I found an exact demo of what I was trying to do on this site;
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
Thanks for the replies!
FunkyG

[boblarson]

Hi RuralGuy thanks for the reply. Yes the field is numeric, but I tried as you suggested and it still does not take/ Now I have the #NAME? error in my field.
This is what I am trying;
=DLookUp("[First Name]","tblEmployees","[Seniority ID] = " & [Me].[SeniorityIDResult])

Funkyg

And just a reminder is that ME is only good in VBA and not in control sources.

And when referencing forms you use

Forms!FormNameHere

(with the S - and not Form!)

[funkygoorilla]

Hi Bob,
Speaking of this issue. When I load the form with these Dlookups in the controls, it pops up an error in each of the fields. It still functions, but is there something I can to get that to not show error?
Thanks, FunkyG.

[boblarson]

Sounds like you have referenced something that it can't find. What is the exact message?

[funkygoorilla]

Hi,
Its the #Error. It appears right when I open the form that uses the DLookups as mentioned before. Is there like a pre setting or something I cas use. Only asking because the DLookup works as it should, but that could throw some people off if it appears right when you open the form.
Thanks, FunkyG!

[boblarson]

Post the EXACT control source formula you are using for any of the DLookups.

[funkygoorilla]

Hi Bob,
Here is the exact DLookup I am using.
=DLookUp("[First Name]","tblEmployees","[Seniority ID] = " & [Forms]![frmVehicleInformationStarting]![SeniorityIDResult])
When the form loads, it displays #Error in the SeniorityIDResult field. It does disappear once you enter anything in the form, but I would like to see if it is possible to get it to not show.

[boblarson]

Try this instead:

=IIF(Len([Forms]![frmVehicleInformationStarting]![SeniorityIDResult] & "") = 0, "",DLookUp("[First Name]","tblEmployees","[Seniority ID] = " & [Forms]![frmVehicleInformationStarting]![SeniorityIDResult]))

[funkygoorilla]

Hi Bob,
Thanks so much for the reply. It worked like a charm. However I am not sure what the control is doing. Is it basically an IF statement saying that if the SeniorityIDResult field is empty, then to lookup the value in the tblEmployees?
Either way, thats what I needed. Thanks again! FunkyG