Need Help With Multiple Conditions and Constraints for Access 2007 VBA Event

115 pts.
Tags:
Microsoft Access
Microsoft Access 2007
VBA
Visual Basic
Hi, I’m working with VBA for On Click Event procedures in Access 2007. My knowledge of Visual Basic is rudimentary and I've come against a business rule I can't even begin to figure out how to code. I work at a college and was asked to build a database for a student conference we're hosting. One of the necessary functionalities it needs to have is the ability to schedule various conference workshops for the students. There are seven total workshops and six of them will be attended by all students, so that was easy to code. The problem is that the seventh workshop will vary. For the seventh workshop, students were given five different choices and asked to rank them in order of preference. Since there is a cap on the number of students allowed in each workshop, it's not a guarantee that students will get their first choice (so I can't code a simple if/then statement). I need a way to go through each student's choice from 1 to 5 and put them in the workshop closest to choice 1 that isn't full (full is 25 for some workshops, 40 for others). I've tried looping (didn't work and crashed my program), if/then statements which gave students their first choice whether the workshop was full or not and I'm at the end of my rope. I've tried counters declared at the module level but nothing has worked for me. I don't even know enough to figure out how to research the question. Here's a sample of my code. I know it's wrong and it doesn't work but I can't figure out how to make it right: the five workshops are called CollegeAthletes (full at 40), CollegeFairs (2 sections each full at 25), CollegeMajors (2 sections each full at 25),CollegeLingo (2 sections each full at 25), and ApplicationEssays (2 sections each full at 25) Here's just a sample of this monstrosity. There are about 50 lines more but I just want to give you an overview of one section so you'll know how I'm coding. If CollegeAthletes=1 And Me.CollegeAthletes.ItemSelected.Count <40 Then "College Athletes" If CollegeAthletes=1 And Me.CollegeAthletes.ItemSelected.Count >40 And CollegeFairs=2 Then "College Fairs" If CollegeAthletes=1 And Me.ItemSelected.Count >40 And CollegeFairs=2 And Me.CollegeFairs.ItemSelected.Count >25 And CollegeMajors=3 Then "College Majors" If CollegeAthletes=1 And Me.CollegeAthletes.ItemSelected.Count >40 And CollegeFairs=2 And Me.CollegeFairs.ItemSelected.Count >25 And CollegeMajors=3 And Me.CollegeMajors.ItemSelected.Count >25 And CollegeLingo=4 Then "College Lingo" If CollegeAthletes=1 And Me.CollegeAthletes.ItemSelected.Count >40 And CollegeFairs=2 And Me.CollegeFairs.ItemSelected.Count >25 And CollegeMajors=3 And Me.CollegeMajors.ItemSelected.Count >25 And CollegeLingo=4 And Me.CollegeLingo.ItemSelected >25 And ApplicationEssays=5 Then "Application Essays" Please Help! Thank you in advance!!

Answer Wiki

Thanks. We'll let you know when a new response is added.

From a design point of view, i would do it like this:
Students requests are held in a table with student_id, choice_no, choice_value, assigned
Workshops are held in table with workshop, section, capacity
workshop_assignees are held in table workshop, section, student_id

then have your procedure

sub process_requests

for each student not assigned fetch student_id, choice_no, choice_value into an array order by choice_no

for a = 0 to 4

create new recordset and open for select wa.section, w.capacity,
count(wa.student_id) stCount
from workshop_assignees wa, workshops w
where wa.workshop = w.workshop and wa.section = w.section
and workshop = array(a).fields(2) .value
group by section, capacity
order by section

now loop through the workshop sections
for b = 1 to rs.count
if rs!stCount < rs!Capacity
space available, so assign student to workshop and section
insert into workshop_assignees (workshop, section, student_id) values (array(a).fields(2),value,
rs!section.value, array(a).fields(0).value)
update student_requests set assigned = ‘Y’ where student_id = array(a).fields(0).value and
choice_value = array(a).fields(2).value
force out of loops
b = rs.count
a = 4
end if
next b
close rs
next a
next student

Using this logic, it will check if there is space available, and then assign students to workshops if there is space. Let us know if you need further help.

Thanks,
Darryn

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dataempress
    Thank you for answering Darryn. I really appreciate your time! I just have a couple of follow up questions. My database right now has one big student table. If I broke them up like you suggested, I would need 4 tables including the one where I keep the student information, such as address and phone number. Would I need to create relationships among all these tables? And would they all need to be 1 to Many. I'm good at designing queries and reports, but normalization is very confusing for me. Also, if this (sub process_requests) is my procedure, do I just write it like that or do I have to declare it like Public or Private? I'm also not sure how to create or open recordsets in VBA or what to list in an array or how to loop. Would the loop you've suggested be a Do/While loop or a For/Next one? I've taken one class in VB and read about Arrays and looping. I just don't understand them. Any clarification you could give me would be much appreciated. Thank you!
    115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following