Action query won't run

[Rowen]

I hope someone can help, I have a data entry form with save button, and I want to set it up so that the user doesn't have to worry about if they've already saved the details or not. They just press save and it'll insert a new record, or if they make amendments to what's been entered on the form then it'll simply update the record instead.

However my update statement doesn't seem to want to work. I have an IF statment with the sql update in the else statement. I know that this is being triggered as the message box appears, but the sql statement isn't executing. I've tested the query in SQL and it works fine, but it doesn't seem to want to execute in VBA.

The code is:

If DCount("EstateInspection", "qryCheckEstateInspection") = 0 Then
         Call saveReading
         MsgBox "Inspection details saved. "
 
  Else
'  DoCmd.SetWarnings True
 
strSQL2 = "Update tblEstateInspections " & _
              "Set [LobbyStairs] = '" & [Forms]!frmInspection.cboOne & "' , [WallsCeilings] = '" & [Forms]!frmInspection.cboTwo & "' " & _
              ", CommunalLighting = '" & [Forms]!frmInspection.cboFour & "' , Grounds = '" & [Forms]!frmInspection.cboFive & "' " & _
               ", ChuteRoom = '" & [Forms]!frmInspection.cboSix & "' , CommunalWindows = '" & [Forms]!frmInspection.cboThree & "' " & _
               ", EntryPhone = '" & [Forms]!frmInspection.cboSeven & "' , GroundsMaintenence = '" & [Forms]!frmInspection.chkGrounds & "' " & _
             ", ASBInBlock = '" & [Forms]!frmInspection.chkSocial & "' , AbandondVehicles = '" & [Forms]!frmInspection.chkStreetClean & "' " & _
              "Where [StairID] = " & [Forms]!frmInspection.cboStairs & " " & _
                     "and Inspectiondate = " & [Forms]!frmInspection.txtDate
 
   DoCmd.RunSQL strSQL2
   Debug.Print strSQL2
      MsgBox "Updated."
  End If

 

[DCrake]

strSQL2 = "Update tblEstateInspections " & _
              "Set [LobbyStairs] = '" & [Forms]!frmInspection.cboOne & "' , [WallsCeilings] = '" & [Forms]!frmInspection.cboTwo & "' " & _
              ", CommunalLighting = '" & [Forms]!frmInspection.cboFour & "' , Grounds = '" & [Forms]!frmInspection.cboFive & "' " & _
               ", ChuteRoom = '" & [Forms]!frmInspection.cboSix & "' , CommunalWindows = '" & [Forms]!frmInspection.cboThree & "' " & _
               ", EntryPhone = '" & [Forms]!frmInspection.cboSeven & "' , GroundsMaintenence = '" & [Forms]!frmInspection.chkGrounds & "' " & _
             ", ASBInBlock = '" & [Forms]!frmInspection.chkSocial & "' , AbandondVehicles = '" & [Forms]!frmInspection.chkStreetClean & "' " & _
              "Where [StairID] = " & [Forms]!frmInspection.cboStairs & " " & _
                     "and Inspectiondate = " & [Forms]!frmInspection.txtDate

If you are running this from within the frmInspection form you can dispense with the [Forms]!frmInspection prefix and replace with Me

also you inspectiondate field needs # around it

and Inspectiondate = #" & Me.txtDate & "#"

You are also calling SaveReading is this doing something else?

David

 

[Rowen]

Thanks so much! It's working now.

I do usually use Me but for some reason I just didn't think about it in the SQL query itself. I know about putting ' around text fields, but it didn't occur to me that a date field would need something to, but that seems to have done the trick.

SaveReading was just inserting the data into the table if it wasn't already there.