Filtering what a drop-down displays

[James]

I have a drop-down box that I only want to display values from this criteria:

Based on these tables:
tblEmployee (EmployeeID - PK) with a 1-m relationship to tblVacation
(VacationID - PK, EmployeeID - FK)

The main form has a date on it (from tblDates). The drop-down is on a
sub-form. If the date on the form and any date from the tblVacation match,
then don't show the Employee's name in the list.

How do you do this, or is it possible?

 

[Marshall Barton]

I have a drop-down box that I only want to display values from this criteria:

Based on these tables:
tblEmployee (EmployeeID - PK) with a 1-m relationship to tblVacation
(VacationID - PK, EmployeeID - FK)

The main form has a date on it (from tblDates). The drop-down is on a
sub-form. If the date on the form and any date from the tblVacation match,
then don't show the Employee's name in the list.

Not at all sure what you're doing here, but try this kind of
query for the combo box's row source:

SELECT E.EmployeeID, E.EmployeeName
FROM tblEmployee As E LEFT JOIN tblVacation As V
ON V.EmployeeID = E.EmployeeID
WHERE V.thedatefield <> Forms!mainform.txtdate

Be sure to requery the subform's combo box whenever the
txtdate text box (on the main form) value is changed.

Me.subformcontrol.Form.thecombobox.Requery

 

[James]

Thanks for your help! That was exaclty what I needed. I would like to go
one more step. I tried to add another factor, but it will not show any
records now. I tried OR instead and shows all of the records. What am I
doing wrong?

txtShiftID is not a bound control.

SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name FROM tblEmployee
AS E LEFT JOIN tblVacation AS V ON E.EmployeeID=V.EmployeeID WHERE
(((V.VacationDate)<>Forms!frmSchedule.txtDate) And
((V.ShiftID)<>Forms!frmSchedule.txtShiftID)) ORDER BY E.FirstName,
E.LastName;

 

[James]

Something I noticed is that I can put values in instead of the control names
and it works, but when I use the contol names nothing is diplayed in the
dropdown list.

 

[Marshall Barton]

Are you sure the form, frmSchedule, is open?

Double check the field type to make sure it matches the text
box value.

 

[Marshall Barton]

Using AND means you only want a record to match when both
fields are different then the form controls. I would guess
that this is what you want. Are you sure you have at least
one record like this? Try test it by entering bizarre
values in the form controls.

Using OR will match all the records where either field is
different from the form controls.

I am still pretty vague about what you're trying to do, so I
can't tell which one you want, or do you want something
else?
--
Marsh
MVP [MS Access]

Thanks for your help! That was exaclty what I needed. I would like to go
one more step. I tried to add another factor, but it will not show any
records now. I tried OR instead and shows all of the records. What am I
doing wrong?

txtShiftID is not a bound control.

SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name FROM tblEmployee
AS E LEFT JOIN tblVacation AS V ON E.EmployeeID=V.EmployeeID WHERE
(((V.VacationDate)<>Forms!frmSchedule.txtDate) And
((V.ShiftID)<>Forms!frmSchedule.txtShiftID)) ORDER BY E.FirstName,
E.LastName;

Not at all sure what you're doing here, but try this kind of
query for the combo box's row source:

SELECT E.EmployeeID, E.EmployeeName
FROM tblEmployee As E LEFT JOIN tblVacation As V
ON V.EmployeeID = E.EmployeeID
WHERE V.thedatefield <> Forms!mainform.txtdate

Be sure to requery the subform's combo box whenever the
txtdate text box (on the main form) value is changed.

Me.subformcontrol.Form.thecombobox.Requery