Using DLookup on Form

[Meemers]

I have a database in which there are (at least) 2 tables: Companies and Cities.
Companies includes fields for addresses including City, State and Zip;
Cities just contains City, State and Zip fields. I want to be able to
automatically "populate" the State and Zip fields in Companies based on the
info in Cities. I thought I could do this via a Companies Form based on the
Companies table, using DLookup to get the State and Zip data from the Cities
table. BUT I am getting error messages when I click on the State field on the
form.
On the form, I used Event Builder for the State field as follows:
OnCLick =DLookup (["State"], "Cities", "[City] = Forms![Companies]!City")

 

[Ofer]

In the control source of the state field write
===============================================
If the city is number then use this
=DLookup ("[State]", "[Cities]", "[City] = " & [enter here City text box
Name])
==============================================
If the city is text then use this
=DLookup ("[State]", "[Cities]", "[City] = '" & [enter here City text box
Name] & "'")
================================================

 

[Meemers]

Thanks - I tried that, but when I pull up a completed form, with the state
and zip fields empty, and click on those fields it does nothing. Does this
only work for a data-entry form, and not when making changes to existing
records? (I haven't tried entering a new record yet.)?

Do I need to specify "OnClick" or something before the =DLookup expression?
(Though that didn't seem to work either.) At what point would the fields show
the relevant data from the other table?
--
Hopeful Thanks
Meemers

In the control source of the state field write
===============================================
If the city is number then use this
=DLookup ("[State]", "[Cities]", "[City] = " & [enter here City text box
Name])
==============================================
If the city is text then use this
=DLookup ("[State]", "[Cities]", "[City] = '" & [enter here City text box
Name] & "'")
================================================
--

I hope that helped
Good Luck

I have a database in which there are (at least) 2 tables: Companies and Cities.
Companies includes fields for addresses including City, State and Zip;
Cities just contains City, State and Zip fields. I want to be able to
automatically "populate" the State and Zip fields in Companies based on the
info in Cities. I thought I could do this via a Companies Form based on the
Companies table, using DLookup to get the State and Zip data from the Cities
table. BUT I am getting error messages when I click on the State field on the
form.
On the form, I used Event Builder for the State field as follows:
OnCLick =DLookup (["State"], "Cities", "[City] = Forms![Companies]!City")

 

[Ofer]

It should retrieve the data automatically when there is value in the city
field in the form.
Have you entered the Dlookup in the control source of the state field?

Thanks - I tried that, but when I pull up a completed form, with the state
and zip fields empty, and click on those fields it does nothing. Does this
only work for a data-entry form, and not when making changes to existing
records? (I haven't tried entering a new record yet.)?

Do I need to specify "OnClick" or something before the =DLookup expression?
(Though that didn't seem to work either.) At what point would the fields show
the relevant data from the other table?
--
Hopeful Thanks
Meemers

In the control source of the state field write
===============================================
If the city is number then use this
=DLookup ("[State]", "[Cities]", "[City] = " & [enter here City text box
Name])
==============================================
If the city is text then use this
=DLookup ("[State]", "[Cities]", "[City] = '" & [enter here City text box
Name] & "'")
================================================
--

I hope that helped
Good Luck

I have a database in which there are (at least) 2 tables: Companies and Cities.
Companies includes fields for addresses including City, State and Zip;
Cities just contains City, State and Zip fields. I want to be able to
automatically "populate" the State and Zip fields in Companies based on the
info in Cities. I thought I could do this via a Companies Form based on the
Companies table, using DLookup to get the State and Zip data from the Cities
table. BUT I am getting error messages when I click on the State field on the
form.
On the form, I used Event Builder for the State field as follows:
OnCLick =DLookup (["State"], "Cities", "[City] = Forms![Companies]!City")