[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access lookup function based on a field value

Posted on 2009-02-19
Medium Priority
Last Modified: 2012-05-06
I am a very very very novice Access user. I have a simple database with one form and two tables. It stores contract information for a limo rental company. What I am trying to do is get Access to populate the address and city fields on the form based on what the user enters in the location field.

My location table has all the names and address info for wedding and banquet halls. When a user fills out the contract form and types in "White Cliffs" in the location field, I want Access to do a lookup to the location table, find White Cliffs, and grab the address, city, etc and populate the associated fields on the contract form, thereby saving key strokes for the user filling out the form/contract.

I hope this makes sense.
Thanks much Experts. Remember, I am a newbie...:-)
Question by:bobrossi56
  • 7
  • 5
LVL 61

Expert Comment

ID: 23688500
<lookup function>

Lookup is pretty close.  It is actually DLookup.

Set each of your control sources to something like this:

= DLookup("City" ,"tblLocation" ,"Location = '" & me.txtLocation & "'")
= DLookup("State" ,"tblLocation" ,"Location = '" & me.txtLocation & "'")
= DLookup("Zip" ,"tblLocation" ,"Location = '" & me.txtLocation & "'")

Include the = signs, and replace field and table names with the actual names from your database
LVL 61

Expert Comment

ID: 23688512
Just a note... my previous post will populate those textboxes for informational purposes - not for editing.

Author Comment

ID: 23691064
OK, I'm 99% there. But I am confused with the last part of the DLOOKUP, what does the me.txtLocation specify?
Here are the details to my databse: The wedding location table is called tblLocation, it contains 3 fields, LocName, LocStreet, LocCity. (White Cliffs, 1 Main St, Boston)

My form is named contract. It has one field named Location. When I type in White Cliffs in the Location field of the form, it should find that in the tblLocation.LocName column, and put the LocStreet in the field next to it on the form, where I have put in this control:
= DLookup("LocStreet" ,"tblLocation" ,"Location = '" & me.txtLocation & "'")

I'm just confused as to what field names go where and the end part of the DLOOKUP as it is returning #Name?
thx millions
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

LVL 61

Expert Comment

ID: 23693030
Hi -

You will get #Name if any of the components of this command are unrecognized, so you do need to fill them in accurately.

The syntax of DLookup is generally:

DLookup("FieldName", "TableOrQueryName", "Criteria(Optional)")

The Criteria is a string that should look like the WHERE clause of a Query, without the "WHERE" keyword.

<what does the me.txtLocation specify? >
That is my own hypothetical name for the textbox on your form that the user enters the Location into. The "me." prefix is shorthand notation for the current form. The "txt" tag is a standard naming convention to indicate a textbox.

<= DLookup("LocStreet" ,"tblLocation" ,"Location = '" & me.txtLocation & "'")>

It looks like you've got it mostly figured out.

All you need to do is:
-- replace "me.txtLocation" with your actual textbox name (Me.YourActualTextboxName)
-- replace "Location " with your actual field name (LocName)

= DLookup("LocStreet" ,"tblLocation" ,"LocName = '" & Me.YourActualTextboxName & "'")
LVL 61

Expert Comment

ID: 23693069
As an aside, the naming conventions I mentioned are the Reddick VBA conventions (RVBA). Most of the Access Experts here adhere to these standards.

Rules specific to Access can be found midway down the page:

Author Comment

ID: 23697792
OK, I give up mbizup. I inserted your dlookup line in, changed the field to the field name on my form and it still returns the #Name error. I have attached both the access front end and back end in hopes that you can easily see what I am doing wrong, and please feel free to break my cookies :-)
I appreciate your help very much...
LVL 61

Expert Comment

ID: 23698321
I'm very sorry - that was completely my oversight.

"Me." is a Visual Basic prefix that works in code, but not in property sheets or queries.  I wasn't thinking clearly.

In a property sheet (eg: the Control Source property), everything is the same except that you will have to leave off the "Me." prefix:

= DLookup("LocStreet" ,"tblLocation" ,"LocName = '" & YourActualTextboxName & "'")

In your sample:
=DLookUp("LocStreet","tblLocation","LocName = '" & [Location] & "'")

I noticed that you did not have a locations table in your sample db.  If you do have a tblLocation on your end with LocName as a field name, this will do the trick (I tested it on my end by adding this table).

Also please scrutinize your contacts table and let me know if this is junk or real data - if you inadvertently uploaded sensitive info, I double as Page Editor here and can remove the file for you.

Author Comment

ID: 23699726
All junk data for testing purposes.
Weird thing is, my db1_be.mdb that I uploaded DOES have a tblLocation table, I wonder why you are not seeing it.
I also put in the control field =DLookUp("LocStreet","tblLocation","LocName = '" & [Location] & "'") and it still returns an error. DO I have to have a join between the tblLocation table and the contracts table?
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 23700519

< I wonder why you are not seeing it.>
I didn't actually look in the back-end.

In the sample you posted, tblLocation is not linked to the front-end, only the contacts table. Any table that your front-end uses needs to be either local or linked from a back-end. When you create a new back-end table, this linking is not automatic.

In a nutshell, if you open your front-end and look at the tables that are present, those are the only tables that are usable in that database. If a table you need is not there, you need to either create it locally or link to it.

This is what you need to do:

Access 2007:
- Right-click one of the tables in the Navigation Pane
- Click Import -> Access Database
- Select the "Link to Data" radio button
- Browse to the back-end that contains the table you need and click OK
- Select that table and click "OK"

Access 2003 and earlier:
- In the database window, select Tables
- Right-click the database window and select Link Tables
- Browse to the back-end that contains the table you need
- Select that table and click "OK"

Author Comment

ID: 23701570
BINGO !!!! That was it. I had never SPLIT an Access database before, and it was recommended I do that by another expert in another posting because I want to eventually run this on a server with 3 people using the contract form. I added the table to the BE, but never linked it to the front end and that was causing all these errors. It works now, and I have added other dlookup fields to the form that work great too.

I appreciate your patience and great genius help solving this problem. Perhaps as I go down the Access path I will have other questions posted and we can work together again.

Thanks so much,

Author Closing Comment

ID: 31549094
Great help, learned a lot, thanks so much.
LVL 61

Expert Comment

ID: 23701632
Glad I could help out!

< I had never SPLIT an Access database before, and it was recommended >
That is a very good idea.
It is essential for multi-user databases.
It is even a good idea for deployed single-user databases.  It facilitates maintaining and developing the database UI without interfering with the user's data.

<I have added other dlookup fields to the form that work great too.>
If you worked out the exact syntax on your own, it sounds like you are doing a great job.   :)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question