Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    65

    Angry 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.

    ---------CODE BEGINS---------------------------------------------------------
    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

    Debug.Print "paid programs: " & PaidProgram.RecordCount

    PaidProgram.MoveFirst

    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
    CurrentReg.MoveNext
    Else:

    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"

    End If
    Loop
    End If
    End If

    End Sub
    ---------------------END OF CODE----------------------------------------------------

    If I run the two queries based on sqlText1 and sqlText2 statements they give me the following results:

    sqlText1
    ==========
    CourseId ProgramId
    Anatomy 1
    Anatomy 2
    Anatomy 3
    Anatomy 6

    sqlText2
    =========
    StudentId ProgramId
    8 3


    So my goal is to see if ProgramId =3 from the second query has a match within the first query.

    Thanks again,
    Jazz

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    Re: This sub crashes Access! What is wrong?

    You've got a "do" loop with no way to exit (see the bolded PaidProgram.MoveNext line)
    Code:
    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
          CurrentReg.MoveNext
       Else:
    
          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"
    
       End If
       PaidProgram.MoveNext
    Loop

  3. #3
    Join Date
    Feb 2003
    Posts
    65
    Thanks JTRockville.

    You are absolutely right. At least now Access does not crush.

    Thanks again,
    Jazz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •