Dlookup

[MJC01]

I have this command set up in the After Update Field of the Location ID on my Data Entry Form. I want it to go back to the City Table, Grab the City Name based on what is entered for a Location ID on the form and input that in the City Name Field on the same form.

Am I putting this command in the wrong place as I keep getting an error?

Thanks.


=DLookup("[City Name]", "City", "[Location ID] =" _
& Forms![Data Entry]!Location ID)

 

[jrbarnett]

Hello

The "Location ID" needs square brackets around it because there is a space in the fieldname

Forms![Data Entry]![Location ID]

Additionally, if the location ID field is stored in a text field rather than numeric, you will need to enclose the field in quote marks, either apostrophes or double quotes:

DLookup("[City Name]", "City", "[Location ID] ='" _
& Forms![Data Entry]![Location ID] & "'")

Putting it in the AfterUpdate event of this field is correct, but you need to assign the city name field to the result of the function call, so wrapping it up:

Me![City name] = DLookup("[City Name]", "City", "[Location ID] ='" _
& Forms![Data Entry]![Location ID] & "'")

John

 

[MJC01]

The Location ID Field is the Primary Key in the table and is an auto number field.

When I tried using the command it gave me a syntax error that said there is either text without quotes or I am missing an operator.

Would something like this work better if it were written in a Macro and then attach the Macro to the On Focus or On Click command?

 

[JeremyNYC]

Did you follow John's suggestion of using the brackets and not using the quotes? The quotes were to be used only if the field was text.

In addition, this is the perfect opportunity to use a combo box instead of two seemingly unrelated fields, one that requires you to know the location ID and one that "magically" updates after you enter a number in that first field.

Use the wizard if you haven't set up a combo box before. It's quite easy, and your users will be a lot happier.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[MJC01]

I have the a combo box that looks up the Location ID -They can chose a location from the drop down or begin typing and it self populates.

The problem is that there is over 150 Locations and they have trouble remember what city they are in.

I did remove the quotes because it is a number field but it still gave me a syntax error.

I have never had to write a string expression before so I am struggling with this. I wrote a 150 + line macro as a work around but that fails if they add a new location so I have been pulling my hair out trying to get this to work and I am still struggling with it.

Other then this one little problem that is turning out to be bigger then I thought, the database works great and they are using it.

Me![City name] = DLookup("[City Name]", "City", [Location ID] ='_
& Forms![Data Entry]![Location ID] & "'")


That is what I tried ... sorry I am just truly stuck on this one.

 

[PHV]

Try this:
Me![City name] = DLookup("[City Name]", "City", "[Location ID] = "_
& Forms![Data Entry]![Location ID])


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

 

[hoganator]

or this if the previous does not work, try this:

Me![City name] = DLookup("[City Name]", "City", "[Location ID] = " & "'" & Forms![Data Entry]![Location ID] & "'" )

i didn't see jbarnett put both sets of additional quotes in there. You want the string that comes out to look like this:

[location id] = '123456'

so you compile it like this:
"[location id] =" & "'" & Forms![Data Entry]![Location ID] & "'"

 

[MJC01]

Thank you all so much for your help.

It is really appreciated!