Filtering out items in Combo Boxes

[ScrmingWhisprs]

Need some help here....

I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list.

Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean.

(I have attached a word doc. that shows the table relationships.)


Thanks a lot ahead of time!!
ScrmingWhisprs

 

[67flyer]

it looks like you need to link the spots all table to the shifts subform table. Then on the combo box put critiera on the spot field so that it is checking for a null or blank spot. That should list only the spots that are open.

67

 

[Bodisathva]

Can't look at the attachment, but assume that you are storing the spot/room/date combination on a worker table, so the filter would be:

SELECT DISTINCT Spot 
                FROM SpotsAll 
              WHERE Room = [yourCombo] 
                  AND Spot NOT IN(SELECT Spot 
                                             FROM WorkerTable
                                            WHERE Room=[yourCombo]) 
           ORDER BY Spot

 

[ScrmingWhisprs]

OK,
I did link the two tables, but now how do I write the criteria for that?


Thanks.
ScrmingWhisprs

 

[Bodisathva]

should have been more precise...sorry. The SQL statement above would be (roughly) the actual recordsource for your Spot combo box. You probably need to add some criteria for the date as well, but it would effectively only allow spots in the combo that haven't been filled.

 

[ScrmingWhisprs]

In reply to Bodisathva,

I adapted your code to:

SELECT DISTINCT Spot FROM SpotsAll WHERE Room = [Room] AND Spot NOT IN(SELECT Spot FROM Shifts Subform WHERE Room=[Room]) ORDER BY Spot

The table Shifts Subform is where the Date, Worker, Room, and Spot is stored. Basically, a "transaction table".

However the code did not work. Is there something missing. If you would like, I can email you the database so you can see how it works.

Thanks.
ScrmingWhisprs

 

[Bodisathva]

if Shifts Subform is the name of a table, you must enclose in brackets because of the space in the name --> [Shifts Subform]

 

[ScrmingWhisprs]

I'm not really sure how to structure the criteria for the date. I'm really new at this. A lot of SQL statements that I'm using in this database are borrowed from these forums.

I also tried to enclose Shifts Subforms in brackets, and that did not work either.

I have attached the database so anyone is free to take a look at the structure.


ScrmingWhisprs

 

[Bodisathva]

couple of problems...

1. you have the same field name in multiple tables. In itself it's not a problem, but creates these types of issues when referencing fields in SQL. Access says "Which Spot?" and simply fails the query. To fix, you must use the tableName.fieldName syntax.
2. you needed an additional date constraint so that you were returning unique records. Just because Bob worked in spot1 on Tuesday doesn't mean that Spot1 is filled on Wednesday.

the corrected recordset for the combo is:

SELECT DISTINCT SpotsAll.Spot
                FROM SpotsAll
       WHERE (((SpotsAll.Spot) Not In (SELECT  [Shifts Subform].Spot
                                                       FROM [Shifts Subform]
                                                      WHERE ((([Shifts Subform].Date)=#9/30/2006#) 
                                                           AND (([Shifts Subform].Room)=[Room]));)) 
           AND ((SpotsAll.Room)=[Room]))
   ORDER BY SpotsAll.Spot;

 

[ScrmingWhisprs]

I copied the code into the Rowsource for the Spot combo on the Sign in Form. It didn't seem to work. I am still getting all the Spots in the selected Room, even when that Spot is already given to someone on that given night.

I'm so close, yet so far...

ScrmingWhisprs

 

[Bodisathva]

you did notice that I used a date literal as the constraint for testing purposes, not the log in date, which you will need to apply for your application?

 

[ScrmingWhisprs]

Yes I did notice. And so I selected 10/7/2006 from the Main page to filter the Sign In form to 10/7/2006, and it still did not work.

Did you get it to work? What am I doing wrong?

ScrmingWhisprs

 

[ScrmingWhisprs]

Something that I should point out is that I have some VB code for the AfterUpdate event under the Room combo box on the form so that it populates the Spot combo box with only the Spots that are in that Room. Perhaps this is a good place to restrict Spots from appearing in the box if it is already used for that night.

Private Sub Room_AfterUpdate()
    On Error Resume Next
    Spot.RowSource = "Select SpotsAll.Spot " & _
        "FROM SpotsAll " & _
        "WHERE SpotsAll.Room = '" & Room.Value & "' " & _
        "ORDER BY SpotsAll.Spot;"
End Sub

ScrmingWhisprs

 

[ScrmingWhisprs]

Sorry to bump this up, but I'm still looking for a solution to this problem. Attached is the most recent version of my Haunted Labyrinth worker database. (Use the Shift Key bypass to look at the structure).

I have three combo boxes on a subform on the Sign-In Workers form. One selects the worker, the next one selects the Room they are in, then the last one is populated with the Spots that are in that Room. What I want to happen is if a spot is taken by another worker for that particular night, I don't want it to show up in the Spot combo box when another worker is signing into that room.


Thanks!!
ScrmingWhisprs