Solved: Student Quiz

[Alexrw]

I'm trying to write a student quiz system using Access (2007) and VBA and i've come into numerous problems. The latest, one I can't just google the answer to, comes in the form of run-time error 424 (object required). I've tried different ways of programming it in (sql, dao etc.) but can't seem to figure it out.

When a teacher assigns homework to a student it needs to create a record in tblMarks of each student that needs to do the quiz. In my database this means it needs to look at tblRegister, find all the students (StudentID) in a certain class (ClassID) which is entered in the form (frmQuiz) in a text box (txtClassID). It then needs to create a new record for each student in tblMarks with the Quiz (QuizID from txtQuizID).

This is as far as I go with it (nothing happens when the button is pressed):
Private Sub cmdAssign_Click()

Dim db As Database
Dim rsr As Recordset
Dim rsm As Recordset

Set db = CurrentDb()
Set rsr = db.OpenRecordset("tblRegister")
Set rsm = db.OpenRecordset("tblMarks")

Do While rsr.EOF = False
If rsr![ClassID] = txtClassID Then
rsm.AddNew
rsm!QuizID = txtQuizID
rsm!StudentID = rsr!StudentID
rsm.Update
rsr.MoveNext
End If
If rsr![ClassID] <> txtClassID Then
rsr.MoveNext
End If
Loop

End Sub

Someone else told me a simpler way of doing it which worked for him but he was using forms instead of tables (run-time error 424):
Private Sub cmdAssign_Click()

DoCmd.OpenTable "tblRegister"
DoCmd.OpenTable "tblMarks"

ClassID.SetFocus
DoCmd.FindRecord txtClassID

If Table![tblRegister].ClassID = Me.txtClassID Then
Table![tblMarks].StudentID = Table![tblRegister].StudentID
Table![tblMarks].QuizID = Me.txtQuizID
End If

End Sub

Thanks for any help. It is very much appreciated.

[OBP]

In the VBA after the .update you should have
.Bookmark = .LastModified

The error that you are getting suggests that you haven't set the necessary VBA Editor's Library References.
When in the VBA Editor you can find them under Main Menu>Tools>References

[Alexrw]

Adding that didn't seem to change anything. Still does nothing after I click the button. Am I just changing values in the recordset and not in the actual table?

I currently have these references loaded:
Visual Basic For Applications
Microsoft Access 12.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 6.0 Library

[OBP]

Ok, you need to add some error handling to your VBA code
Before the first Dim statement add
On Error Goto Err_Command0_Click



and after the Loop add



Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click



If an error occurs that will tell you.
Also change the Dim rsr As Recordset Dim rsm As Recordset
to
Dim rsr As Object

Dim rsm As Object

[Alexrw]

Thanks for the help. Unfortunately it still doesn't come up with anything, not even an error message.

[OBP]

Between the Do While and If rsr![ClassID] = TxtClassID then lines of code
add the following to check what rsr![ClassID] and TxtClassID values actually are
msgbox rsr![ClassID] & " - " & me.TxtClassID

I would choose a TxtClassID that you know is in the Table so that you know it should find something.

[Alexrw]

Yay for progess! Now I get message boxes saying the following:
2-2
3-2
2-2
3-2
2-2
2-2
2-2
1-2
1-2
1-2
3-2
1-2
3-2
3-2
3-2

[OBP]

Change this line
.Bookmark = .LastModified
to
rsm.Bookmark = rsm.LastModified

[OBP]

Looking at the 2 values it should create 5 Records

[Alexrw]

It seems to be doing something but whenever I open up tblMarks after there are no new values. Here's the current code:

Private Sub cmdAssignX_Click()

On Error GoTo Err_Command0_Click

Dim db As Database
Dim rsr As Object
Dim rsm As Object

Set db = CurrentDb()
Set rsr = db.OpenRecordset("tbRegister")
Set rsm = db.OpenRecordset("tbMarks")

Do While rsr.EOF = False
MsgBox rsr![ClassID] & " - " & Me.txtClassID
If rsr![ClassID] = Me.txtClassID Then
rsm.AddNew
rsm!QuizID = Me.txtQuizID
rsm!StudentID = rsr!StudentID
rsm.Update
rsm.Bookmark = rsm.LastModified
rsr.MoveNext
End If
If rsr![ClassID] <> txtClassID Then
rsr.MoveNext
End If
Loop

Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

[OBP]

When using recordsets you sometimes have to close the form to see any table changes, so you need to check that.
I would also change your VBA code to this so the Msgbox only prints those that meet the requirement.
On Error Goto Err_Command0_Click

Dim db As Database
Dim rsr As Object
Dim rsm As Object

Set db = CurrentDb()
Set rsr = db.OpenRecordset("tbRegister")
Set rsm = db.OpenRecordset("tbMarks")

Do While rsr.EOF = False

If rsr.[ClassID] = Me.txtClassID Then
MsgBox rsr![ClassID] & " - " & Me.txtClassID & " - " & Me.txtQuizID
rsm.AddNew
rsm.QuizID = Me.txtQuizID
rsm.StudentID = rsr.StudentID
rsm.Update
rsm.Bookmark = rsm.LastModified

End If

rsr.MoveNext

Loop

Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

[Alexrw]

Nothing shows up now when the button is pressed. There's other fields in tblMarks, could that be a problem? They're not key's at least.

[OBP]

Try changing this line
If rsr.[ClassID] = Me.txtClassID Then
back to
If rsr![ClassID] = Me.txtClassID Then
or
If rsr.ClassID = Me.txtClassID Then

Because this test is not being met, even though you can see from the previous runs that 2 = 2 on 5 occassions.

[OBP]

Of course it would be much easier to help you if you posted a zipped Access 2003 formatted version on here fro us to look at.

[Alexrw]

Thank you very much for your help so far. It's been driving me crackers for a while now. Unfortunately I don't seem to be able to upload it to this site because the file size is too large and when I delete irrelevant parts of the DB it just gets slightly bigger. Megauplaod is the only other site I know of I could upload this too and it keeps coming up with an error.

[OBP]

Alex, you need to Compact & Repair the database prior to Zipping it.
But I can only open Access 2000-2003 databases.

[Alexrw]

Can't get it less than 1.2mb unless I use a .rar format. I could e-mail it to you if you like.

[OBP]

Alex, emailing would be the best bet, I will pm you my email address.
ps Wierd, you do not have Private Mail.

[OBP]

Can you pm me with your email address?

[Alexrw]

It says I don't have permission, try messaging me again.

[OBP]

No your icon doesn't have it.
my email is osborn.ac at googlemail dot com

[OBP]

Alex, it turned out to be a simple one, the value in the form ClassID field is text and the Value in the Table ClassID field is Numeric, so they are never equal. Adding Val() to the Me.txtClassID works fine.

[Alexrw]

Thanks mate, will hopefully be able to get the whole thing finished soon.

[OBP]

Alex, if you use a Combo to select the ClassID you don't need to use the Val().
Actually you still do need it, but it is a more efficient way to do it.

[johnwatkins3]

can i get a copy of your access quiz? my email is johnwatkins35@gmail.com