Assigning a value to one control based on the value of another con

[Stef]

On entering the zip-code in one control on a form I want the the name of the
state appear in another control, just to fulfil a form correctly. I have a
table with all zip-codes with corresponding name of the state in the second
column. Very happy if someone can help me with this.

 

[Klatuu]

In the Before Update event of the control where you enter the zip code:
Dim varState as Variant

varState = DLookup("[STATE_NAME]","StatesTable","[ZIP_CODE] = '" _
& Me.txtZip & "'")
If IsNull(varState) Then
MsgBox "Zip Code Not Found"
Cancel = True
Else
Me.txtStateName = varState
End If

Of course you will have to change the names for your object name.

 

[Stef]

Hi
Your code looked fine, but I could make it work. No errors coming up but it
leaves the textbox Me.txtStateName empty. I am not so familiar in codeing in
Access but I have good experince in writing VBA code in Excel. I'm little
confused about your apostrophs and the two equal signs on the same line. Is
this maybe not quite correct?
--
Stef

In the Before Update event of the control where you enter the zip code:
Dim varState as Variant

varState = DLookup("[STATE_NAME]","StatesTable","[ZIP_CODE] = '" _
& Me.txtZip & "'")
If IsNull(varState) Then
MsgBox "Zip Code Not Found"
Cancel = True
Else
Me.txtStateName = varState
End If

Of course you will have to change the names for your object name.

On entering the zip-code in one control on a form I want the the name of the
state appear in another control, just to fulfil a form correctly. I have a
table with all zip-codes with corresponding name of the state in the second
column. Very happy if someone can help me with this.

 

[Klatuu]

The first = assigns the value returned by the DLookup function to the
variable varState
The second = is used as a logical operator for the criteria argument of the
DLookup function to compare the values of [ZIP_CODE] and Me.txtZip
The apostrophies are used to enclose the value of Me.txtZip because it is
text. If it is not text, but a number in [ZIP_CODE], Then it should be this
way:

varState = DLookup("[STATE_NAME]","StatesTable","[ZIP_CODE] = " _
& Me.txtZip)
Why you are not getting a value into the text box, I don't know. Have you
stepped through the code in debug mode to watch the values?

Hi
Your code looked fine, but I could make it work. No errors coming up but it
leaves the textbox Me.txtStateName empty. I am not so familiar in codeing in
Access but I have good experince in writing VBA code in Excel. I'm little
confused about your apostrophs and the two equal signs on the same line. Is
this maybe not quite correct?
--
Stef

In the Before Update event of the control where you enter the zip code:
Dim varState as Variant

varState = DLookup("[STATE_NAME]","StatesTable","[ZIP_CODE] = '" _
& Me.txtZip & "'")
If IsNull(varState) Then
MsgBox "Zip Code Not Found"
Cancel = True
Else
Me.txtStateName = varState
End If

Of course you will have to change the names for your object name.

On entering the zip-code in one control on a form I want the the name of the
state appear in another control, just to fulfil a form correctly. I have a
table with all zip-codes with corresponding name of the state in the second
column. Very happy if someone can help me with this.

 

[Stef]

When I copied your (the original) code it responds OK but only results in "No
Zip Code found" whatever valid value I enter. But at least I'm a little bit
on my way. Thanks for your interest.

--
Stef

The first = assigns the value returned by the DLookup function to the
variable varState
The second = is used as a logical operator for the criteria argument of the
DLookup function to compare the values of [ZIP_CODE] and Me.txtZip
The apostrophies are used to enclose the value of Me.txtZip because it is
text. If it is not text, but a number in [ZIP_CODE], Then it should be this
way:

varState = DLookup("[STATE_NAME]","StatesTable","[ZIP_CODE] = " _
& Me.txtZip)
Why you are not getting a value into the text box, I don't know. Have you
stepped through the code in debug mode to watch the values?

Hi
Your code looked fine, but I could make it work. No errors coming up but it
leaves the textbox Me.txtStateName empty. I am not so familiar in codeing in
Access but I have good experince in writing VBA code in Excel. I'm little
confused about your apostrophs and the two equal signs on the same line. Is
this maybe not quite correct?
--
Stef

In the Before Update event of the control where you enter the zip code:
Dim varState as Variant

varState = DLookup("[STATE_NAME]","StatesTable","[ZIP_CODE] = '" _
& Me.txtZip & "'")
If IsNull(varState) Then
MsgBox "Zip Code Not Found"
Cancel = True
Else
Me.txtStateName = varState
End If

Of course you will have to change the names for your object name.

:

On entering the zip-code in one control on a form I want the the name of the
state appear in another control, just to fulfil a form correctly. I have a
table with all zip-codes with corresponding name of the state in the second
column. Very happy if someone can help me with this.