enter zip code and come up with city (1 Viewer)

[y2kmental]

hi, I just want to say how much this forum has helped me. now for the question


On a form, I want to type a zip code and come up with its city in the next field automatically. I already have all the zip and citys in a table. Would anyone know how to do this?

 

[ColinEssex]

DLookup function should do it

 

[y2kmental]

how do i use it?

 

[Rich]

Use a combo box with two columns

 

[y2kmental]

doesnt really seem to work the way i want it to, can you show me an example?

 

[y2kmental]

could you possible write out the dlookup command for me?

When I select from the field ZIP I want it to show up in a text box

its on the form, LogBook

the zip code, city, and ID is comming from the table TBLCityZip

Thanks in advance

 

[Pat Hartman]

Using a combo requries no code. Add a combo to your form. The wizard will walk you through the process.

 

[y2kmental]

but when i type the zip code in one box, i want the city to automatically be filled in the next field

 

[Rich]

Since the city is already stored in your db you don't need to store it again, just display it. Add an unbound textbox to your form, set its control source to = [YourCombo].[Column](1), where column 1 is the second column of your combo

 

[y2kmental]

but lets say i would like to overide this function. Right now I have new yorks zip codes in my table, but sometimes we may need to enter out of state zips, will this let me type over them?

thanks for your help

 

[y2kmental]

if i need to enter the town manually, how do i overide it?

 

[Pat Hartman]

Usually applications either store city, state, zip and don't use any lookups or they just store zip and join to the zip table as necessary to retrieve city and state. You seem to want to combine both techniques which entails more work for little benefit.

You should include the three columns in the rowsource for the combo - zip, city, state. Specify that the bound column is 1 and make all three columns visible. In the AfterUpdate event of the combo, you can copy the city and state columns to your form's bound fields -
Me.txtCity = Me.cboZip.Column(1)
Me.txtState = Me.cboZip.Column(2)

The columns are a zero based array so .column(1) is actually the second column.

You'll need to set your limit to list property to no since you don't intend to use a complete zip code list. I don't know what will happen when you enter a zip that is not in the list. You'll have to figure out what to do.