Runtime Error 3075 *Please Help*

[KLahvic]

Hello all, this is my first post and hope to get a solution to my inquiry. I have a form where I enter a vendor ID and the form auto populates all vendor information i.e. name address, zip code etc. When the zip code portion populates I have a table with just zip codes and cities and states and would like the city state to be looked up on this table. I have been told the dlookup would work but with the code, I am getting the runtime 3075 error. Exactly as below:

Run-time error '3075':

Syntax error (missing operator) in query expression '[Zipcode]='.

I have included the code string I am using on after update event below:

Private Sub PSTCD_Change()
Me.txtcty = DLookup("[City]", "tbl_zips", "[Zipcode] = " & Me.PSTCD)
Me.txtst = DLookup("[State]", "tbl_zips", "[ZipCode] = " & Me.PSTCD)
End Sub

Please, any insight would be appreciated. I am not going to get into how this is for a company where I promised a deliverable, but it is...


Thank you in advance -

Ken

 

[DCrake]

If your zip code is not a number then it needs quotes around it

Me.txtcty = DLookup("[City]", "tbl_zips", "[Zipcode] = '" & Me.PSTCD & "'")

David

 

[PeterF]

Is the field PSTCD a number field? If not you need additional quotes.
Are you referencing the form control or the table field? I expected a me.txtPSTCD.
Set a breakbpoint in your code to see what the value is of me.PSTCD.
If it can be null use the nz funtion to give a value.

 

[KLahvic]

David/Peter - Thank you both for your response. I have changed a couple of things. ZipCode is a text field so I added the quotes at the end as in the example you provided. I changed PSTCD to txtpstcd as well as changed the name filed to reflect as well.

I do not get the error any longer, but it still does not work to my liking. I am going to attach my db to this forum, if either of you wouldn't mind checking it out I would appreciate it. I am a rookie at this so please be gentle...lol...

BTW - Use vendor ID 18666 to test if you try anything as it has all the fields to populate.

Thanks Again -

Ken

 

[DCrake]

Can you send it pre 2007?

 

[KLahvic]

Yes here it is in 2003

 

[namliam]

Private Sub PSTCD_Change()
Me.txtcty = DLookup("[City]", "tbl_zips", "[Zipcode] = " & Me.PSTCD)
Me.txtst = DLookup("[State]", "tbl_zips", "[ZipCode] = " & Me.PSTCD)
End Sub

*OUCH*

Why is DLookup so popular, dont nobody write no sql anymore??

You dont use DLookups for this, use a proper query...

 

[DCrake]

If you condiser that for all the PSTCD's in the main table not one appears in the zip code table then it is obvious that the dlookup is going to return a no find.

You will need to wrap Nz(DLookUp....,"") around the dlookup to prevent an error being raised.

What you could do when you resiolve the above is to bring in the zip files tables and create a join betwen PCTCD and Zip and brind down the city, etc into the sql for the vendor combo and refer to the columns as you already have done.

David

 

[KLahvic]

*OUCH*

Why is DLookup so popular, dont nobody write no sql anymore??

You dont use DLookups for this, use a proper query...

Thank you...If I knew what exactly I was doing, I wouldn't be coming to these boards for help. I have no clue about SQL?!?! Please don't criticize when I simply asked for help.

"What you could do when you resiolve the above is to bring in the zip files tables and create a join betwen PCTCD and Zip and brind down the city, etc into the sql for the vendor combo and refer to the columns as you already have done."

David -

Thank you for your help, I added the nz statement and will try to understand the SQL portion, but I do not have any experiance with that. If you can point me in the right direction I can muddle through it and give it a go.

 

[DCrake]

Be very aware that the forms you are designing will cope with the resolutions of the end users monitors/screens. It may look nice on your large 21 inch development pc but put it on a laptop or a 17 inch screen you cannot see all the screen.

 

[namliam]

Each DLookup does a single search in your table(s), while a single query can do both in one go...

Worse, if your PSTCD is a combobox you should be getting it from there using hidden columns and fetching the data from there, which it right now isnt, but could be as you probably only want zips to be taken that are "real" thus will probably be known?