Solved Update command using primary key updates the correct record but also updates first record (1 Viewer)

[Brother Arnold]

I suspect this comes down to a lack of understanding but I've spent a couple of hours going down rabbit holes so I think I need a bit of help before I let it ruin my evening!

This is my update statement:
DoCmd.RunSQL "Update tblAppointment SET tblAppointment.appointment_notes = txtApptNotes WHERE tblAppointment.ID = " & p_lngID

So what is happening here is that I'm updating the notes field for a specific appointment record. The primary key is ID. What actually happens is it updates the record that belongs to the primary key OK but it is also overwriting the appointment_notes in the first record in the appointment table which is the problem. I've rewritten the code using .execute and got the same problem which makes me think it's not the code. The form is about as simple as it gets - a text box and a save button. The form's record source and type is tblAppointment and Dynaset. the text box txtApptNotes is bound to appointment_notes. Any help would be much appreciated.

 

[Ranman256]

that is correct IF you are viewing only 1 record, and if p_LngID is the name of the textbox on the form bound to ID field.
seems to me it would be: txtID.

If you have list of records, the cursor must be on the record you want to update.

 

[jdraw]

If the record in your table has appointment_notes say "This is a test.", and you have a form to collect notes in control txtAppNotes. Let's say the txtAppNotes is "Hello World!"
The update statement you have will (I think) replace "This is a test." with "Hello World!". If I understand your concern, you want to add/append Hello World to the already existing notes value. The expected output would be something like "This is a test. Hello World!".
If that is the requirement, then you would need to take the existing Note value and the new note and update the record.

I mocked up an example. Below is the relevant vba code. I used the after update event of txtApptNotes

Private Sub txtApptNotes_AfterUpdate()
    On Error GoTo txtApptNotes_AfterUpdate_Error
    Dim sql As String
    sql = "Update tblAppointment  Set tblAppointment.appointment_Notes = tblAppointment.appointment_Notes & '" & txtApptNotes & "' WHERE tblAppointment.ID = " & Me.p_lngID
    If Not IsNull(Me.txtApptNotes) Then
    Debug.Print Me.txtApptNotes & vbCrLf & sql
        CurrentDb.Execute sql, dbFailOnError
    End If
    
    On Error GoTo 0
txtApptNotes_AfterUpdate_Exit:
    Exit Sub

txtApptNotes_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure txtApptNotes_AfterUpdate" _
        & "  Module  Form_FRMtblAppointment "
    GoTo txtApptNotes_AfterUpdate_Exit
End Sub

 

[Brother Arnold]

If the record in your table has appointment_notes say "This is a test.", and you have a form to collect notes in control txtAppNotes. Let's say the txtAppNotes is "Hello World!"
The update statement you have will (I think) replace "This is a test." with "Hello World!". If I understand your concern, you want to add/append Hello World to the already existing notes value. The expected output would be something like "This is a test. Hello World!".
If that is the requirement, then you would need to take the existing Note value and the new note and update the record.

I mocked up an example. Below is the relevant vba code. I used the after update event of txtApptNotes

Private Sub txtApptNotes_AfterUpdate()
    On Error GoTo txtApptNotes_AfterUpdate_Error
    Dim sql As String
    sql = "Update tblAppointment  Set tblAppointment.appointment_Notes = tblAppointment.appointment_Notes & '" & txtApptNotes & "' WHERE tblAppointment.ID = " & Me.p_lngID
    If Not IsNull(Me.txtApptNotes) Then
    Debug.Print Me.txtApptNotes & vbCrLf & sql
        CurrentDb.Execute sql, dbFailOnError
    End If
   
    On Error GoTo 0
txtApptNotes_AfterUpdate_Exit:
    Exit Sub

txtApptNotes_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure txtApptNotes_AfterUpdate" _
        & "  Module  Form_FRMtblAppointment "
    GoTo txtApptNotes_AfterUpdate_Exit
End Sub

Thanks jdraw. I'm a relative beginner with Access so it's interesting to see how you write your code. I've learnt a few things from picking it apart so thanks! HoweverI think Ranman256 has nailed the problem but I don't know how to fix it.

 

[jdraw]

Perhaps you could describe the problem in plain English.

My interpretation was your Note was being overwritten, and you didn't want that. I thought you wanted to add to the Note that already existed for that record.

I did not follow this in ranman's response.

...if p_LngID is the name of the textbox on the form bound to ID field.
seems to me it would be: txtID.

 

[Brother Arnold]

that is correct IF you are viewing only 1 record, and if p_LngID is the name of the textbox on the form bound to ID field.
seems to me it would be: txtID.

If you have list of records, the cursor must be on the record you want to update.

I think you might have found the problem. The txtApptNotes is bound to the appointment_notes field in tblAppointment. I haven't bound anything to the ID. How do I correct that?

 

[Brother Arnold]

Perhaps you could describe the problem in plain English.

My interpretation was your Note was being overwritten, and you didn't want that. I thought you wanted to add to the Note that already existed for that record.

Sorry, the problem was whatever record I chose to update it would always update record 1 as well by overwriting the notes field. I didn't want to update record 1 at all. Since posting the issue I deleted every line of code except the update statement and changed p_lngID to 10. The result was it updated record 1 but not 10 so I think the issue now is how I binded the txtApptNotes and not the ID. I don't know how to rectify it though.

 

[Gasman]

If the notes field is bound, why the update statement?

 

[jdraw]

It seems that p_lngID was equal 1 and that's why record with ID =1 was updated regardless of which record you wanted to update. However, if you set it to 10 and record 1 still gets updated "who nows what is happening"?

Perhaps you could post a copy of the database.

Update: Just saw gasman's response: Yes if it's a bound form, the record will be updated when you leave the record and changes are made without using an Update query.

 

[Brother Arnold]

If the notes field is bound, why the update statement?

Oh because I'm an idiot! Thank you. I blame the heat. I removed the binding and it worked first time. Doh! On the bright side jdraw has given me some ideas on how to improve my error handling. Sorry everyone for wasting your time.

 

[Gasman]

I would have left it bound and just updated via the form?

Oh because I'm an idiot! Thank you. I blame the heat. I removed the binding and it worked first time. Doh! On the bright side jdraw has given me some ideas on how to improve my error handling. Sorry everyone for wasting your time.