Recordset using VBA

[grundig1987]

Good Afternoon,

I will try and make this as clear as possible, and hope that someone on here can help me!

I have a table titled 'enrolments' - This table has 2 fields, StudentID, and CourseID. It is a one to many table so for example, one record could be:

StudentID B787878 CourseID BSB778

And another could be:

StudentID B787878 CourseID BNS885

In order for students to view the choices they have made, I have set up a form which has 6 text boxes (6 courses is the maximum a student can choose) - In each of these boxes I have code for Dlookup, as shown below:

Module_1 = DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID_4 & "'")

Module_2 = DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID_4 & "'")

Module_3 = DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID_4 & "'")

Module_4 = DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID_4 & "'")

Module_5 = DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID_4 & "'")

Module_6 = DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID_4 & "'")

Understandably each of these boxes only fills in with the first value in the table. Is there a way of using Dlookup to return all of the records that meet the criteria?

I have been searching endlessly for a piece of code to do this, and so far have only found the solution to be a recordset, with the code below:

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("SELECT [TheField] FROM [TheTable] WHERE
TheWhereCondition")
with rs
while not .eof
debug.print ![the field]
.movenext
wend
end with

Using my information, I have put the code as below:

dim db as database, rs as recordset
set db = currentdb()
Set rs = db.OpenRecordset("SELECT [CourseID] FROM [Enrolments] WHERE [StudentID] = '" & Student_ID_4 & "'")
with rs
while not .eof
debug.print ![the field]
.movenext
wend
end with

When I run this code, it brings up 4 forms, and each of the 4 separate forms has the relevant StudentID, but each one has a different module. I am not sure why these are not on the same form? Can anybody help with this?

Thanks in advance,

Mark

 

[Uncle Gizmo]

I'm not sure that what you mean by "Form" and "Module" is the same as what I mean by" Form" and "Module".

Please could you clear up this area of possible misunderstanding.

 

[grundig1987]

I'm not sure that what you mean by "Form" and "Module" is the same as what I mean by" Form" and "Module".

Please could you clear up this area of possible misunderstanding.

Thanks for getting back Uncle Gizmo - Apologies for the confusion; by form, I mean a simple access form which you can put combo boxes/textboxes on etc, in this case, it is where my textboxes are located. By Module, I am not referring to VBA modules for code, but rather a university course i.e. a module during a semester. Please see below for how this should read:

When I run this code, it brings up 4 forms, and each of the 4 separate forms has the relevant StudentID, but each one has a different CourseID. I am not sure why these are not on the same form? Can anybody help with this?

I look forward to hearing back from you.

Thanks,

Mark

 

[grundig1987]

I'm not sure that what you mean by "Form" and "Module" is the same as what I mean by" Form" and "Module".

Please could you clear up this area of possible misunderstanding.

From what I understand, I have now set the recordset to the one which I would like, but am having trouble displaying each record in the recordset in separate textboxes.

The Recordset should be something like this:

B787878 BSB090
B787878 BSB050
B787878 BSB045
B787878 BSB023

This is what I hope I have set with the code below:

dim db as database, rs as recordset
set db = currentdb()
Set rs = db.OpenRecordset("SELECT [CourseID] FROM [Enrolments] WHERE [StudentID] = '" & Student_ID_4 & "'")
with rs
while not .eof
debug.print ![CourseID]
.movenext
wend
end with

What I would like to do now is put each CourseID in 4 Separate text boxes. Hopefully that makes sense.

Cheers,

Mark

 

[jdraw]

More clarification please.
What code exactly are you running?
What 4 forms are brought up? 4 copies of the same form?
What is student_id_4? Is that a text box?

 

[grundig1987]

More clarification please.
What code exactly are you running?
What 4 forms are brought up? 4 copies of the same form?
What is student_id_4? Is that a text box?

I don't understand what you mean by what code I am running??

Basically, I am doing a database which allows students to pick up to a maximum 6 courses across the university year. Firstly, they fill in their own details i.e. name, address, email etc. I then have a button with this code:

If IsNull(DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID & "'")) Then
DoCmd.OpenForm "OptionDetailForm"
Forms![OptionDetailForm]![Student_ID_2] = Me![Student_ID]
Forms![OptionDetailForm]![Student_ID_2].Enabled = True
Forms![OptionDetailForm]![Combo51].Enabled = True
Forms![OptionDetailForm]![Combo54].Enabled = True
Forms![OptionDetailForm]![Combo56].Enabled = True
Forms![OptionDetailForm]![Combo64].Enabled = True
Forms![OptionDetailForm]![Combo66].Enabled = True
Forms![OptionDetailForm]![Combo68].Enabled = True
Forms![OptionDetailForm]![Combo51].SetFocus
Else
DoCmd.OpenForm "ViewExistingOptionChoices"
End If

