Access lookup function based on a field value

Posted on 2009-02-19
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
    LVL 61

    Expert Comment

    <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

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

    Author Comment

    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
    LVL 61

    Expert Comment

    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

    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

    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

    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

    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


    < 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

    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

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

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now