I'm having some trouble getting my combo box to only pull the records that correspond to what is selected.... My form is supposed to be for attendance records - it allows the user to remove attendees who will no longer be attending an event. My combo box will hold predefined dates for events (in a table) and I want the user to be able to pick a date and then the data below will be the list of names that are currently marked for attending, then next to each record is a remove button. The names are in their own table w/an integer field that corresponds to the the autonumber in the date table for each date.

My problem is that the combo box either pulls the same records for every date, depending on how the relationships go in the form, or else it will find the records, but it adds an empty field for each date that currently does not have data at the end of the list. After I select each indiv date w/the combo box, it now displays on every date selection, an empty field for each date in that list - ie: there are 7 dates available to choose from, after viewing each of them, all now have 7 empty fields. In the table, these empty fields do not show up, it's only in the form and the query which is really weird... What is wrong w/what I am doing that it shows all these extra fields? My coding and sql are below:


Private Sub cboFindRecord_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DinnerID] = " & Str(Nz(Me![cboFindRecord], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit Sub
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume ExitProc
End Sub

SQL in form:

SELECT tblAttendance.*, tblDinnerDates.*
FROM tblAttendance RIGHT JOIN tblDinnerDates ON tblAttendance.Date = tblDinnerDates.DinnerID;

SQL in combo:

SELECT tblDinnerDates.DinnerID, tblDinnerDates.DinnerDate
FROM tblDinnerDates
ORDER BY tblDinnerDates.DinnerDate;

Please help!!