How can I autofil the City and State fields by entering a Zip?

[Rabbit]

I didn't mean the actual data, just the names of the fields.

[b]Tbl_Main[/b]
[ID]; PK; Autonumber
[State]; Text
[Zip]; Numeric

[kumana1]

Would you need a table that already has all of this relevant information to do something like this? What I mean is, wouldn't you need a table that has the City, State, Zip Code already populated? Not sure if I'm making any sense here.

[AndyWal6]

You probably have a Table with the relevent fields i.e. ZIPId, CITY, STATE, and ZIP already populated with all the data you will need, so, Make your ZIP field on the form a dropdown with four columns referencing your table and set the column widths to 0,0,0,2.5cm so as to show only the zipcode, now people can either type in the zip or select, That done you make the City and State fields = [ZIP].column(1) and [ZIP].column (2) remembering that ZIPId would be column(0) and not required, Don't forget to make the City and State fields Locked to prevent rubbish being entered. You can use the same in Reports printed from your Data. Hope that helps, it always works for me!
Andy

[Crayola465]

I didn't mean the actual data, just the names of the fields.

[b]Tbl_Main[/b]
[ID]; PK; Autonumber
[State]; Text
[Zip]; Numeric

COUNTER
EBDORMANT OR TRESCHEAT
DATE PROCESSED
YEAR DUE
HOLDER STATE
CODE
CHECK #
LAST ACTIVITY DATE
AMOUNT
UNITS
DDA/SUSPENSE ACCT
CO CODE
PAYEE LAST NAME
TITLE1
MULTI OWNER
CO OWNER LAST
TITLE2
STREET ADDRESS
STREET ADDRESS 2
CITY
STATE
ZIP
SS #
TR-ACCT NUMBER
ADMINISTRATOR
ASSET/TR-ACCT NAME
CUSIP NUMBER
DUE DILIGENCE MEMO
DATE PAID
MEMO
PAID TO CUSTOMER

[Rabbit]

COUNTER
EBDORMANT OR TRESCHEAT
DATE PROCESSED
YEAR DUE
HOLDER STATE
CODE
CHECK #
LAST ACTIVITY DATE
AMOUNT
UNITS
DDA/SUSPENSE ACCT
CO CODE
PAYEE LAST NAME
TITLE1
MULTI OWNER
CO OWNER LAST
TITLE2
STREET ADDRESS
STREET ADDRESS 2
CITY
STATE
ZIP
SS #
TR-ACCT NUMBER
ADMINISTRATOR
ASSET/TR-ACCT NAME
CUSIP NUMBER
DUE DILIGENCE MEMO
DATE PAID
MEMO
PAID TO CUSTOMER

And is this the table that you want to look up City/State using zip code? If not, give me the information on the lookup table. If it is, then I misassumed that you had a seperate table with every zip code of every city and state.

This lookup will only work insofar as you have a customer from that city/state/zip code. Which should work if you put the code in the After Update event of the Zip Control and you named everything correctly.

[Crayola465]

And is this the table that you want to look up City/State using zip code? If not, give me the information on the lookup table. If it is, then I misassumed that you had a seperate table with every zip code of every city and state.

This lookup will only work insofar as you have a customer from that city/state/zip code. Which should work if you put the code in the After Update event of the Zip Control and you named everything correctly.

Oh Im sorry, I do have a seperate table for the City State Zip.

The fields are

ZipId
ZipCode
City
State
DefaulyCity

[Rabbit]

Oh Im sorry, I do have a seperate table for the City State Zip.

The fields are

ZipId
ZipCode
City
State
DefaulyCity

Assuming that the above table is named [zip-code]:

Private Sub Zip_AfterUpdate()
   Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
   Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
End Sub

[Crayola465]

Assuming that the above table is named [zip-code]:

Private Sub Zip_AfterUpdate()
   Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
   Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = " & Me.ZIP)
End Sub

Now it says Run-time error '3464':
data type mismatch in criteria expression.

Does that mean I have labels wrong?

[Rabbit]

Now it says Run-time error '3464':
data type mismatch in criteria expression.

Does that mean I have labels wrong?

That most likely means your zip code field is text.

Try:

Private Sub Zip_AfterUpdate()
   Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
   Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
End Sub

[Crayola465]

That most likely means your zip code field is text.

Try:

Private Sub Zip_AfterUpdate()
   Me.[CITY] = DLookup("[City]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
   Me.[STATE] = DLookup("[State]", "[zip-code]", "[ZipCode] = '" & Me.ZIP & "'")
End Sub

You were right. Thank you so much for your help, I really appreciate it.

[Rabbit]

Not a problem, good luck.

[Crayola465]

Not a problem, good luck.

I actually have one more question. It finally works and what I now would like to do is have it only enter the City and State fields if it doesnt recognize the zip code. i.e. if there is a zip code that isnt in my database, I want to be able to enter it manually. Otherwise, I want it to skip right over the City and State fields. Is that possible?

[Rabbit]

I actually have one more question. It finally works and what I now would like to do is have it only enter the City and State fields if it doesnt recognize the zip code. i.e. if there is a zip code that isnt in my database, I want to be able to enter it manually. Otherwise, I want it to skip right over the City and State fields. Is that possible?

Yes, this is possible. You just set the focus to a different control depending on whether or not City or State is a null.

If IsNull(Me.City) Then
     Me.City.SetFocus
Else
     Me.[Other Control Name].SetFocus
End If

[Crayola465]

Yes, this is possible. You just set the focus to a different control depending on whether or not City or State is a null.

If IsNull(Me.City) Then
     Me.City.SetFocus
Else
     Me.[Other Control Name].SetFocus
End If

Im not sure I understand what I would use as the [other control name]. Is it something I can just choose or would there be a name from my table I should use?

[Rabbit]

Im not sure I understand what I would use as the [other control name]. Is it something I can just choose or would there be a name from my table I should use?

It's something you would choose. Whatever the next textbox or whatever you want them to go to instead of city/state.