DLookup question 1

[JSD]

Hello,

I am working with a db that has a form and a subform. On the main form, there is an unbound control called 'employeeno'. After user updates the employee number I want their name to appear in another control on the main form also. Because 'employeeno' is unbound in this case, I tried using a DLookup to grap the employee first name after update. It returns '#Name'. I am thinking this is because the 'employee' control is unbound, correct? Maybe there is another way around. I appreciate any time and thought.

Thanks

Jeremy

 

[bhoran]

Could you supply you code for the Dlookup? Dlookup should still work on an unbound control, I assume you are using something like
=Dlookup("[employeename]", "[tblEmployees]","[employeeno]=[ctrEmployeeNo]&quot

You may have a refernecing issue if your control is that same name as your fields on the table you are looking up.

Alternaltively if the "employeeno" is already coming from a query perhaps you can modify that query to include the 1st name and simply link it the same way the employeeno control is linked.

 

[JSD]

Hello bhoran,

DLookUp("[employeefirst]";"tblemployee";"[employeeno]=Forms![frmwipin_plantone2]![employeeno]&quot

The names are assuredly correct. In addition, what I have is a form and subform. The only controls on the main form are 'employeeno' and 'employeefirst','employeelast'. I tried bringing in the names through the query but they don't refresh after update of employeeno because employeeno is unbound in this case. I can't bind employeeno because after update it begins a new record and there are required fields in the subform, so I get a 'required field' error. Any further thoughts?

Thanks

Jeremy

 

[bhoran]

So at the moment you have employeeno that refreshes after update and goes onto the next record. The employeefirst is not refreshing at this time - is that correct?

If you are using a query you could use the requery function to requery at the time you move to the next record this may save processing time if the tblemployee is large as Dlookup has to scan the entire table.

******
I notice you have semicolons in your code they should be commas, and don't forget the = sign as your textbox is looking for a formula.

=DLookUp("[employeefirst]","tblemployee","[employeeno]=Forms![frmwipin_plantone2]![employeeno]&quot

also try setting it to =Forms![frmwipin_plantone2]![employeeno] just to see if it is passing the value from your [employeeno] textbox.

If it is not try just =[employeeno] (as you have indicated they are all on the same form so the long reference is not necessary.

p.s. it is good practice to prefix your controls on the form so they don't get confused with the fields on the table i.e. ctremployeeno