So basically, if the studentID has not already made their choice of courses, they will be taken to a blank form where they can enter these, else they will be taken to a form which shows their choices, taking them directly from the table they entered them into initially. This is the form I am talking about now i.e. ViewExistingOptionChoices.

Yes it is 4 of the same forms. I don't think it is a coincidence that for this particular studentID, 4 CourseIDs were chosen, although I am sure you will be able to clarify this for me. I have been messing around with the control source of the form, and have set it to the table where the initial option choices go into - This table is called enrolments. For each of the text boxes on the form 'ViewExistingOptionChoices' I have set the control source of the textboxes as the table 'enrolments', with field CourseID, as this is what I want to return. When I open up ViewExistingOptionChoices, I have 4 different forms, each has the same StudentID, but all of the 6 text boxes on one form have the same CourseIDs - When I move to the next form it is a different CourseID, but again, all 6 text boxes are the same, and so on.

And Student_ID_4 is a text box which automatically fills in on the form based on a value taken from elsewhere.

I don't feel like I am being very clear in all honesty but I don't know how I can explain it any better - I am an absolute beginner with this stuff!

Look forward to hearing from you.

Thanks,

Mark

 

[spikepl]

I too am confused as to what is going on - are 4 forms opened by some code? You haven't shown any code that opens forms.

It seems to me that selecting multiple items by using comboboxes is not a very good idea, since you'd eg have to check that the same item does not get selected twice, or remove the item that was selected from all the as yet untouched comboboxes.

WHy not a listbox? Or rather 2? One showing available courses, and on clicking a course, the course would be removed from the list of available courses and be added to the second listbox, showing the student's selection so far? Something similar to whan you select which items you wissh to see in the comob/listbox wizard.

 

[bluetongue]

Why not use two tables witha a form and subform?
This code on the afterUpdate event of the subform, will detect when the student attempts too enter more than six choices.

Private Sub Form_AfterUpdate()
Dim numRecs As Integer
Dim fred As Variant

numRecs = DCount("CourseID", "Enrolments", "key = " & Me.Parent.Key)
If numRecs > 6 Then
fred = MsgBox("You can select no more than 6 records." & vbCrLf & "Either Delete or change an existing record.", vbOKOnly, "Limit Reached")
End If

End Sub

 

[jdraw]

I would suggest 3 tables

1) Student with StudentId as PK
2) Course with CourseID as PK
3) Enrolment with (StudentId+CourseID) as PK .....junction table

A form with 2 combos
cboStudent based on Student
cboCourse based on Course
for data entry/selection of Courses

Logic to ensure no more than 6 courses are selected per Student and no duplicates are chosen.

Don't know if Student must select 6, or, 1 or more with Max(6)


There are video tutorials here that may help put this into context
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[grundig1987]

I would suggest 3 tables

1) Student with StudentId as PK
2) Course with CourseID as PK
3) Enrolment with (StudentId+CourseID) as PK .....junction table

A form with 2 combos
cboStudent based on Student
cboCourse based on Course
for data entry/selection of Courses

Logic to ensure no more than 6 courses are selected per Student and no duplicates are chosen.

Don't know if Student must select 6, or, 1 or more with Max(6)


Merry Christmas all,

Thanks for the help - JDraw - This is how I have set the table out from the very beginning, exactly as you said, and it works great, all the data entry etc, and from what I understand having a table like this is much more efficient, so no problem there, the only problem is showing the table data on a new form.

As simply put as possible, I want my form to show all of the choices a particular student has made. Below you will see what the enrolments table looks like:

StudentID CourseID
B787878 BSB070
B787878 BSB020
B787878 BSC201
B787878 BSG839

