CODEWITHSUNDEEP
sqlvbams-access
Syamil Fuad
Syamil Fuad

Reputation: 33

access vba update one table, multiple fields

Help, i want to update my table PARKING with the field PARKING_STATUS value UPDATED but I got error. I fetch everything from the same table. All fields at the form are from the same table PARKING, with PK and FK with other table. This is the code:

Private Sub Approve_Btn_Click()

    'update data into table(s)
     CurrentDb.Execute "UPDATE PARKING" 
    "SET(parking_id= '" & Me.lot_num_dropdown & "', parking_status = '" & "UPDATED" & "', car_reg_num = '" &    Me.car_reg_num_dropdown & "', result_id = '" & Me.result_id_dropdown & "')" & _
    "WHERE parking_id = '" & Me.lot_num_dropdown & "'"

End Sub

Upvotes: 1

Views: 5738

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider the safer, cleaner, and more maintainable version with querydef parameterziation. No quotes or string concatenation is required.

Simply bind parameter values to a prepared SQL statement which alternatively can be a saved query in database instead of VBA string.

Private Sub Approve_Btn_Click()
    Dim qdef As QueryDef

    sql = "PARAMETERS idparam LONG, statusparam TEXT(255), carregparam TEXT(255), resultidparam LONG;" _
           & " UPDATE PARKING SET parking_id = [idparam], parking_status = [statusparam]," _
           & "                    car_reg_num = [carregparam], result_id = [resultidparram]" _
           & " WHERE parking_id = [idparam]"

    ' assign prepared statement to qdef object
     Set qdef = CurrentDb.CreateQueryDef("", sql)  'OR Set qdef = CurrentDb.QueryDefs("mySavedQuery")

     ' bind named parameters
     qdef!idparam = Me.lot_num_dropdown 
     qdef!statusparam = "UPDATED"
     qdef!carregparam = Me.car_reg_num_dropdown  
     qdef!resultidparam = Me.result_id_dropdown

     ' execute action query
     qdef.Execute dbFailOnError

     Set qdef = Nothing
End Sub

Upvotes: 2

Related Questions