What is wrong with this code

[Zonie32]

Please help. Here is the code I am using on a form.

Private Sub ThreeDigZip_AfterUpdate()
Me.City = DLookup("[City]", "tblZips", "[ThreeDigZip] = '" & Me.ThreeDigZip & "'")
Me.State = DLookup("[State]", "tblZips", "[ThreeDigZip] = '" & Me.ThreeDigZip & "'")
End Sub

I want it to automatically put the State and City in when I type in the 3 digit Zip. The first line is working, when I type 850 for the zip it puts Phoenix in the City text box. But it doesn't do it for the state. I want them to do both at once. Can anyone help?

 

[chris153]

Looks OK !

try using DAO (Should be quicker than 2 DLookups anyway!)

dim db as dao.database
dim rst as dao.recordset

set db = CurrentDb()

set rst = db.openrecordset ("SELECT * FROM tblZips WHERE ThreeDigZip = '" & Me.ThreeDigZip & "'", dbOpenDynaset)

rst.movefirst
me.city = rst.fields("City").value
me.state = rst.fields("State").value
rst.close

Note, remember to reference the DAO Library if you haven't already done so.

 

[KenReay]

HI

Is ThreeDigZip a combo box?

if yes why not include the city and state as hidden columns (width 0) and use:

=ThreeDigZip.Column(1)
=ThreeDigZip.Column(2)

to populate your city and state text boxes. no lookups required, faster still

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Zonie32]

thanks for the quick response. I copied your code and pasted then did a debug and it gave an error on
set db = CurrentDb().

I'm not very familiar with VB code and just happened to stumble upon the original code I used in the above message.

Can you help further? also, do i have to type something in the code to reference the DAO library? I went to tools, references and it is checked.

 

[chris153]

No probs,

Make sure that the 'Microsoft DAO 3.6 Object Library' reference is checked.

If it is I'm in trouble