Trouble with error

HaileyJ · September 6th, 2007, 07:59 AM

Hi, everyone.

I'm currently working on a task, and I'm not sure how to do it. The user gets an error message upon entering a student's attendance for a class. The error happens randomly, and after several attempts, it works. There is an error trapping in the save attendance status control that points to the â.updateâ code that triggers the error.

My job is to determine what part of the â.updateâ action fails. Do you have any ideas of how to approach this? Any help is appreciated.

Code:

Private Sub cmdSubmitAttend_Click()
'On Error GoTo eh
Dim lErrorTrap As Integer
    If Me.shpSaveChanges.Visible = False Or
 IsNull(Me.lstDayAttendRecord) Or Me.lstDayAttendRecord = 0 Then
        Exit Sub
    End If
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
        With rs
            .ActiveConnection = CurrentProject.Connection
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
lErrorTrap = 1
            .Open "Select * FROM tblAttendanceHistory WHERE
 AttendanceHistoryId = " & Me.lstDayAttendRecord
lErrorTrap = 2
                rs!AttendanceStatusID = Me.cboAttendanceStatusId
lErrorTrap = 3
                rs!MinutesAttended = Me.txtMinutesAttended
lErrorTrap = 4
                rs!AttendanceComments = Me.txtAttendanceComments
lErrorTrap = 5
                rs!ClassNumber = lClassNumber
lErrorTrap = 6
            .Update
lErrorTrap = 7
        End With
    RefreshDayAttendList
    ClearBlueChange
lErrorTrap = 8
    Me.cboAttendanceStatusId = 0
    Me.txtMinutesAttended = 0
lErrorTrap = 9
    Me.txtAttendPartName = ""
    Me.txtAttendanceComments = ""
lErrorTrap = 10
    Me.lstDayAttendRecord = 0
    Me.lstDayAttendRecord.SetFocus
lErrorTrap = 11
ex:
    Set rs = Nothing
    Exit Sub
eh:
    ehGeneralOpError Form.Name, "cmdSubmitAttend_Click", lErrorTrap
    GoTo ex
End Sub

Regards,

Hailey
www.msaccessrepair.com - Access Recovery

mmcdonal · September 7th, 2007, 07:04 AM

First of all, Access has a big problem with this kind of runtime string building:

.Open "Select * FROM tblAttendanceHistory WHERE AttendanceHistoryId = " & Me.lstDayAttendRecord

I would suggest this instead:

Dim sSQL As String
Dim i1stDay As Integer

i1stDay = Me.lstDayAttendRecord
sSQL = "Select * FROM tblAttendanceHistory WHERE AttendanceHistoryId = " & ilstDay
...
.Open sSQL

Also, I would do adOpenDynamic, but that is just my preference.

Also, all of your rs! statements might be better served if you took the values into variables first, and then updated the recordset with the variable values, and not directly from the form. This gives your operations a chance to fail before you open a recordset.

Did any of that help?

mmcdonal