Hi All,
i could use your help, I have two tables my call log tabe that has a combox with zip codes that i am using, cazip table that has my zip codes and the state codes (shown below).
zip - statecode
61721 - 13
61007 - 15

i created a form so that when i change the zip dlookup will return the state code for me in the box that labeled statecode.... Currently when i change the zip code it only returns the frist value in side the colum of state codes.

exp

zip=61007
statecode=13

so this is my current dlookup script

Private Sub CBOZIP_AfterUpdate()
STATECODE = DLookup("[STATECODE]", "[CAZIP]", "[CBOZIP]=" & CBOZIP)

what do i need to do to make it pull the state code that matches the zip.

i am very new to this as in the last week so most of my learning is hands on and that not much. any help would be great
Thanks
jkisner

Would this be simpler for you?

Autofill

Is the Field in the CAZIP Table actually named CBOZIP? If so, what is its Datatype?

Fields composed entirely of Digits but not used for mathematical calculations are usually defined as Text, and your syntax is only correct if CBOZIP is defined as a Number.Linq ;0)>

Hi
Thanks for your help it is currently has a data type as a number.

Please confirm that the Field in the Table is actually named CBOZIP? I assume that CBOZIP is a Combobox that has the Zips in it, but that's an odd name for a Field in a Table.

Linq ;0)>

hi thanks for you help. The table fields are ID,STATECODE,ZIP,CITY,COUNTY the primery Key is Zip . Im not sure if that matters but thought i would let you know incase it does if you need any othere info please let me know. Thanks again for your help.

If the Table Field is named ZIP then your code needs to be

DLookup("[STATECODE]", "[CAZIP]", "[ZIP]=" & CBOZIP)

Of course, since ZIP and STATECODE are in the same Table, the standard way of doing this would be to have both ZIP and STATECODE in the CBOZIP's RowSource, and then use this code to populate the STATECODE Textbox:
assuming that the STATECODE was the 2nd Field in the Combobox, reading left-to-right; the Column Index is Zero-based.

This would alleviate the need to use DLookUp() altogether.

Linq ;0)>

Thanks, I took your second option and it worked great. Now if i want to take the value given in the STATECODE and look for othere items in a differnt table. would i need to us DLookup for that. THANKS FOR YOUR HELP

Yes, DLookUp would be the way to go, assuming that the other Table had a Field that would match STATECODE.

Linq ;0)>

Thanks for all your help i have it up and going and it is all looking good. now i just need to figure out mult user stuff. Which i well post a new thread if need be. Thanks again for all your help. have a good day

Glad we could help!

Good luck with your project!

Linq ;0)>