UPDATE query

[sanjna000]

I am trying to update one of my tables using VBA. My code is as follows:

Sub UpdateChildAccounts(selItem As String)
Dim wrkJet As DAO.Workspace
Dim db As DAO.Database
Dim dbname As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim Q As DAO.QueryDef

dbname = "C:\Documents and Settings\Sanju\Desktop\Project\Commcare.mdb"
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase(dbname, , True, "Microsoft.Jet.OLEDB.3.51")
Set Q = db.CreateQueryDef("")
strSQL = "UPDATE ClientAccountMapping" & _
" SET (ClientAccountMapping.InvoiceCode)='RBK'" & _
" WHERE (((ClientAccountMapping.InvoiceCode)='" & selItem & "'));"
Q.Sql = strSQL
Q.Close
End Sub

I am getting a syntax error in UPDATE query. I cannot see anything wrong in there. I hope some can help me to find my error.

Thanks a lot for u r help in advance.
Sanjna...

 

[jcrater]

Try removing the semicolon. I know that MS Access always appends one to the end of each command in a query window, but I've never used it with DAO.

 

[sanjna000]

I use SELECT statement with the semi colon, it didn't give any prob. I guess there should be something else wrong with it.

 

[PHV]

What about this ?
strSQL = "UPDATE ClientAccountMapping" & _
" SET InvoiceCode='RBK'" & _
" WHERE InvoiceCode='" & Replace(selItem, "'", "''") & "'"


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

 

[GoodOmens]

When I tried this it objected to the parenthesis in
"SET (ClientAccountMapping.InvoiceCode)='RBK'"

Oddly enough, I did not receive a syntax error concerning the (ClientAccountMapping.InvoiceCode)in the WHERE section.

 

[sanjna000]

PH,

I used u r code. It generated an error by highlighting 'replace' stating that sub function not defined. Do i have to give reference?

Thanks for u r help!
Sanjna...

 

[PHV]

Seems you have an old access ...
What about this ?
strSQL = "UPDATE ClientAccountMapping" & _
" SET InvoiceCode='RBK'" & _
" WHERE InvoiceCode=' & selItem & "'"


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

 

[sanjna000]

I tried but doesn't seem to be working.

 

[PHV]

You have to execute the query ...
Q.Execute

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

 

[sanjna000]

PH,
I did execute the query. It didn't give any error but still not updating the table.

 

[sanjna000]

Is there any way that i can run UPDATE query without using query def?

 

[sanjna000]

I tried to use following code. But it gave me an error stating variable not defined. Is any reference needs?

DoCmd.setWarnings False
DoCmd.RunSQL strSQL
DoCmd.setWarnings True

I really appriciate u r help!
Sanjna...

 

[PHV]

Can you please post the code of the whole sub saying us which line is highlighted when in debug mode ?

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