Code to update student attendance record

[rossco]

Hi. I am after some help with this code please. I've tried to setup the following code to track student attendance when they enter a class. The background is a student will buy for example 10 classes and when they enter the room, they scan their ID tag and the database needs to track the number of times they've attended and how many classes they have left. I think the code should:

1. capture the ID
2. match the ID of the current student to the orders placed by that student and update the relevant numbers accordingly.

The code crashes on the line 'db.execute sSQL' with error 91 "Object variable or With block variable not set"

What am I doing wrong? I am not sure whether the entire procedure is clumsy or not but any help would be appreciated please.

Code: Select all

Private Sub Barcode_AfterUpdate()
    Dim db As DAO.Database
    Dim extID As String
    Dim sSQL As String
    
    '   capture scanned code into Visits table
    If Me.NewRecord Then
        Me.Visit = Now
        Me.ExtractedID = CLng(Right(Me.Barcode, 3))
        extID = CLng(Right(Me.Barcode, 3))
        MsgBox "Visit Registered", vbInformation
        RunCommand acCmdRecordsGoToNew
        Me.ExtractedID = ""
        DoCmd.GoToControl "Barcode"
    End If

    '   match student ID with current student order and update number of classes used and attended
    sSQL = "UPDATE qryOrders_and_Details, qryExtractCurrentVisitor_Stg1 " _
            & " SET qryOrders_and_Details.ClassesUsed = +1, " _
            & " qryOrders_and_Details.ClassesRemaining = [ClassesBought]-1" _
            & " WHERE (((qryOrders_and_Details.StudentID)= " & extID & "));"

    db.Execute sSQL
    
End Sub

[HansV]

You need to insert a line

Code: Select all

    Set db = CurrentDb

above the line with db.Execute. By the way,

qryOrders_and_Details.ClassesUsed = +1

will set the value of ClassesUsed to 1. Is that what you intended?

[rossco]

Thanks Hans. I also changed "= +1" to "= ClassesUsed +1". Thanks for that too.