Recordset using VBA

[grundig1987]

As to the prerequisites: is the entire thing a 2-semester thing? Or longer? For in the latter instance, even the first semester choices are not unlimited, but constrained by the prerequisites? Also, can a course have more than one prerequisite? IF one only, then the obvious place to plug the requirement in would be the course table. If unlimited number, then I'd probably set up a separate table :

Prerequisites
-----------
CourseID (PK)
PrerequisiteID (PK)



the .Selected property is read/write, so you can set it to True for each selected Item, and that will then display your selection.

Is each course available in both semesters?

The listbox with choices for semester 1 should display the courses available in that semester, where the reqirements for prerequisites have been fulfilled for that particular student.

For semester 2, the same, minus the choices made for semester 1.

You could requery listbox 2 each time a choice is made in listbox 1.

The query that is the record source for listbox 2, you would have to construct in the fly, and you could have an additional criterion :

SELECT ..... WHERE CourseID NOT IN (list of course IDs selected in Listbox 1, separated by "," and in parantheses) eg. WHERE CourseID NOT IN (1110, 1111) etc.

to avoid a mutiple display of a given course.

Now the additional challenge is to find the ones that are selected in Listbox 1 and are a prerequisite for the ones to be shown in Listbox 2. "We leave this exercise to the reader"

Spikepl - The entire thing is across 2 semesters, and the choices are different per semester i.e. the same module cannot be chosen in both semesters, so I suppose removing one course from the second list box when it is picked in the first list is actually irrelevant. A course can only have 1 prerequisite - It is also the case that one course in Semester 2 may not be taken if a particular course is taken in Semester 1. So in a way the query will need to not only need to satisfy prerequisite requirements, but also the opposite of prerequisites (whatever that is) - I have no idea how I would even set up a query to satisfy all of that sort of criteria.

International Business 2 (Semester 2) cannot be selected unless International Business 1 (Semester 1) has been chosen.

Business Programming 2 (Semester 2) cannot be selected unless Business Programming 1 (Semester 1) has been chosen.

Decision-Making (Semester 1) & Decision Analysis (Semester 1) cannot both be chosen due to 'common material'

Business Finance (Semester 2) & Corporate Finance (Semester 2) cannot both be chosen due to 'common material'

Business Planning (Semester 1) & Corporate Strategy (Semester 2) cannot both be chosen due to 'common material'

If Management Dissertation 1 (Semester 1) or Management Dissertation 2 (Semester 2) are chosen, the other Management Dissertation must be chosen i.e. both of these must be chosen, or neither!

I have set up a new field in the table 'courses' which is prerequisites - Only 3 of the Semester 2 courses have prerequisites, but I have entered all of the prerequisites in. For the query in list box 2, I have used the table courses, and included fields 'CourseName', 'CourseCredits', 'CourseSemester' - Under 'CourseSemester' I have put the criteria as '= 2' for Semester 2 modules - I have attempted your code but to be honest have no idea where to start - As you can tell I am a complete beginner and am not at all familiar with how this type of query would be structured. Under the field 'CourseName' in the query, I have put the criteria as 'Select CourseName From Courses where prerequisite in (List112)' - This is probably nowhere near correct, but the logic is just to show the course name of courses in the course table where the prerequisite is satisfied in list112 (List box 1) but it says that the syntax of the subquery is incorrect - In fact, even if the syntax was correct I have no doubt this would not return the results I am after! When you said 1110, 1111 etc, what does this mean? For reference, list box number 1 is called 'list112' and list box 2 is called 'list114' - Thanks, apologies for all the pestering but thanks for putting up with it! I don't expect to be spending too much longer on this database but this is an addition which would really help, and is really the only time I have come unstuck.

I have completely validated all of the choices through VBA by the way, so that if 2 courses are chosen which cannot be put together, and the user attempts to submit these choices, they will not be allowed to and will be shown a message as to what criteria are not satisfied, but I think it would be much better if it could be set as we have discussed.

From what I have told you above, and all of the criteria that needs to be satisfied, is it difficult, or even possible, to set up a query to satisfy all of this criterion?

Thanks,

Mark

 

[grundig1987]

As to the prerequisites: is the entire thing a 2-semester thing? Or longer? For in the latter instance, even the first semester choices are not unlimited, but constrained by the prerequisites? Also, can a course have more than one prerequisite? IF one only, then the obvious place to plug the requirement in would be the course table. If unlimited number, then I'd probably set up a separate table :

Prerequisites
-----------
CourseID (PK)
PrerequisiteID (PK)



the .Selected property is read/write, so you can set it to True for each selected Item, and that will then display your selection.

Is each course available in both semesters?

