Queries based on listbox

[grundig1987]

Hi guys,

I have a multiple select list box (List112), in which students can choose up to 3 courses, out of a possible 8. I have a command button which validates the information using VBA to check that no 'bad' choices have been made. I then take the user to another access form, where they can choose 'backup' choices in case for whatever reason they are not able to get their first choice course(s).

The row source for List112 is a query for all semester 1 modules:

SELECT Courses.CourseName, Courses.CourseCredits
FROM Courses
WHERE (((Courses.CourseSemester)=1));

On the backup choices form (OptionDetailFormBackupChoices), I have 2 combo boxes (Combo58 & Combo60) which allow the student to choose a single 10 credit backup course, and a single 20 credit backup course. For example, Combo58:

SELECT Courses.CourseName, Courses.CourseCredits
FROM Courses
WHERE (((Courses.CourseCredits)=10) AND ((Courses.CourseSemester)=1));

This part of the code works fine, and it returns all Semester 1 courses with 10 credits. HOWEVER, I also want to remove the options that were already chosen in List112 on the previous form (OptionDetailForm2) - Below is the code I have used which concatenates all of the choices made in List112, and then splits them into an array called 'CoursesChosen' -

Private Sub List112_Click()

Dim ar() As String
Dim Count As Integer, i As Integer, j As Integer
Dim Msg As String
Dim CoursesChosen() As String
Count = 0
Msg = ""

For i = 0 To List112.ListCount - 1
'check if the row is selected and add to count
If List112.Selected(i) Then Count = Count + 1
Next i

'based on the above count declare the array
ReDim ar(Count)

j = 0
For i = 0 To List112.ListCount - 1
If List112.Selected(i) Then
'if selected then store the item from the
'first column in the array. change 1 to the
'respective column number
ar(j) = List112.Column(0, i)
j = j + 1
End If
Next i

'Check values stored in array
For i = 0 To Count - 1

MsgBox ar(i)
Msg = Msg & ar(i) & ","

Next i

CoursesChosen = Split(Msg, ",")

What I want to do is add this to the SQL, so something like the below:

SELECT Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses
WHERE (((Courses.CourseCredits)=10) AND (Courses.CourseName) NOT IN ("CoursesChosen") AND ((Courses.CourseSemester)=1));

I know the first thing you are probably thinking is that it won't return anything if I just put 'CoursesChosen' without an array figure, but even if I change this to 'CoursesChosen(0)' or 'CoursesChosen(1)' this does not make a difference.

I hope one of you can help with this. Everything is OK apart from recognition of the values selected in List112.

Thanks,

Mark

 

[hk1]

First off, I recommend you rename your controls to something logical. List112, Combo58, Combo60 are terrible names. Try something relevant like lstCourses, cboBackupCourse1, cboBackupCourse2.

Secondly, it looks like you're leaning to much on code to try to determine what choices are allowed and not allowed. This is very poor design. I think you need to try to use tables/fields more. This system should continue to work even if you are not available to work on it. Can you upload your database? This problem seems too complex to me to be able to answer without seeing the design.

 

[grundig1987]

First off, I recommend you rename your controls to something logical. List112, Combo58, Combo60 are terrible names. Try something relevant like lstCourses, cboBackupCourse1, cboBackupCourse2.

Secondly, it looks like you're leaning to much on code to try to determine what choices are allowed and not allowed. This is very poor design. I think you need to try to use tables/fields more. This system should continue to work even if you are not available to work on it. Can you upload your database? This problem seems too complex to me to be able to answer without seeing the design.

Hk1 - Thanks for the reply - I know what you mean about the logical controls, everything else on my DB is but until I get the code working etc it isn't yet worth changing the controls on the current form. I am having trouble uploading the file, I completely understand what your saying about how it should work even if I am not there, and in this sense I am really having trouble with listboxes. How do you mean using tables and fields more?

Thanks,

Mark

 

[vbaInet]

HOWEVER, I also want to remove the options that were already chosen in List112 on the previous form (OptionDetailForm2) - Below is the code I have used which concatenates all of the choices made in List112, and then splits them into an array called 'CoursesChosen' -

Chosen by whom? The current student or any student? I would imagine the former.

 

[grundig1987]

Chosen by whom? The current student or any student? I would imagine the former.

VbaInet - Correct, it is for the current student. Can you help with this?

Thanks,

Mark

 

[vbaInet]

Do you have a CourseID field?

 

[grundig1987]

Do you have a CourseID field?

VbaInet - Yes I do have a CourseID Fields, it is the primary key.

 

[vbaInet]

Ok, I don't know how you have set this up but you should have a CourseAllocations table and in that table the CourseSemester field should be there also. If this is so, then here:

SELECT PupilID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses INNER JOIN Pupils ON Courses.PupilID = Pupils.PupilID
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 1 AND NOT EXISTS
   (SELECT CourseID
    FROM CourseAllocations
    WHERE CourseAllocations.PupilID = Courses.PupilID AND CourseAllocations.CourseSemester = 1);

 

[vbaInet]

If you are building the sql in string (which is what I would do in this case) then you don't need to join the Pupils table to the Courses table.

SELECT CourseID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 1 AND NOT EXISTS
   (SELECT CourseID
    FROM CourseAllocations
    WHERE CourseAllocations.PupilID = "[COLOR=Red][B] &[COLOR=Navy] Me.txtPupilID[/COLOR] &[/B][/COLOR] " AND CourseAllocations.CourseSemester = 1);

