Query tweak

[BRZ-Ryan]

This db is for scheduling employees to go to events. I'm trying to make it easier for the schedulers to only populate the people combobox with people who are not already scheduled for the date they are currently scheduling (the combo is populated after a date is chosed on the form, which is Forms!Navigation!NavigationSubform.Form!EventDate) . Forms!Navigation!NavigationSubform.Form!txtID is the current event being entered.



-When an event is created, the Events table holds the ID of the event and the event date
-When a person is assigned to an event, a record is created in attendance table with the Event ID and Security ID

My logic is:

Where not exists 
-Event for same as selected date
-Event ID <> current event ID
-EventID = Attendance.EventID AND Security.ID = Attendance.EmployeeID

Tables:

Events
-ID
-EventDate

Security
-ID
-FirstName
-LastName

Attendance
-EventID (=Events.ID)
-EmployeeID (=Security.ID)

What I added to the existing query

    AND Exists (
     SELECT Security.ID 
     FROM Security, Events, Attendance 
     WHERE ((Events.EventDate = Forms!Navigation!NavigationSubform.Form!EventDate) AND 
      (Events.ID <> Forms!Navigation!NavigationSubform.Form!txtID)) AND 
      (Events.ID=Attendance.EventID AND 
      Attendance.EmployeeID = Security.ID))=False))

[June7]

What is your question?

[BRZ-Ryan]

What is your question?

This db is for scheduling employees to go to events. I'm trying to make it easier for the schedulers to only populate the people combobox with people who are not already scheduled for the date they are currently scheduling (the combo is populated after a date is chosed on the form, which is Forms!Navigation!NavigationSubform.Form!EventDate) . Forms!Navigation!NavigationSubform.Form!txtID is the current event being entered.

So assume I am already scheduled for an event in the db on 1/24/14

A scheduler starts entering an event, picks 1/24/14 as the date, the comobox is requeried and it should not include me because I have an existing event out there on this same day already.

[June7]

This gets complicated. The technique to accomplish has been discussed in a number of threads, finding them again is a challenge. Here is one https://www.accessforums.net/forms/h...ist-37486.html

First, the record must be committed to table for the info to be available for combobox filtering.

DoCmd.RunCommand acCmdSaveRecord

[BRZ-Ryan]

I haven't dug through that thread yet, but based on the subject I am not looking to remove an item from the combobox (though that would be cool) once they are selected, I am looking to initially populate it with people who are not yet scheduled on that date. The date selection is required before selecting attendees so the date is there for checking in the query.

1. Create event
2. Pick date
3. Populate attendees who are not yet linked to an event on selected date in step 2

[June7]

You want to select a date and have the combobox requery to show only people who are not already scheduled for that date.

Sorry, misstatement about committing the record first.

However, the technique attempting to describe in that referenced thread is still relevant. I know I've gone through this more than once. Seem to have touched on it in your other thread https://www.accessforums.net/queries...17/index2.html - go to post 23.