Auto fill code not working

[derrickchanson]

I am trying to get an "address", "state", and "city" field to automatically fill in on a form based on the selection from a listbox called "site".

The "Site" field list comes from a separate table called "tbldistricts", which contain the matching address, city, and state for the site.

When I enter a site in the field the address, city, and state fields fill in automatically, but with the same information everytime, never changing.

Here is the code I entered for the "site" field:

Private Sub Site_Exit(Cancel As Integer)
Dim varAddress1, varCity, varState As Variant
Address1 = DLookup("Address1", "tblDistricts", "Site =[Site]&quot
City = DLookup("City", "tblDistricts", "Site = [Site]&quot
State = DLookup("State", "tblDistricts", "Site = [Site]&quot
If (Not IsNull(varAddress1)) Then Me![Address1] = Address1
If (Not IsNull(varCity)) Then Me![City] = City
If (Not IsNull(varState)) Then Me![State] = State
End Sub

Why are the address, city, and state not filling in properly?

Thanks
Derrick

 

[derrickchanson]

Here is another code that does work, but only when the "Site" field is not a Combobox. In this situation the site field is called "Intitute" on the form and "site" in the table "tbldistricts"

Private Sub Institute_Exit(Cancel As Integer)
Dim varAddress1, varCity, varZipCode, varState As Variant
varAddress1 = DLookup("Address1", "tblDistricts", "Site = [Institute]&quot
varCity = DLookup("City", "tblDistricts", "Site = [Institute]&quot
varState = DLookup("State", "tblDistricts", "Site = [Institute]&quot
If (Not IsNull(varAddress1)) Then Me![Address1] = varAddress1
If (Not IsNull(varCity)) Then Me![City] = varCity
If (Not IsNull(varState)) Then Me![State] = varState

End Sub