SQL update query code not working

[Sketchin]

Hi,

I am running the following code that should update a table after a command button is pressed:

Private Sub cmdSaveReservation_Click()
On Error GoTo cmdSaveReservation_Click_Err
    On Error Resume Next
Dim strSQL As String
strSQL = "UPDATE tblReservations SET tblReservations.CategoryID =" & _
"[Forms]![frmReservations]![txtCategoryID], tblReservations.Portfolio = [Forms]![frmReservations]![chkPortfolio] " & _
"WHERE (((tblReservations.ReservationID)=[Forms]![frmReservations]![TxtReservationID]))"
CurrentDb.Execute strSQL, dbFailOnError
   
   '  Lock all controls
Call LockResControls
 
    Me.Requery
    DoCmd.RunCommand acCmdSaveRecord
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
  DoCmd.GoToRecord , "", acLast
cmdSaveReservation_Click_Exit:
    Exit Sub
cmdSaveReservation_Click_Err:
    MsgBox Error$
    Resume cmdSaveReservation_Click_Exit
End Sub

Unfortunately this is not working properly. When I click the save button, the table does not update and I dont get any errors.

Anyone see anything suspect?

 

[pbaldy]

You might try using DoCmd.RunSQL instead of the execute method, which can't resolve the form references. I'd stick with the execute method and concatenate the form references into the string, like:

"WHERE tblReservations.ReservationID=" & [Forms]![frmReservations]![TxtReservationID]

 

[Sketchin]

Worked like a charm!

Thanks alot for the help!

 

[pbaldy]

No problemo!

 

[Sketchin]

Maybe I'm not done yet.... Put this code in an after update, and the msgbox works, aswell as the Me.contactID=0 and requery, but the SQL update code doesn't update my table.

Private Sub cboCompany_AfterUpdate()
Dim strSQL As String
' Original code
MsgBox "Hello", vbOKOnly
strSQL = "UPDATE tblReservations SET tblReservations.CategoryID =" & _
"[Forms]![frmReservations]![txtCategoryID], tblReservations.Portfolio = [Forms]![frmReservations]![chkPortfolio] " & _
"WHERE (((tblReservations.ReservationID)=[Forms]![frmReservations]![TxtReservationID]))"
DoCmd.RunSQL (strSQL)
Me.ContactID = 0
Me.cboContactID.Requery
End Sub

Only using the message box to see if the event is firing...fyi

 

[pbaldy]

Try this technique to see what the finished SQL is and test:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Sketchin]

Got this in the immediate window:

UPDATE tblReservations SET tblReservations.CategoryID =[Forms]![frmReservations]![txtCategoryID], tblReservations.Portfolio = [Forms]![frmReservations]![chkPortfolio] WHERE (((tblReservations.ReservationID)=[Forms]![frmReservations]![TxtReservationID]))

 

[pbaldy]

If you paste that into a new query does it work? Personally I'd concatenate the form references into the string, so that you know what all the values are. At this point, you don't know if the WHERE clause is getting the right value.

 

[Sketchin]

I know that the statement works, because if I put it behind a command button, it updates the table, in after update, it doesn't.

I have had this problem before using an Update Query in an after update event, where it will only run the update if I double click the query or put it in an onClick event, but won't work in the after update event.

Very strange...

 

[Sketchin]

Ok, bear with me....changed the SQL to this:

strSQL = "UPDATE tblReservations SET tblReservations.CategoryID = " & Me.[txtCategoryID] & "  " & _
" ,  tblReservations.Portfolio = " & Me.[chkPortfolio] & " " & _
" WHERE tblReservations.ReservationID = " & Me.[TxtReservationID] & " "

Got this result in the immediate window:

UPDATE tblReservations SET tblReservations.CategoryID = 1 , tblReservations.Portfolio = -1 WHERE tblReservations.ReservationID = 208

Which is totally right! But why isn't it actually updating the table!? Arghhhhhhh.......

 

[pbaldy]

Can you post the db here?

 

[Sketchin]

I would rather email it as it contains some info that I wouldn't want to share with the world.

Does that work?

 

[pbaldy]

Sure:

removed

 

[pbaldy]

How do I recreate the problem?

 

[Sketchin]

Create a new reservation start to finish, then open tblReservations, and see if the field "CategoryID" or "Portfolio" has updated.

I believe the code is currently in the after update of the cboContact ID event

 

[Sketchin]

I should mention, don't click the "Update contact info" button, because that works as my temporary solution!

 

[pbaldy]

I think at the point you select from the dropdown, the record hasn't been saved yet, thus there's no record to update. Try adding this before the SQL runs:

If Me.Dirty Then Me.Dirty = False

 

[Sketchin]

It worked!!! I really can't thank you enough...this has been a nagging issue for me for a couple months now, and now that its solved, I can apply this to a couple other forms where I was having the identical issue!

Thank you thank you thank you!!!

 

[pbaldy]

Happy to help!