how to auto populate a field from another table using dlookup

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I can get my form to auto populate the City and State, but
not with the correct info. It pulls the first City and
State listed from the Zipcodes table for every zipcode you
choose in the form. Can you check my code??


zipcodes
zip
state
city

customer_tracking
fname
lname
address
city
state
zip

(The following code is used by the 'zip' field under
the 'AfterUpdate' control in a form
called 'customer_tracking'.)

Private Sub zip_AfterUpdate()
Dim varState, varCity As Variant
varState = DLookup("State", "zipcodes", "zip =[Zip] ")
varCity = DLookup("City", "zipcodes", "zip =[Zip] ")
If (Not IsNull(varState)) Then Me![state] = varState
If (Not IsNull(varCity)) Then Me![city] = varCity
End Sub
 
Your DLookups are telling the function to return the first record found where
the ZIP in the row matches the ZIP in the row.

Try modifying them to something like:
DLookup("State", "zipcodes", "zip = '" & Me.Zip &"'"

That should build a "Where" clause with the current value of the Zip control.

So you end up with a clause that looks like

Zip = '21045'
instead of
Zip = [Zip]
 
I copied and pasted the line you've suggested - perharps I
left something out, because it didn't take.

I don't want to type in all the variables within my script
(23454, 60612, 90210, 12345, etc). That could end up
being hundreds of zip codes & cities.

I want to maintain a seperate table of zipcodes, and then
have my form pull the data from the zipcodes table.

-----Original Message-----
Your DLookups are telling the function to return the first record found where
the ZIP in the row matches the ZIP in the row.

Try modifying them to something like:
DLookup("State", "zipcodes", "zip = '" & Me.Zip &"'"

That should build a "Where" clause with the current value of the Zip control.

So you end up with a clause that looks like

Zip = '21045'
instead of
Zip = [Zip]

I can get my form to auto populate the City and State, but
not with the correct info. It pulls the first City and
State listed from the Zipcodes table for every zipcode you
choose in the form. Can you check my code??


zipcodes
zip
state
city

customer_tracking
fname
lname
address
city
state
zip

(The following code is used by the 'zip' field under
the 'AfterUpdate' control in a form
called 'customer_tracking'.)

Private Sub zip_AfterUpdate()
Dim varState, varCity As Variant
varState = DLookup("State", "zipcodes", "zip = [Zip] ")
varCity = DLookup("City", "zipcodes", "zip =[Zip] ")
If (Not IsNull(varState)) Then Me![state] = varState
If (Not IsNull(varCity)) Then Me![city] = varCity
End Sub
Click to expand...
.
Click to expand...
 
Try replacing all your code with this:

Me!City = DLookup("City", "zipcodes", "zip = '" & Forms!NameOfForm!Zip & "'"
Me!State = DLookup("State", "zipcodes", "zip = '" & Forms!NameOfForm!Zip & "'"
 
I left off the closing parens. Sorry.

varState = DLookup("State", "zipcodes", "zip = '" & Me.Zip &"'")
varCity =DLookup("State", "zipcodes", "zip = '" & Me.Zip &"'")

In the case above if the control with the zip code in it hold the value 21045
and you have that value in the Zip field of your table you will get
VarState set to "Md" and varCity set to "Columbia"

You DO NOT type all the possible zip codes into the statement. They are stored
in the Zipcodes table.

Of course, if you are totally confident of the values matching up every time
between the zipcode and the city and state, you could just link to the table and
show the relevant city and state. HINT: It is not always the case that there is
a one-to-one link between a Zip Code and a locale and a state. I know of cases
where one zip code has several villages.

I copied and pasted the line you've suggested - perharps I
left something out, because it didn't take.

I don't want to type in all the variables within my script
(23454, 60612, 90210, 12345, etc). That could end up
being hundreds of zip codes & cities.

I want to maintain a seperate table of zipcodes, and then
have my form pull the data from the zipcodes table.
-----Original Message-----
Your DLookups are telling the function to return the first record found where
the ZIP in the row matches the ZIP in the row.

Try modifying them to something like:
DLookup("State", "zipcodes", "zip = '" & Me.Zip &"'"

That should build a "Where" clause with the current value of the Zip control.

So you end up with a clause that looks like

Zip = '21045'
instead of
Zip = [Zip]

I can get my form to auto populate the City and State, but
not with the correct info. It pulls the first City and
State listed from the Zipcodes table for every zipcode you
choose in the form. Can you check my code??


zipcodes
zip
state
city

customer_tracking
fname
lname
address
city
state
zip

(The following code is used by the 'zip' field under
the 'AfterUpdate' control in a form
called 'customer_tracking'.)

Private Sub zip_AfterUpdate()
Dim varState, varCity As Variant
varState = DLookup("State", "zipcodes", "zip = [Zip] ")
varCity = DLookup("City", "zipcodes", "zip =[Zip] ")
If (Not IsNull(varState)) Then Me![state] = varState
If (Not IsNull(varCity)) Then Me![city] = varCity
End Sub
Click to expand...
.
Click to expand...
Click to expand...
 
Yes, I forgot the closing parens at the end of each line too!

Steve
PC Datasheet


PC Datasheet said:
Try replacing all your code with this:

Me!City = DLookup("City", "zipcodes", "zip = '" & Forms!NameOfForm!Zip & "'"
Me!State = DLookup("State", "zipcodes", "zip = '" & Forms!NameOfForm!Zip & "'"

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Ray said:
I can get my form to auto populate the City and State, but
not with the correct info. It pulls the first City and
State listed from the Zipcodes table for every zipcode you
choose in the form. Can you check my code??


zipcodes
zip
state
city

customer_tracking
fname
lname
address
city
state
zip

(The following code is used by the 'zip' field under
the 'AfterUpdate' control in a form
called 'customer_tracking'.)

Private Sub zip_AfterUpdate()
Dim varState, varCity As Variant
varState = DLookup("State", "zipcodes", "zip =[Zip] ")
varCity = DLookup("City", "zipcodes", "zip =[Zip] ")
If (Not IsNull(varState)) Then Me![state] = varState
If (Not IsNull(varCity)) Then Me![city] = varCity
End Sub
Click to expand...
Click to expand...
 
Back
Top