Need some help with populating a textbox

[serino]

I have main form that used to enter new clients. I would like to be able to enter zip code in the CLZIP textbox and afterupdate, if the CLZIP matches the zip code from the T-103 table it would then populate an unbound textbox "txtemail" on the main form with the assigned email address for that zip code.

I have two tables:
T-099ClientTbl T-103EmailAddress
Fields CLZIP Zip
Email

Here is what I have so far and I am not sure I am going about this the right way. I have placed this code on the afterupdate of CLZIP field of the form and it does not seem to work.

If Me.Form.[CLZIP] = [T-103EmailAddress]![Zip] Then
Me.Form.[txtemail] = [T-103EmailAddress]!

Any help is appreciated.

 

[lameid]

The problem is unless the value is part of the recordsource, you can't do the kind of comparasion your wrote. The good news is there are "Domain" Functions that help you find information in tables. They are Dlookup (the one you need), DSum, DCount, DMin and DMax. Generally it is not a great idea to use these as often you can accomplish the same thing by including the value in the record source or you need more than one value from the same table or query and a recordset is better suited (when you reread this later so it makes sense: use a recordset if you would use more than one dlookup on the same table). Recordsets are much more involved so for now try this.

If the Field Zip in the table T-103EmailAddress is a number then do this....

Me!txtemail = Dlookup("Email","[T-103EmailAddress]", "Zip = " & Me!CLZIP)

If it is text do this...

Me!txtemail = Dlookup("Email","[T-103EmailAddress]", "Zip = """ & Me!CLZIP & """")

The difference is that you have to have delimeters for text values in criteria.

 

[serino]

lameid,

Thank you for your quick reply. The Field Zip in the table T-103EmailAddress is a number. Where do put this code? would it be the control source for txtemail field on the main form?

 

[lameid]

Sorry, same place you had the code

the afterupdate of CLZIP field of the form

 

[serino]

Thank You. It worked out great.