Combo box is updating all fields in Sub Form

[cmb]

Hi there,

I have a sub form that adds and displays appointments related to an order. The form has two combo boxes, cboVenderTypeID and cboVenderID and a date picker. There is a 3rd combo box on the parent form called OrderNode. The contents the cboVenderID combo box are filtered by the selections made in cboVenderTypeID and VenderID comb boxes. This works fine and adds the appropriate appointment to tblAppoints. The problem is when I try to add an appointment with of a different type (different value in cboVenderTypeID) all the values in the existing appointments change to the latest value selected in cboVenderTypeID. Any help with my code would be greatly appreciated! Many thanks in advance!

Private Sub cboVenderTypeID_AfterUpdate()

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

If Me.cboVenderTypeID = 4 Then

strSQL = "SELECT tblVenders.VenderID, tblVenders.VenderFullName, tblVenders.VenderTypeID " & _
"FROM tblNodes " & _
"INNER JOIN tblVenders " & _
"ON tblNodes.NodeID = tblVenders.NodeID " & _
"WHERE (((tblVenders.VenderTypeID)=" & Me.[cboVenderTypeID] & ")) " & _
"ORDER BY tblVenders.VenderFullName;"

Else

strSQL = "SELECT tblVenders.VenderID, tblVenders.VenderFullName, tblVenders.VenderTypeID, tblNodes.NodeID " & _
"FROM tblNodes " & _
"INNER JOIN tblVenders " & _
"ON tblNodes.NodeID = tblVenders.NodeID " & _
"WHERE (((tblVenders.VenderTypeID)=" & Me.[cboVenderTypeID] & ") " & _
"AND ((tblNodes.NodeID)=" & [Forms]![frmNavigation]![NavigationSubform]![OrderNode] & ")) " & _
"ORDER BY tblVenders.VenderFullName;"

End If



Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

Me!cboVenderID.RowSource = strSQL
DoCmd.RunCommand acCmdSaveRecord
Me!cboVenderID.Requery

End Sub

[June7]

Possibly the combobox ControlSource is bound to wrong field. What is the RecordSource for the form?

Why run acCmdSaveRecord?

The recordset code is not needed - remove those lines.