sql update in VBA

[redknite]

I have a table called tblListings with a field called "Status"

I have another table called tblSales. In the form for this table I want to have cmd button that will change the "Status" field in tblListings from PEN to SLD. The two tables are related with a field called "TRNumberL"



I can't seem to get it to run right. Can someone tell me what I'm doing wrong?

Private Sub cmdCloseSale_Click()
Dim myUpdate As String
Dim mySet As String
Dim myWhere As String
Dim strSQL As String
'set sql update statement
myUpdate = "UPDATE tblListings "
'set sql set statement
mySet = "SET Status = 'SLD' "
'set sql where statemewnt
myWhere = "WHERE TRNumberL=" & " ' " & Me!TRNumberL & "'"
'define full sql statement
strSQL = myUpdate & mySet & myWhere & ";"
DoCmd.RunSQL strSQL
End sub

[RuralGuy]

Have you tried just creating a query that joins the two tables so you can directly change fields in both tables?

[redknite]

That's an option, but not what i really wanted to do. Is there something wrong with the code?

[RuralGuy]

Have you put in a MsgBox for strSQL just before the RunSql so you can see whatb you are trying to run? What does the code do? Describe better than it doesn't run right please.

[pkstormy]

Wouldn't it be easier to just create the strSQL itself versus breaking it into pieces first using myUpdate, mySet, and myWhere and then piecing it together to form strSQL?

ie..
strSQL = "UPDATE tblListings set Status = 'SLD'..... WHERE...."

I personally like to avoid creating unnecessary variables such as myUpdate, mySet, and myWhere (since declaring new variables does eat up memory.)

[redknite]

when i run it in the msgbox this is what I get:

UPDATE tblListings Set Status = 'SLD' WHERE TRNumberL= 'L-10-043';

When I run the cmd without the msgbox i get a prompt saying

You are about to update 0 rows. Like it can't find a record in tblSales with a TRNumberL of L-10-043.

[redknite]

Wouldn't it be easier to just create the strSQL itself versus breaking it into pieces first using myUpdate, mySet, and myWhere and then piecing it together to form strSQL?

ie..
strSQL = "UPDATE tblListings set Status = 'SLD'..... WHERE...."

I personally like to avoid creating unnecessary variables such as myUpdate, mySet, and myWhere (since declaring new variables does eat up memory.)

I tried that first. I separated it out so i could try to find my error. I originally had this...

"strSQL = "UPDATE tblListings SET TRNumberL ='SLD' Where TRNumberL=" & Me.TRNumberL"

But that didn't work. Any Ideas on that?

[RuralGuy]

Unless you changed something, this:
"WHERE TRNumberL=" & " ' " & Me!TRNumberL & "'"
creates a WHERE condition with a leading space.
This: UPDATE tblListings Set Status = 'SLD' WHERE TRNumberL= ' L-10-043';
instead of: UPDATE tblListings Set Status = 'SLD' WHERE TRNumberL= 'L-10-043';

[redknite]

PURE GENIUS. On your part that is! Thank you so much.

I owe you a beer...

[RuralGuy]

Glad we could help. Are you ready to use the Thread Tools and mark this thread as Solved?