auto fill

[rmyslik]

I am new to this and am trying to make a on exit expresion? that fills int the fields CITY and STATE from the zip codes table. The only problem is that it uses the 1st zip code from the table ONLY! Please help. What am I doing wrong?
Sub Zip_Exit(Cancel As Integer)
Dim varSTATE, varCITY As Variant
varSTATE = DLookup("STATE", "Zip Codes", "ZIP =[Zip]&quot
varCITY = DLookup("CITY", "Zip Codes", "ZIP =[Zip]&quot
If (Not IsNull(varSTATE)) Then Me![State] = varSTATE
If (Not IsNull(varCITY)) Then Me![City] = varCITY
End Sub

 

[gol4]

missing the identifier for the fild to compare to
try
varSTATE = DLookup("STATE", "Zip Codes", "ZIP =me.[Zip]&quot
varCITY = DLookup("CITY", "Zip Codes", "ZIP =me.[Zip]&quot

 

[jwm]

Following is the code I place on the After Update property of my zipcode field. You'll have to adjust the field names IAW your scheme.

Private Sub strPostalCode_AfterUpdate()

Me!strCity = DLookup("City", "tblZipCode", "Zip = " & "'" & Me!strPostalCode & "'&quot

Me!strStateOrProvince = DLookup("State", "tblZipCode", "Zip = " & "'" & Me!strPostalCode & "'&quot

Me!strFIPS = DLookup("FIPS", "tblZipCode", "Zip = " & "'" & Me!strPostalCode & "'&quot

End Sub

HTH.

 

[JerryDennison]

While either of these two methods work they are not efficient. Domain Aggregate functions are notoriously slow. A far easier and simpler method is to include the city and state fields in the combobox that's used for storing the zip code. Then in an unbound control on the form you would use these columns as their controlsource.

=Forms!FormName!ZipCode.Column(1)
=Forms!FormName!ZipCode.Column(2)

NOTE: column number begins with 0. I'm assuming that the zip code is the primary key (although it really shouldn't be) and therefore is column(0). City could then be column(1) and state column(2).