You could use the on current event of the form with an if statement. If the combo box is not null and if the end date is null, then lock the combo box. Nobody could change it until they put an end date.

Thanks Adam. Does that also prevent me adding a new assignment on another person's record? I.e. Bob's assignment is still active, but we're updating Mary's record and assign her to Bob's room, which should not be available yet.

I tried update queries to mark the rooms as occupied when there is no end date, and mark them as unoccupied when the end date field gets a date. This hides the room in the assignment subform's current record (naturally), so now currentAssignmentSF shows the current active assignment. I moved the original roomAssignmentSF to a pop-up form to force users to create new assignments there. It all appears to work great until I go to create the new assignment. I get an error "need related record in personT, even though I used the button wizard to open roomAssignmentSF to a specific record (and related person_id). Now you see my rabbit hole.

You would just add a little more. Re-thinking here, there's no reason to lock the combo box I guess, you get more functionality if it's unlocked, then you could actually "change" someones room if they wanted to move into another empty room. Not sure if that's what you're going for, but in either case, I think the next step is to base that combo box off a query that only shows rooms that have no active people assigned (you may need to use 2 queries, and/or an aggregate query to get this to display properly). Then the only rooms you could even select would be rooms that are available.

Hiding the assigned room is effectively what I was shooting for in my own solution - see my reply:

<> markOccupiedQ runs after the roomAssignmentT record is modified with a start date, but no end date.
<> This also hides the now occupied room from the current record in the subform.
<> To "fix" the side effect, I created currentAssignmentSF to show the current assignment - and allow editing only.
<> markUnoccupiedQ runs after the currentAssignmentSF record has an end date, allowing the room to appear again in the combobox in roomAssignmentSF.
<> This appears to work well, but now my roomAssignmentSF no longer allows new records (related record rquired in personT message pops up).

It's really hard to advise how to make the query when I don't know how your database works or relationships are setup.

The query is definitely the easier way to do it. Otherwise you'd be looking at locking the field like I mentioned, and probably need to use DCOUNT to check if the room you pick has somebody already assigned to it with no end date in the before update event of the combo box.

I've got a pretty comprehensive algorithm for avoiding conflicting reservations in Access Developer 24.

Thanks Richard, I'll check that out. I'm currently up to Expert 28, and may just take a sneak peak at the Dev24 class.