The data hasbeen changed ........ re edit

[fileman1]

I keep having an occasional notice coming up saying "The data has been changed. Another user edited the record and saved the changes before you attempted to save your changes Re Edit the record. There is only me on PC.

I have a sub form with 3 controls on it. A combo control, List box and command button. The combo has it's rows source to a table full of peoples names. The idea is a user opens the combo and selects a name. This is then saved to a table related to the main form/table. The list box row source is then updated with SQL query.
If a user selects a name in the list box, the command button is enabled, which allows the selected item to be deleted from the list box/table.
There is a pattern to the problem showing up. If a user adds names to the listbox via the combo, and then deletes them all, when he clicks the combo again to re enter records the warning message box appears "The data ....."

Code - The Combo

Private Sub Combo3_Click()
Dim mysql As String
  Set db = CurrentDb()
  Dim rs2 As DAO.Recordset
  Set rs2 = db.OpenRecordset("Advisors", dbOpenDynaset)


If vbYes = MsgBox("Do you want to associate record with selected Advisor?", _
     VbMsgBoxStyle.vbYesNo Or VbMsgBoxStyle.vbQuestion) Then

  rs2.FindFirst "ID1 = " & Me.ID1 & " AND Advisor = '" & Me.Combo3.Column(1) & "'"

  If rs2.NoMatch = False Then
      Me.Combo3.Requery
      Set rs2 = Nothing
      Me.Dummy.SetFocus
      Exit Sub
  End If

  rs2.AddNew
  rs2!ID1 = Me.ID1
  rs2!Advisor = Me.Combo3.Column(1)
  rs2.Update

    mysql = "SELECT Advisors.ID1, Advisors.Advisor FROM Advisors"
    mysql = mysql & "  WHERE (Advisors.ID1)=" & ID1 & ";"
    Me.List0.RowSource = mysql

      Set rs2 = Nothing
      Me.Combo3.Requery
      Me.Combo3 = Null
      Me.Dummy.SetFocus
    End Sub

Code - Delete Button

Private Sub Delete1_Click()
    Dim mysql As String
    Set db = CurrentDb()
    Dim rs2 As DAO.Recordset
    Set rs2 = db.OpenRecordset("Advisors", dbOpenDynaset)

    rs2.FindFirst "ID1 = " & Me.ID1 & " AND Advisor = '" & Me.List0.Column(1) & "'"

    If rs2.NoMatch = False Then
        rs2.Delete
    End If

    Set rs2 = Nothing

    mysql = "SELECT Advisors.ID1, Advisors.Advisor FROM Advisors"
    mysql = mysql & "  WHERE (Advisors.ID1)=" & ID1 & ";"
    Me.List0.RowSource = mysql

    Me.Delete1.Enabled = False
    Me.List0 = Null
End Sub

Code - List Click

Private Sub List0_Click()
Me.Delete1.Enabled = True
End Sub

Code - Sub Form On Current

Dim MySql as string
mysql = "SELECT Advisors.ID1, Advisors.Advisor FROM Advisors"
    mysql = mysql & "  WHERE (Advisors.ID1)=" & ID1 & ";"
    Me.List0.RowSource = mysql
etc etc

 

[fileman1]

In addition, sometimes after being told the previous message, if I continue I get a further message - Error 3077 Syntax error (missing operator) in expression

rs2.FindFirst "ID1 = " & Me.ID1 & " AND Advisor = '" & Me.Combo3.Column(1) & "'"

It cannot be the list is empty as one needs to click on a list item for the Delete button to be enabled.