UPDATE query, syntax incorrect 3

[beckwiga]

Something is wrong with the syntax of my UPDATE SQL here. Being prompted to enter parameter values when I execute. VBA syntax syntax is still new to me. Anyone assist?


Private Sub Zip_AfterUpdate()

Dim strSQL1 As String
Dim strSQL2 As String

Forms!Address.Refresh
DoCmd.SetWarnings False

strSQL1 = "UPDATE Address SET [City]=ZIP_CODES.CITY WHERE ZIP_CODES.ZIP = Forms!Address.Zip"
strSQL2 = "UPDATE Address SET [State]=ZIP_CODES.STATE WHERE ZIP_CODES.ZIP = Forms!Address.Zip"

'If IsNull(Forms!Address.City) And IsNull(Forms!Address.State) Then

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2

'End If

DoCmd.SetWarnings True

Forms!Address.Refresh


End Sub

 

[TonyJollans]

Hi beckwiga,

I'm not entirely sure what all your names represent but you have some things inside quotes which you need outside them. The elements which must be interpreted in your (VBA) code must be outside the quotes whilst the names to be interpreted by SQL must be inside them. If you need more help can you post a little more detail about your Forms, Tables and Queries.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fredericofonseca]

try
strSQL1 = "UPDATE Address SET [City]=ZIP_CODES.CITY WHERE ZIP_CODES.ZIP = " & Forms!Address.Zip
strSQL2 = "UPDATE Address SET [State]=ZIP_CODES.STATE WHERE ZIP_CODES.ZIP = " & Forms!Address.Zip

if Forms!address.zip is not numeric then you need
= '" & Forms!Address.Zip & "'"



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

I think you have to join the Address and ZIP_CODES tables too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckwiga]

After reading your suggestions, tried the following which I thought should work, but still being prompted for param values.

Private Sub Zip_AfterUpdate()

Dim strSQL1 As String
Dim strSQL2 As String

Forms!Address.Refresh
DoCmd.SetWarnings False

strSQL1 = "UPDATE Address SET [City]=ZIP_CODES_ADDRESS.CITY WHERE Address.Id = " & Forms!Address.ID
strSQL2 = "UPDATE Address SET [State]=ZIP_CODES_ADDRESS.STATE WHERE Address.Id = " & Forms!Address.ID

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2

DoCmd.SetWarnings True

Forms!Address.Refresh


End Sub

History: I joined the ZIP_CODES table with the ADDRESS table on Zip Code, this is the table ZIP_CODES_ADDRESS. What I am wanting is that after a user enter the zip code on Form Address, after update I want to return the City and State from ZIP_CODES (as a lookup) and populate the City, State in the Address table. I was thinking the above looked good, but, alas, still no love. Any other input appreciated as always.

The Enter Param values it prompts me for are ZIP_CODES_ADDRESS.CITY and STATE as if it doesn't know what they are, which leads me to believe that either my syntax is wrong or my where is wrong. But I think my where is right. I can post my table(s) if helpful.

 

[PHV]

Why not simply this ?
Forms!Address!City = DLookUp("CITY", "ZIP_CODES", "ZIP=" & Forms!Address!Zip)
Forms!Address!State = DLookUp("STATE", "ZIP_CODES", "ZIP=" & Forms!Address!Zip)

Anyway, the UPDATE way:
strSQL = "UPDATE Address INNER JOIN ZIP_CODES ON Address.Zip=ZIP_CODES.ZIP SET City=ZIP_CODES.CITY,State=ZIP_CODES.STATE WHERE Address.Id=" & Forms!Address!ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi beckwiga,

Why do you need to have the City and State on the Address table at all? It's contrary to basic database design principles and it should be sufficient for them to be on the Zip codes table, joined to the Address table when you need them?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[beckwiga]

Thanks, guys. I got this working with:

strSQL1 = "UPDATE Address INNER JOIN ZIP_CODES ON Address.Zip=ZIP_CODES.ZIP SET ADDRESS.CITY=ZIP_CODES.CITY,ADDRESS.STATE=ZIP_CODES.STATE WHERE Address.Id=" & Forms!Address!ID

Thanks for the help!