The listbox with choices for semester 1 should display the courses available in that semester, where the reqirements for prerequisites have been fulfilled for that particular student.

For semester 2, the same, minus the choices made for semester 1.

You could requery listbox 2 each time a choice is made in listbox 1.

The query that is the record source for listbox 2, you would have to construct in the fly, and you could have an additional criterion :

SELECT ..... WHERE CourseID NOT IN (list of course IDs selected in Listbox 1, separated by "," and in parantheses) eg. WHERE CourseID NOT IN (1110, 1111) etc.

to avoid a mutiple display of a given course.

Now the additional challenge is to find the ones that are selected in Listbox 1 and are a prerequisite for the ones to be shown in Listbox 2. "We leave this exercise to the reader"

Spikepl - Please see below me SQL:

SELECT Courses.CourseName, Courses.CourseCredits, Courses.CourseSemester
FROM Courses
WHERE (((Courses.CourseSemester)=2) AND ((Courses.Prerequisite) Not In ("list112")));

This does not appear to be working! Any ideas?!

Thanks,

Mark

 

[spikepl]

SELECT ..... WHERE CourseID NOT IN (list of course IDs selected in  Listbox 1, separated by "," and in parantheses) eg. WHERE CourseID NOT  IN (1110, 1111) etc.

So here (1110, 1111) are course IDs selected in Listbox 1, separated by "," and in parantheses, if the CourseID's indeed are 1110 and 1111, and only those two were selected.

 

[spikepl]

As to the ones that cannot be chosen at the same time, you could have yet another column, eg. called "Antiprerequisite" , or perhaps just stick with the current Prerequisite, but add a new column with a number in it, allowing you to interpret the contents of Prerequisite eg. 1= required, 2= disallowed together, etc... It all depends on how many cases in total you have of each, and the expected life-time of your DB. The least elegant but workable solution is simply to run some VBA and check each course against hard-coded requirements for each specific course. The purists will of course object, and rightly so, but if you have to have something running here and now? Again, it depends on the total number of courses.

 

[grundig1987]

As to the ones that cannot be chosen at the same time, you could have yet another column, eg. called "Antiprerequisite" , or perhaps just stick with the current Prerequisite, but add a new column with a number in it, allowing you to interpret the contents of Prerequisite eg. 1= required, 2= disallowed together, etc... It all depends on how many cases in total you have of each, and the expected life-time of your DB. The least elegant but workable solution is simply to run some VBA and check each course against hard-coded requirements for each specific course. The purists will of course object, and rightly so, but if you have to have something running here and now? Again, it depends on the total number of courses.

Spikepl - I will have a go at having two columns then, one prerequisite, one antiprerequisite, or something like that! This is what the Courses Table now looks like:

Course Name CourseID Prerequisite AntiPrerequisite

I have put the name of prerequisite courses in the prerequisite column, and for those courses that cannot be chosen at the same time as others, I have put the name of the course in the AntiPrerequisite column.

Other than the parenthesis error, is the code below OK?

SELECT Courses.CourseName, Courses.CourseCredits, Courses.CourseSemester
FROM Courses
WHERE (((Courses.CourseSemester)=2) AND ((Courses.Prerequisite) Not In ("list112")));

The part I am struggling with is understanding how I can enter the values that have been chosen in listbox112, instead of putting in 'list112' - Bare in mind that at this point no records will have been added to the enrolments table. The choices in list112 will simply be highlighted.

Thanks,

Mark

 

[grundig1987]

Spikepl - I will have a go at having two columns then, one prerequisite, one antiprerequisite, or something like that! This is what the Courses Table now looks like:

Course Name CourseID Prerequisite AntiPrerequisite

I have put the name of prerequisite courses in the prerequisite column, and for those courses that cannot be chosen at the same time as others, I have put the name of the course in the AntiPrerequisite column.

Other than the parenthesis error, is the code below OK?

SELECT Courses.CourseName, Courses.CourseCredits, Courses.CourseSemester
FROM Courses
WHERE (((Courses.CourseSemester)=2) AND ((Courses.Prerequisite) Not In ("list112")));

The part I am struggling with is understanding how I can enter the values that have been chosen in listbox112, instead of putting in 'list112' - Bare in mind that at this point no records will have been added to the enrolments table. The choices in list112 will simply be highlighted.

Thanks,

Mark

I have had a look at the select property thing but do not know how to define something that is selected. As an example, the top row in list112 is 'Business Planning 1'.

What should I put after if me.list112.selected = true then?

Surely not:
if me.list112.selected = true then
Prerequisite = "Whatever the selected option is"

I'm getting there but just need a little more guidance!

Thanks,

Mark