[RESOLVED] Access Option Buttons and Counter Loop

Hi,

I was working in Access 2007, but was recently upgraded to 2010. I'm sure any advice given would be okay for either version.

I was asked to make a scheduling database for a conference. There are 7 workshops in my database, and participants are supposed to pick in order of preference ie Choice 1, Choice 2, Choice 3, etc. in case their first choices are full. I have option groups representing the choices. There are 49 option buttons in total, 7 representing the workshops and 7 representing each choice. I put validation code in my submit button to make sure the user doesn't try to select two of the same choice.

I'm having trouble with one thing, and this has stumped me for the three years I've been doing it. I try to improve the logic each year but I can never get past this hurdle. If Choice 1 is full, I want to automate the program to pick Choice 2, and all the way down the line. Right now, we're doing it manually. If Choice 1 is full, the user has to manually go in and change their choice 1. I'd like the database to automatically select choice 2 in that case, but I'm unsure how to do it.

I've tried including a counter and for/next loop in my submit button. Am I on the right track? Can I handle this all in one sub or do you think I need to be creating seperate tables for each workshop. I'd appreciate any advice you can give me. Thank you!

Rather than a counter, I would look at creating a query for the workshops, for the current year. This will tell you how many participants you have. You can then that and compair it to the max participants allowed for each workshop (I'd recommend setting up another table for this, it would just contain the workshopID and maxParticipants). I'd also recommend just using combo boxes instead of all the options buttons, 49 buttons on a form is alot to look at, I'd go crosseyed.

I can't really give you more help without getting more information... Does each participant only goto 1 workshop, is there a specific number they goto, or just whatever is available. Are you worried about people dropping out of classes and you want the next person in line to have that position.

Thank you so much for your reply!

Yes, each participant goes to 1 workshop, and they get to choose between the 7. They give us their #1 preference and #2 preference and #3 preference and so on. I do want to consider a disenrollment tool as well if possible, in case someone changes their mind about a workshop. I agree with you about the option buttons. They were a last resort because I didn't know how else to structure. One year, I had combo boxes and about 200 if/then statements trying to account for every possible scenario. That was a total failure.

If I did a separate table, would there be any way to assign a student to their second workshop choice if their first was full, and so on down the line? Again, thanks for your help!

For the comboboxes/if statements, just setup a query for each combobox and in the criteria simply put

Code:

---

Not Like ComboBox2 And Not Like ComboBox3 And Not Like ComboBox4  etc...

---

Then on the after update event for each combobox, run a requery, you may have to do it for each combobox. This will make it so that when they make a choice in a box, it requeries all the other boxes and they will not display that same choice.

For the table, Yes you can. I am getting ready to go, but I think I have a better way for solving that. I'll be on later, or tomorrow morning.

You've just given me a whole new way to look at this database. Instead of mapping out every possible scenario with the preferences, I could just indicate what workshops are available and the user could enroll the student in their first choice that is available. That would simplify things quite a bit. Do you think this is a good idea?

Glad I have you thinking about it. It all depends on how you want to have have the whole thing set up. If you set it up that way, and a class fills up, the next person who comes along will not be able to select it as a choice. If someone later decides to drop the class it would be available to the next person who goes to sign up, which isn't exactly fair, but if that's not a concern it would be a much simpler way of doing it.

Another way would be a variation of what I mentioned above, quering the comboboxes. Setup the comboboxes so that only one of them is enabled at a time. So that when the form is opened they can only select combobox1, once they make that selection, it will enable combobox2 and disable combobox1. This would extremely simplify any queries or if statements. You could also create a clear button that resets all the comboboxes so they can change their choices.

I'll have to get back to you on the rest of it, I've been busy working on other projects at work. I was hoping someone else would pitch in.

Thank you for your input. I really feel like I'm moving to a different level of understanding. Unfortunately, my time for this project is very limited (ie before I leave today), so I'm just going to do the simplified version.

However, I'm very interested in practicing your version. It sounds really good, and I think it's just what I'm looking for to take me to the next level when I build next year's database. When you have the time, I'd like to hear the rest of your advice. I want to make sure I have a complete understanding and a mental image, so I can begin practicing. No rush at all, just whenever you have time. I know work must come first, and it's very nice of you to help, so I'm happy to take what I can get :).

What version of Access are you using??

Here is something I made up quickly for a sample of the comboboxes, I saved it in 2002 version.

I started using Access 2010 very recently. Before that I was using 2007. Thank you!

Thanks for all your help!