How do I populate a text box based on the data in another text box?

[stateemk]

I have a form that has a region code on it. That region code is based on the zip code. What code would I use to have the program lookup the region code once the zip code has been entered. I already have a table that has the zip codes broken down into their regions.

[ajalwaysus]

I would write some VBA code on the AfterUpdate event of the Zipcode, that would do a DLookUp based on the ZipCode, then assigns a value to the region field.

Example:

Private Sub ZIPCODE_AfterUpdate()
     me.Region.Value = DLookUp("REGION", "[TABLE1]", "ZIPCODE = " me.ZipCode.Value)
End Sub
 

-AJ

[stateemk]

Here's the code I've got but it doesn't like the last me.HOME_ZIP.Value. What did I do wrong?

Private Sub txtHomeZip_AfterUpdate()
    me.REGION_CD.Value = DLookUp ("Region Number", "[REGION BREAKDOWN BY ZIP CODE]", "Zip_Code = " me.HOME_ZIP.Value)

[ajalwaysus]

 
Private Sub txtHomeZip_AfterUpdate()
     me.REGION_CD.Value = DLookUp ("Region Number", "REGION BREAKDOWN BY ZIP CODE", "Zip_Code = " & me.txtHOME_ZIP.Value)
End Sub
 

Try this, you didn't include the "txt" in the "me.HOME_ZIP.Value", and if this doesn't work give me the error message.

Also, are you wanting to save this field, or is it just for displaying? I ask, because if you are only doing it for displaying, there may be a more efficient way of doing this that I have in mind.

-AJ

[stateemk]

I got it to accept the statement when I added a comma after the last set of closing quotes and before the Me.txtHomeZip.Value (see code)

Private Sub txtHomeZip_AfterUpdate()
    Me.REGION_CD.Value = DLookup("Region Number", "[REGION BREAKDOWN BY ZIP CODE]", "Zip_Code = ", Me.txtHomeZip.Value)
End Sub

I then tried to run it, but I am getting a compile error of Wrong number of arguments or invalid property assignment. Could I maybe be referring to the wrong names? The REGION_CD is the name of the text box on the form that I want the region code to show after the zip is entered. The "Region Number" is the column in the REGION BREAKDOWN BY ZIP CODE table that is the region code. The Zip_Code is a column in the REGION BREAKDOWN BY ZIP CODE table and the txtHomeZip is the text box in the form where the zip code is initially typed. I'm sure I've thoroughly confused you now, so just let me know if you need further explanation.

[missinglinq]

If ZipCode is defined as a Number Datatype:

me.Region.Value = DLookUp("REGION", "[TABLE1]", "[ZIPCODE] = " & me.ZipCode.Value)

If ZipCode is defined as Text Datatype:

me.Region.Value = DLookUp("REGION", "[TABLE1]", "[ZIPCODE] = '" & me.ZipCode.Value & "'")

You have to be sure, of course, that all the object names are the same as your actual names.

Linq ;0)>

[ajalwaysus]

Your compile error is because of your extra comma, you are now passing 4 arguments to a function (DLookUp) that only uses 3. Every comma you use adds another argument.

I found an error in my code in Post #4 and then corrected it, please try it again, and if it does not work, give me the error.

-AJ

[NeoPa]

See Quotes (') and Double-Quotes (") - Where and When to use them. Assuming the ZIP code is alphanumeric you would need to surround the value in quotes (').
Try :

Private Sub txtHomeZip_AfterUpdate()
    Me.REGION_CD = DLookup("[Region Number]", _
                            "[REGION BREAKDOWN BY ZIP CODE]", _
                            "Zip_Code = '" & Me.txtHomeZip & "'")
End Sub

[ajalwaysus]

Keep in mind what NeoPa and missinglinq are saying, but in regards to your compile error, make sure to read my post #7.

Don't mean to override you NeoPa, but the compile error stateemk is having still exists in your code. I point it out in post #7. =)

-AJ

[NeoPa]

No. You're absolutely right AJ. I didn't pay enough attention.

I have fixed my earlier post in place.

[stateemk]

I think it's working the way I want it. I'm still doing some testing. Thanks for all your help.