Unanswered: This sub crashes Access! What is wrong?
Oh boy! Problems after problems!
While trying to get this sub to work properly and fixing the sql strings (Thanks Cal), suddenly Access hang up on me. When debuging Access does not report any errors, but at run time Access just stops responding. I desperately need help because the deadline is approaching. My comments should help you understand what is going on. In a nutshell, if a student pays for a full program, when he registers for an individual course (part of the program) he should not pay anything.
Thanks in advance for help and ideas.
Private Sub CourseId_Click()
' after the course is selected check if the student is registered for
' a program where this course is part of it. If YES, make sure to:
' 1) change the Process field to YES
' 2) automaticaly enter AmauntDue, Course fee = 0
Dim db As DAO.Database
Dim PaidProgram As DAO.Recordset ' student has registered and paid for a full program
Dim CurrentReg As DAO.Recordset 'which program required the current course
Dim sqlText1 As String
Dim sqlText2 As String
sqlText1 = "SELECT ProgramId, CourseId FROM ProgramDetails WHERE CourseId =" & Me.CourseId
sqlText2 = "SELECT StudentId, ProgramId FROM Registrations WHERE NOT ProgramId Is Null AND StudentId =" & Me.StudentId
Set db = CurrentDb
Set PaidProgram = db.OpenRecordset(sqlText2)
Set CurrentReg = db.OpenRecordset(sqlText1)
' check if the course is present in "ProgramDetails" table, meaning it is a
' mandatory part of a program.
If CurrentReg.RecordCount > 0 Then
'the course is part of one or more programs
Debug.Print "this course is part of :" & CurrentReg.RecordCount
If PaidProgram.RecordCount > 0 Then
'and the student has registered for at least one program
'so check if the course is part of any of the registered programs
You've got a "do" loop with no way to exit (see the bolded PaidProgram.MoveNext line)
Do While Not PaidProgram.EOF
CurrentReg.FindFirst "ProgramId = " & PaidProgram!ProgramId
Debug.Print "current PrId: " & CurrentReg!ProgramId
Debug.Print "paid PrId: " & PaidProgram!ProgramId
If Not CurrentReg.NoMatch Then
MsgBox "You have already registered for the program requiring this course." & vblcrt & "No paiment is required at this time."
Me.TotalDue = 0
Me.CourseFee = 0
Me.Paid = 0
Me.Process = "Yes"