Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    I have a database that I use for the Sunday School at our church. I am trying to set up a way to track attendance and found on another database a system that is just what I want. Basically, all I need to know is who was here on a specific day.

    This other database has a form, with a text box for the date and a list box on it that shows all the students. Using Control or Shift, you can select whatever students are there that day, then click the "Create Attendance Records" button and it will save the records to tblAttendance. It worked good in the other database, so I copied everything over to my database and changed the names of the tables and queries to match my existing database.

    I've doublechecked to make sure that I have everything spelled correctly but everytime I select some names and click the Create button, I get the following error "3164 - Field cannot be updated". Unfortunately, it doesn't tell me what field. There is also a text box on the form, that when you click the Create button, it tells you "Records Created". When I click the Create button without selecting any names, then it seems to work, but of course, no records are actually created.

    Here is the code that is attached to the Click of the Create Attendance Records button. Any ideas on what I am missing? Thanks.

    Note, there was also code to resize a subform that was on the original form, since I didn't need that, I commented it out, it didn't work when it was active either, but I included it below, just in case that is what is causing the problem.

    Option Compare Database

    Private Sub cmdCreate_Click()
    On Error GoTo Err_cmdCreate_Click

    Me.txtNotice = CreateAttendanceRecords(Me.lstStudents)
    Me.lstStudents.Requery

    Exit_cmdCreate_Click:
    Exit Sub

    Err_cmdCreate_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_cmdCreate_Click
    End Sub


    Public Function CreateAttendanceRecords(ctlRef As ListBox) As String
    On Error GoTo Err_CreateAttendanceRecords_Click

    Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef

    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qryStudents4Attendance
    Set rst = qd.OpenRecordset

    For Each i In ctlRef.ItemsSelected
    rst.AddNew
    rst![Student ID] = ctlRef.ItemData(i)
    rst!AttendanceDate = Me.txtToday
    rst.Update
    Next i
    Set rst = Nothing
    Set qd = Nothing
    CreateAttendanceRecords = "Records Created"

    Exit_CreateAttendanceRecords_Click:
    Exit Function

    Err_CreateAttendanceRecords_Click:
    Select Case Err.Number
    Case 3022 'ignore duplicate keys
    Resume Next
    Case Else
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_CreateAttendanceRecords_Click
    End Select

    End Function



    Private Sub Form_Resize()

    End Sub
    'Const LeftRightPadding = (0.125 + 0.25) * 1440
    'Const TopBottomPadding = (0.25 + 0.375) * 1440

    'Me.Students.Height = Me.InsideHeight - TopBottomPadding
    'Me.lstStudents.Width = Me.InsideWidth - LeftRightPadding
    'End Sub

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The issue probably relates to qryStudents4Attendance. If you open that query directly does it let you add records?

    You could try just opening the recordset where you add the attendance records against the Attendance table.

    Code:
    Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Dim SQL as string
     
     Set dbs = CurrentDb
     SQL= "Select tblAttendance.* from tblAttendance"
     Set rst = dbs.OpenRecordset(SQL, dbOpenDynaset)
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts
    No, it will not let me add any records.

    I used your code and it works! Thanks John, I really appreciate it.

Posting Permissions

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