Use the Not in List event to set your row source to something different. This event fires AFTER all the change events, so even though you are setting your row source in the change event, you will reset it to the new row source if the user enters a value that is not in the combo box list. You must set the combos LimitToList property to true however, to get the NotInlist event to fire:
Code:
Private Sub txtDate_NotInList(NewData As String, Response As Integer)
txtLeader.RowSource = "SELECT [tblStaffDetails]![txtLName] & "", "" & [tblStaffDetails]![txtFName] & "" - "" & [tblStaffDetails]![txtFaculty] AS Staff FROM tblStaffDetails INNER JOIN tblActivityStaffDate ON tblStaffDetails.Staff_ID=tblActivityStaffDate.[Staff Member] ORDER BY tblStaffDetails.txtLName, tblStaffDetails.txtFName, tblStaffDetails.txtFaculty; "
txtLeader.Enabled = True
If txtLeader.ListCount = 1 Then
txtLeader.Value = txtLeader.ItemData(0)
txtLeader.Enabled = False
End If
End Sub
Private Sub txtDate_Change()
txtLeader.RowSource = "SELECT [tblStaffDetails]![txtLName] & "", "" & [tblStaffDetails]![txtFName] & "" - "" & [tblStaffDetails]![txtFaculty] AS Staff FROM tblStaffDetails INNER JOIN tblActivityStaffDate ON tblStaffDetails.Staff_ID=tblActivityStaffDate.[Staff Member] WHERE (((tblActivityStaffDate.Activity)=Forms.frmNewRegister.txtActivity) And ((tblActivityStaffDate.dteDate)= Forms.frmNewRegister.txtDate)) ORDER BY tblStaffDetails.txtLName, tblStaffDetails.txtFName, tblStaffDetails.txtFaculty; "
txtLeader.Enabled = True
If txtLeader.ListCount = 1 Then
txtLeader.Value = txtLeader.ItemData(0)
txtLeader.Enabled = False
End If
End Sub