Where txtCourseID is the name of the textbox that holds the Pupil's ID.

Edit: On second thoughts, there's no relationship between the Courses and Students table so this is your only option.

 

[grundig1987]

If you are building the sql in string (which is what I would do in this case) then you don't need to join the Pupils table to the Courses table.

SELECT CourseID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 1 AND NOT EXISTS
   (SELECT CourseID
    FROM CourseAllocations
    WHERE CourseAllocations.PupilID = "[COLOR=Red][B] &[COLOR=Navy] Me.txtPupilID[/COLOR] &[/B][/COLOR] " AND CourseAllocations.CourseSemester = 1);

Where txtCourseID is the name of the textbox that holds the Pupil's ID.

Edit: On second thoughts, there's no relationship between the Courses and Students table so this is your only option.

VbaInet - Thanks for that. Below is the code I have used. I have entered the student number now for the purposes of testing out the code but I will alter this afterwards. It doesn't appear to be working.

SELECT CourseID,Courses.CourseName
FROM Courses INNER JOIN Enrolments ON Courses.CourseID = Enrolments.CourseID;
WHERE Courses.Credits = 10 AND Courses.CourseSemester = 1 AND NOT EXISTS
(SELECT CourseID
FROM Enrolments
WHERE Enrolments.StudentID = "A764582" AND Enrolments.CourseSemester=1)

The error I am getting is 'Characters found after end of SQL statement'

Thanks,

Mark

 

[grundig1987]

Ok, I don't know how you have set this up but you should have a CourseAllocations table and in that table the CourseSemester field should be there also. If this is so, then here:

SELECT PupilID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses INNER JOIN Pupils ON Courses.PupilID = Pupils.PupilID
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 1 AND NOT EXISTS
   (SELECT CourseID
    FROM CourseAllocations
    WHERE CourseAllocations.PupilID = Courses.PupilID AND CourseAllocations.CourseSemester = 1);

Edit: Code slightly altered and now the syntax appears to be incorrect, but the desired results are not being returned. See code below:

SELECT CourseID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 2 AND NOT EXISTS
(SELECT CourseID
FROM Enrolments
WHERE Enrolments.StudentID = "A764582");

The 4 records I have in the Enrolments table are:

StudentID CourseID
A764582 BusPla110
A764582 BusPro120
A764582 BusPro220
A764582 CorFin210

Yet the query is returning no records, even though there are several other courses which satisfy the criteria.

 

[vbaInet]

Minor adjustment:
SELECT CourseID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 2 AND NOT EXISTS
(SELECT Enrolments.CourseID
FROM Enrolments
WHERE Enrolments.StudentID = "A764582" AND Enrolments.CourseID = Courses.CourseID);

 

[grundig1987]

Minor adjustment:
SELECT CourseID, Courses.CourseName, Courses.CourseCredits,Courses.CourseSemester
FROM Courses
WHERE Courses.CourseCredits = 10 AND Courses.CourseSemester = 2 AND NOT EXISTS
(SELECT Enrolments.CourseID
FROM Enrolments
WHERE Enrolments.StudentID = "A764582" AND Enrolments.CourseID = Courses.CourseID);

VbaInet - The code works great thanks, BUT only if I enter the studentID within the quotes - These combo boxes will be on a form titled 'OptionDetailFormBackupChoices' - Also on this form is a textbox called Student_ID_3. When I adjust the code, replacing "A764582" with " & me.Student_ID_3 & " as below, it is once again showing all records, without excluding those that have already been chosen:

SELECT Courses.CourseName
FROM Courses
WHERE (((Courses.CourseCredits)=10) AND ((Courses.CourseSemester)=1) AND ((Exists (SELECT Enrolments.CourseID
FROM Enrolments
WHERE Enrolments.StudentID = " & me.Student_ID_3 & " AND Enrolments.CourseID = Courses.CourseID))=False));

Please advise, nearly there!

Thanks,

Mark

 

[vbaInet]

Here:

SELECT Courses.CourseName
FROM Courses
WHERE (((Courses.CourseCredits)=10) AND ((Courses.CourseSemester)=1) AND ((Exists (SELECT Enrolments.CourseID
FROM Enrolments
WHERE Enrolments.StudentID = [Forms]![OptionDetailFormBackupChoices]![Student_ID_3] AND Enrolments.CourseID = Courses.CourseID))=False));

Put it directly in the row source of the combo box and Requery the combo when necessary.

 

[grundig1987]

Here:

SELECT Courses.CourseName
FROM Courses
WHERE (((Courses.CourseCredits)=10) AND ((Courses.CourseSemester)=1) AND ((Exists (SELECT Enrolments.CourseID
FROM Enrolments
WHERE Enrolments.StudentID = [Forms]![OptionDetailFormBackupChoices]![Student_ID_3] AND Enrolments.CourseID = Courses.CourseID))=False));

Put it directly in the row source of the combo box and Requery the combo when necessary.

VbaInet - Thanks a lot! All sorted now, just out of interest what is the difference between entering the query direct into the row source box, as opposed to linking the query to the row source?

Thanks,

Mark

 

[vbaInet]

No difference. I thought you were building the sql using vba but it's obvious now that you weren't doing that. But if you were building the sql using vba you will use & Me.txtBoxName & then set the sql string to the row source of the combo box.

So it's up to you to put that statement directly in the Row Source, or create a query and set the row source to the query. It's all about which one you find more convenient.