On the click of a command button, I do a Dlookup to see if this student has already enrolled on a number of courses, and if they have i.e. B787878 has above, open up a form. The form has 6 text boxes, and all I want to do is a Dlookup for the StudentID in question (which is taken from a previous form automatically so no worries there) and put each of the courses, in the text boxes. So at the top of the form I have the StudentID of the current user. Below this I have 6 text boxes. A minimum of 4 and a maximum of 6 of these boxes should be filled in with each of the CourseIDs the students have chosen. How can I achieve this?

In response to BlueTongue, the entering of courses is already validated in another form - The user will not be able to proceed unless they enter 30 credits for each semester, so again, this is not an issue.

Finally, as SpikePL suggested, unfortunately in one of my previous forms where the student actually makes their choices, I will need to use combo boxes of each of the available courses. I have done this, but am struggling as to what code I require to ensure that after the update of one combo box, the course chosen in that combo box is removed from all other combo boxes?

So in short, I am not having trouble entering the CourseID or StudentID, or for that matter having any problem with data entry at all in that respect; the problem is showing on a separate form, the data that the students have already chosen i.e. showing all CourseID's for one StudentID. On top of this, I do also require that the combo boxes are changed after update to remove either course that have already been chosen. Similarly, there are prerequisites for certain courses, so for example, a student cannot take International Marketing 2, unless they have also chosen International Marketing 1. How would this be achieved using the Combo Boxes.

Thanks in advance, and sorry for all the hassle.

Mark

 

[spikepl]

If you insist on comboboxes, although I don't quite understand why, the record source for each combobox could be a query, which exludes the items already selected in the remaining 5 comboboxes, and in the AfterUpdate event of each combo, you'd requery the other 5.

So the query criterion would be :

"SELECT ..... WHERE StudentID= " & myStudentID & " AND CourseID NOT IN (" & _ Forms![OptionDetailForm]![Combo51] & "," _
Forms![OptionDetailForm]![Combo52] & "," _ ....
Forms![OptionDetailForm]![Combo56] & ")"

 

[spikepl]

Arrgh the thing messed up the formatting ,so here goes again:

"SELECT ..... WHERE StudentID= " & myStudentID & " AND CourseID  NOT IN (" & _  
Forms![OptionDetailForm]![Combo51] & "," &  _ 
Forms![OptionDetailForm]![Combo52] & "," &  _ 
...
Forms![OptionDetailForm]![Combo56] & ")"

 

[spikepl]

And on reflection, this would of course not work in the current shape, for the instances where there are combos without selection SO you'd have to construct the query on the fly. Honestly, why not use a listbox which is more or less custom made for the very thing you want to accomplish?

 

[grundig1987]

And on reflection, this would of course not work in the current shape, for the instances where there are combos without selection SO you'd have to construct the query on the fly. Honestly, why not use a listbox which is more or less custom made for the very thing you want to accomplish?

Spikepl - Thanks for getting back to me! OK - The reason I am slightly hesitant about list boxes is because I had already set up the combo boxes (not knowing any better), and have done the coding behind them, but I suppose if using list boxes will make everything easier it wouldn't take too long to doctor the code accordingly in line with list boxes. So what are the advantages of a list box over a combo box then? Would I not still have to do a query to remove courses from list 2, when say one has been chosen on list 1? I assume this cannot be done automatically? Also, will a list box work for the idea of prerequisites? So as per my previous example, could it be done so that international marketing 2 is not part of any of the list boxes unless international marketing 1 has been chosen?

Thanks in advance,

Mark

 

[spikepl]

First - separate the problems:

1. What is allowed for a student to select
2. How to carry out the selection and display it

So far I have focused on 2. The reason why I recommend a listbox is that you only need 1 (or 2) controls, instead of 6. With one, you could show the selection made so far, and you can select each item only once. With 2, one showing what is available, and one what is selected, you escape from having to write 6 queries. Further, displaying the selection elsewhere is again easy using 1 listbox, instead of having to sprinkle the info over 6 textboxes.

As to issue 1, it seeems to me to be the same, irrespective of how you choose to display (and select) the data. Besides - surely you cannot choose Marketing 1 at the same time as Marketing 2, but Marketing 1 should be on the list of courses of the student, before the student can chose Marketing 2? I would organise this in the table of courses, in a field eg called Prerequisite, as a required precursor to each course, so that the list of available courses displayed for a specific student only shows the courses, whose precursors appear on the student's completed list (if I have understood you requirements correctly).

 

[grundig1987]

