Link to home
Start Free TrialLog in
Avatar of bobrossi56
bobrossi56

asked on

Access lookup function based on a field value

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...:-)
Avatar of mbizup
mbizup
Flag of Kazakhstan image

<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
Just a note... my previous post will populate those textboxes for informational purposes - not for editing.
Avatar of bobrossi56
bobrossi56

ASKER

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
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 & "'")
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:
http://www.xoc.net/standards/rvbanc.asp
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...
Bob
db1.mdb
db1-be.mdb
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.
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?
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,
-Bob
Great help, learned a lot, thanks so much.
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.   :)