Hello, I try to fill fields auto

[Guest_imported]

Hello,

I try to fill fields automatically on form based on a control's value.

I have fields that there are "ST(state), CITY, ZIP" in table "MOTOR_CARRIERS". The form should be: When I enter Zip I will never have to enter State/City again.

The following are my codesbut not work)

Private Sub ZIP_Exit(Cancel As Integer)

Dim varST, varCity As Variant
varST = DLookup("[ST]", "MOTOR_CARRIERS", "[ZIP]=ZIP&quot
varCity = DLookup("[CITY]", "MOTOR_CARRIERS", "[ZIP]=ZIP&quot
If (Not IsNull(varST)) Then Me![ST] = varST
If (Not IsNull(varCity)) Then Me![CITY] = varCity

End Sub

Any suggestion?

Many Thanks.
Eric

 

[rbowes]

Eric,

Your syntax is wrong.
Private Sub ZIP_Exit(Cancel As Integer)

Dim varST, varCity As Variant
varST = DLookup("[ST]", "MOTOR_CARRIERS", "[ZIP]='" & me.ZIP & "'&quot
varCity = DLookup("[CITY]", "MOTOR_CARRIERS", "[ZIP]='" & me.ZIP & "'&quot
If (Not IsNull(varST)) Then Me![ST] = varST
If (Not IsNull(varCity)) Then Me![CITY] = varCity

End Sub

The way you had it originally made Access ask for a City or State value where [ZIP] = "[ZIP]". Since, there is no Zipcode in the US like that, you're not going to get the results you're looking for.

Good Luck.

 

[Guest_imported]

Can this still be done if the fields you want auto-displayed on the form are from different tables and if so what needs to be changed?
Much appreciated.

 

[rbowes]

I doesn't matter what table is used to retrieve the values. Just keep the right syntax.