First - separate the problems:

1. What is allowed for a student to select
2. How to carry out the selection and display it

So far I have focused on 2. The reason why I recommend a listbox is that you only need 1 (or 2) controls, instead of 6. With one, you could show the selection made so far, and you can select each item only once. With 2, one showing what is available, and one what is selected, you escape from having to write 6 queries. Further, displaying the selection elsewhere is again easy using 1 listbox, instead of having to sprinkle the info over 6 textboxes.

As to issue 1, it seeems to me to be the same, irrespective of how you choose to display (and select) the data. Besides - surely you cannot choose Marketing 1 at the same time as Marketing 2, but Marketing 1 should be on the list of courses of the student, before the student can chose Marketing 2? I would organise this in the table of courses, in a field eg called Prerequisite, as a required precursor to each course, so that the list of available courses displayed for a specific student only shows the courses, whose precursors appear on the student's completed list (if I have understood you requirements correctly).

Spikepl - Initially this throws up a problem - In order to validate the choices by totalling the number of credits each course has, I had some VBA shown below which did this:

CreditsChoice1 = DLookup("[CourseCredits]", "Courses", "[CourseName] = '" & Combo51.Value & "'")
CreditsChoice2 = DLookup("[CourseCredits]", "Courses", "[CourseName] = '" & Combo54.Value & "'") Etc

Is it possible to do this with multiple selection list boxes?

 

[spikepl]

If your listbox displays CourseID (bound field) and then CourseName and CourseCredits, you can extract the values from the selected fields and yell, if the sum of credits selected exceeds the allowed total.

 

[spikepl]

To get stuff out of the columns of a selected row of a listbox (here for column 1):
Dim lngRow As Long
Dim strMsg As String
With Me.lstMyListBox
For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strMsg = strMsg & ", " & .Column(1, lngRow)
End If
Next lngRow
End With

 

[grundig1987]

To get stuff out of the columns of a selected row of a listbox (here for column 1):
Dim lngRow As Long
Dim strMsg As String
With Me.lstMyListBox
For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strMsg = strMsg & ", " & .Column(1, lngRow)
End If
Next lngRow
End With

Spikepl - Thank you for all the advice, and a great bit of code! I have added the code to my database, and just had to make the minor replacement of closing the space in strMsg = strMsg & ", " as it was not able to complete a Dlookup, but apart from that everything seems to be working great! I will not have a go at validating the credits per semester etc, and will most probably get back to you! Great stuff, thanks!

 

[grundig1987]

To get stuff out of the columns of a selected row of a listbox (here for column 1):
Dim lngRow As Long
Dim strMsg As String
With Me.lstMyListBox
For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strMsg = strMsg & ", " & .Column(1, lngRow)
End If
Next lngRow
End With

Spikepl - The list boxes are working great - I have two queries; Semester1courses, and Semester2courses - As the student can pick a maximum of 3 for each Semester I have just done 2 list boxes, one for Semester 1 and one for Semester 2. Everything is now validated so that the user can only pick 30 credits in each semester. I am however still having trouble with the prerequisites, and updating one list box based on the other. For example, if International Business 1 is not selected in list112, then International Business 2 cannot be chosen in list114. How would this be done? It would be handy if I could just have an afterupdate piece of code to do this. I could of course validate through VBA so that if only one of those choices is made it can bring up an error, but I would prefer if things appeared and disappeared from the list boxes accordingly.

Also, I am still having trouble setting up a form which shows the choices that the student has made. The code I have is below - Basically if the Dlookup finds the current student ID (taken from a text box) in the table enrolments, then the form for that student ID is shown, with the appropriate list box choices made.

If IsNull(DLookup("[CourseID]", "Enrolments", "[StudentID] = '" & Student_ID & "'")) Then
DoCmd.OpenForm "OptionDetailForm2"
Forms![OptionDetailForm2]![Student_ID_2] = Me![Student_ID]
Forms![OptionDetailForm2]![Student_ID_2].Enabled = False
Forms![OptionDetailForm2]![List112].Enabled = True
Forms![OptionDetailForm2]![List112].Enabled = True
Else
DoCmd.OpenForm "OptionDetailForm2", , , "[StudentID]='" & Student_ID & "'"
End If

When this form is brought up the list box choices previously made are not highlighted. Is there any way to do this?

Thanks,

Mark