Combo box: how to list a range of numbers, and disable/disallow certain selections?

[Oimoi]

Hey again.

I've run into yet another problem on my form, and I'd appreciate any help at all!

There's a text box on my form that is bound to another field called "SeatNumber", which basically reserves a seat for a registering student. The problem is that you have to blindly enter a seat number, without knowing the class' number of available seats AND which ones are already taken.

So what I want to do is make a combo box that lists all available seats, then disables/disallows seats that are already taken. I have a separate table called "Courses" that includes a field called "ClassSize", so I'm assuming I'll have to factor that in somewhere (for example, if ClassSize = 20, then the combo box should somehow list seats 1~20).

Got a nagging feeling that coding's involved, but I don't know where to start. Any hints?

 

[Mr. B]

Try creating a table that will hold one record for each seat in each class.
Something like:

ClassSeatID - AutoNumber
ClassID - FK to Class table
StudentID - FK to Student to link student to a seat
SeatNumber - actual number of seat

you can then build a query to populate your combo box with only records for a specific class where the studentID is null.

Hope this helps.

 

[Oimoi]

Thanks for responding!

And that actually makes a whole lot of sense. Will mess around with that and let you know the results in a little bit. *currently working on something else*

 

[Oimoi]

Ack - so instead of taking class size into account, I've been given permission to just make 30 the max number of seats for every class.

So far I have the table with ClassID, StudentID and SeatNumber, where ClassID and StudentID form the composite primary key.These fields are located in a subform, where ClassID and SeatNumber are combo boxes. After a ClassID is selected, I want the subform to automatically run a query that filters out taken seats in the SeatNumber combo box on the same subform. How exactly do I go about this, and how do I make the combo box list 01 through 30 for every class?

Any help would be appreciated!

 

[Oimoi]

Basically - is it possible to run a query based on a selection in one combo box (ClassID) and then have the filtered results show up in another combo box (SeatNumber) on the same form?

 

[Oimoi]

Any ideas at all would be helpful.

 

[Mr. B]

It is not totally clear to me exactly what you currently have, but I will assume that you have a combo box that curretly displays a list of all of the seats in one class. IF this is the case then you can have this same combo box to display only seats that are not already assigned to a student. I also do not know if you are using a query or just an sql statement to populate that combo box, but in either case you can have the same combo box do the work.

Create a new query that will return a on column list of the seats that have been assigned. Once you have that list being created with this new query, switch to the sql view and copy the sql statement.

Open the original query for your combo box and in the criteria of the Seats column, add and "NOT IN" clause using the new sql statement as the criteria.

The criteria statement would look lik:

Not IN(Select .....)

Inside the parenthese above, just place the sql statement the selects the assigned seats.

Hope this helps.

 

[Oimoi]

Greets Mr. B. Really appreciate you taking the time to look through my nonsense.

To answer your questions, I do have a combo box that lists seats 01 - 30, and I've been trying to create a query that would populate the box with available seats.

Taking your tips into consideration, I now have two separate queries: one that pulls up unavailable/taken seats (A), and another that should pull up available seats (B). I plugged in some random data, and A seems to work fine, where the criteria for SeatNumber = Is Not Null. The criteria for SeatNumber on query B is "Not IN (SQL statement for query A)", but on the form, the combo box is still blank.

I've been trying to run query A in an "After Update" Event Procedure for the ClassID combo box, then have the SeatNumber combo box run query B. Is there a better way to go about this? And can you run a query in VB without having it open in another window (like OpenQuery)?

(Sorry for all the simple questions. Just started Access a couple of weeks ago, so I'm still learning as I go.)

 

[Mr. B]

I was under the impression that you had a table with a list of all of the available seats. I was also assuming that you have a table where you are assigning seats to students. If this is true, your combo box would first list all of the seats available until you create the sql statement that list all of the assigned seats. This query that lists the assigned seats is the statement that goes into the "Not In() in the creteria of the seats field in the query for your combo box.

When your query for your combo box is created correctly, you will not need to run any queries form code. The only code you would need would be to requery the combo box after assigning another seat.