What is correct syntax to use a Dlookup in an access form?

[scrapcode]

Hi there,

I'm trying to use a Dlookup to fill a textbox on a form with the users name.

I've captured the username by using Environ("userna me") when the form loads and dumped that into a textbox.

I then want to Dlookup to relate the username to the staff list (a linked table) and bring back the persons name.

It sounds like it should be easy to do but I must be making a silly error but I can't see it. The code I'm using is:

Me.TxtSubmitter.Value = Dlookup("[Consultant]","TblLocalStaffList","[Personnel Number]"=forms![Form1]!TxtUsername")

Where:
TxtSubmitter is the text box I want the name returned to.
Consultant is the field in the table containing the values to be returned (i.e. Name).
TblLocalStaffLi st is the table containing the usernames and name.
Personnel Number is the field in the table to be checked,
and forms![Form1]!TxtUsername is the text box containing the username (that needs to be related to the table field "Personnel number".

I'm sure it's something simple and I've tried to be as complete as possible with the code and explinations but any questions just let me know and i'll do my best to clarify.

Thanks for your help.

[MikeTheBike]

Hi
If 'TxtUsername' is in the same for as 'TxtSubmitter' then perhaps this will do it

Me.TxtSubmitter.Value = Dlookup("[Consultant]","TblLocalStaffList","[Personnel Number] = '" & Me.TxtUsername & "'")

You need to concatinate the value of the variable in to the DLookup string.

Note: for text fields you need to delimite the criteria with apostropies (so the interpreter knows where the criteria statrs and ends within the complete string).

HTH


MTB

[scrapcode]

Hey, so I've updated the code to this:

[CODE]Me.TxtSubmitter .Value = DLookup("Consul tant", "LocalStaffList ", "[Personnel Number]='" & Me![txtusername] & "'")[CODE/]

And it's working better now, but it's giving a "Data missmatch in criteria expression" error message. But I know that the text box it's reading from only contains numbers and the field its comparing to also only contains numbers. Any suggestions?

Thanks

[MikeTheBike]

Hi agian

Following on, conversly Numeric Fields do not require apostophies ie

Me.TxtSubmitter.Value = DLookup("Consultant", "LocalStaffList", "[Personnel Number]= " & Me.txtusername)

??

MTB

[scrapcode]

Mike, you are a star. Thanks buddy, that worked a treat.