Help to create an UPDATE query SQL on VBA!!!

[AlvaroCity]

Hello there.

I am trying to create an UPDATE query on vba but I don't how to do it.

I have been watching lots of tutorial about how to do it but I am too noob.

I hope you could give me a hand.

I have this code so far. I hope it would help to spot what is wrong.

Dim Ssql As String
    Dim StrNumpiezas As String
    Dim StrAlbaran As String
    
    StrAlbaran = Me.AlbaranID
    StrNumpiezas = Me!frmSubAlbaran.Form!txtNumeroPiezas
    
    Ssql = "UPDATE tblPiezas INNER JOIN (tblPedidoDetalle INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID]) ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]" & _
           "SET tblPiezas.Stock=" [Stock]-StrNumPiezas" & _
           "WHERE (((tblpedidodetallealbaran.AlbaranID)=StrAlbaran));

Thank you for your help.

 

[Frothingslosh]

When troubleshooting something like this, the first thing you should do is have your procedure debug.print the completed SQL statement. You'll often find things like, oh, missing spaces in the text.

 

[TJPoorman]

CurrentDb.Execute(Ssql)

 

[TJPoorman]

Also, you have some incorrect quotations:

    Ssql = "UPDATE tblPiezas INNER JOIN (tblPedidoDetalle INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID]) ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]" & _
           "SET tblPiezas.Stock= [Stock] - [COLOR="Red"]" & [/COLOR]StrNumPiezas & _
           "WHERE (((tblpedidodetallealbaran.AlbaranID)=[COLOR="red"]" &[/COLOR] StrAlbaran [COLOR="red"]& "[/COLOR]));[COLOR="red"]"[/COLOR]

Basically you are trying to build a string to pass to the database to execute.
As Frothingslosh said, adding Debug.Print Ssql after you build the string will help you to see your errors.

 

[Gasman]

What I tend to do with Access SQL for VBA is construct it in the Query designer, get it working as you wish and then amend to concatenate the strings and add any criteria.

That way the bulk of the code and syntax is done and you only need worry about the criteria for the most part.

HTH

 

[MarkK]

But not only . . .

   Debug.Print Ssql

. . . but also then copy that SQL from the immediate pane, and paste it into the SQL view of a new query, and see if it works!

 

[Frothingslosh]

Regarding TJPoorman's suggested code:

    Ssql = "UPDATE tblPiezas INNER JOIN (tblPedidoDetalle INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID]) ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]" & _
           "SET tblPiezas.Stock= [Stock] - [COLOR=red]" & [/COLOR]StrNumPiezas & _
           "WHERE (((tblpedidodetallealbaran.AlbaranID)=[COLOR=red]" &[/COLOR] StrAlbaran [COLOR=red]& "[/COLOR]));[COLOR=red]" [/COLOR]

There still appear to be some errors. The spaces I was referring to are still missing (look at SET and WHERE and the ends of the lines just above each), and let's not forget that StrNumPiezas and StrAlbaran are both strings.

 

[MarkK]

. . . and you need a space before the WHERE clause . . .

           "SET tblPiezas.Stock= [Stock] - " & StrNumPiezas & [COLOR="Red"]" " &[/COLOR] _

. . . and the same issue before the SET.

But again, you'll see these easily if you print the SQL, and try and run the SQL.

 

[AlvaroCity]

thank you everyone for your help. It means a lot.

I still wasnt able to make it work. I guess I am really noob on this. Could you please give me some tips with the debug.print
Another thing is, if it is necessary to put CurrentDb.Execute(Ssql) to make it work?

This is the print
UPDATE tblPiezas INNER JOIN (tblPedidoDetalle INNER JOIN tblpedidodetallealbaran ON tblPedidoDetalle.[PedidoDetalleID] = tblpedidodetallealbaran.[PedidoDetalleID]) ON tblPiezas.[PiezaID] = tblPedidoDetalle.[PiezaID]SET tblPiezas.Stock= [Stock] - 10 WHERE (((tblpedidodetallealbaran.AlbaranID)=198));
Thank you again

 

[AlvaroCity]

Hello everyone again. Finally I was able to make it!!!!

I used the DoCmd.RunSQL Ssql statement that I saw in a tutorial and It works.

But I have doubts.... Is it correct although it works... Why is this? Should have used another statement?

Thank you for everything!

 

[Frothingslosh]

If RunSQL executed it, then the statement was fine. CurrentDb.Execute would have done the trick as well - both work, it's just that Execute is technically the 'current' approach. Both are perfectly acceptable, however.

The only thing I'd have done differently would be using numeric variables rather than string variables. Access is really good about converting data types on the fly, but it's still safer (and less prone to cause programmer errors a year or two down the road) to keep data types matched up. Still, we're talking like 99.9% accuracy vs 99.99%, so it's generally pretty minor as long as you don't do something silly like try to match actual text against a number.

Not that I've ever tried to do that or anything....

 

[AlvaroCity]

Dear Frothingslosh.
First of all, thank you for your answer
It is very nice to see people like you helping newbies like me.
So as you said...
If im not wrong, you mean that you would have put the variables Dim StrNumpiezas and Dim StrAlbaran as integer for example or long?
Thank you again

 

[tranchemontaigne]

Gasman provided great advice for learning how to syntax troubleshoot any SQL statement, including UPDATE statements.

The only think I would add is that double quotes can cause problems. I prefer to use ASCII character codes when writing dynamic SQL as they are clearly understood as characters within a string (instead of string delimiters). Here are the ASCII characters I use all the time:

chr(09) is a tab
chr(10) is a carriage return
chr(13) is a line feed
chr(34) is a double quote
chr(35) is a hash/pound character
chr(39) is a single quote
chr(42) is an asterisk

TIP: You can always double check your memory of ASCII character codes by using debug.print with an ascii character code function call in the immediate window as an alternative to opening an ASCII character code table.

 

[Frothingslosh]

Either integer or long, based on what tblPiezas.Stock and tblpedidodetallealbaran.AlbaranID are. My guess is that Stock is an integer or long integer field, and AlbaranID, being an ID field, is long integer?

Keep in mind, it's really minor and, unless you're doing REALLY precise math, really just serves to avoid programming errors in the future. Honestly, your procedure is perfectly acceptable as it is.