Can ctlRef.ItemData be used conditionally?

[SammyB]

Part of the database I am putting together requires that I keep attendance
for a class. I found an example for what I am trying to do on another Access
help site located here:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54924.
Basically, a listbox is created of all the potential attendees of a class. I
can select multiple students from the list and then choose a class ID. Once I
have the students and class ID selected, I click a button and individual
records are created in an attendance table with student IDs and the class ID.

While the example does an excellent job of keeping attendance for who
attended a particular class, I am equally interested in who did NOT attend
the class. What I would like my code to do is create an attendance record for
everyone in the list box (not just the ones selected) and use the
ctlRef.ItemData conditionally to add an ‘X’ to the attendance field of all
records that attended the class and leave a Ҡin the field for those who did
not attend. Is this possible?

Thank you in advance to anyone who can help me.

Below is the code I have used so far (take note of the commented lines):

Private Sub CREATE_ATTENDANCE_Click()
On Error GoTo Err_cmdCreate_Click

Me.TEXT_NOTICE = CreateAttendanceRecords(Me.STUDENT_LIST)
Me.STUDENT_LIST.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!qry_YR1_Attendance
Set rst = qd.OpenRecordset

For Each i In ctlRef.Recordset 'IS THIS LINE CORRECT?
rst.AddNew
rst!STUDENT_ID = ctlRef.ItemData(i)
rst!CLASS_ID = Me.CLASS

' BELOW IS THE CONDITIONAL PSEUDO CODE OF THE LOGIC I WOULD LIKE
If ctlRef.ItemsSelected = True Then
rst!Attendance = "X"
Else
rst!Attendance = ""
End If
rst.Update
Next i

' COMMENTED LINES BELOW ARE OLD CODE THAT WORKED FOR ADDING SELECTED ITEMS
ONLY
' For Each i In ctlRef.ItemsSelected
' rst.AddNew
' rst!STUDENT_ID = ctlRef.ItemData(i)
' rst!CLASS_ID = Me.CLASS
' rst!Attendance = "X"
' 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

 

[Klatuu]

There may be a more efficient method than what you are doing.
I would assume for the specific class, I assume you have a table that shows
which students are registered in the course. If so, then you should be able
to use an Append query to Add records to the attendance table for all
regsistered students for the specific sesssion.
Having done that, you can then use the ItemsSelected collection of the list
box to build a WHERE IN(......) clause for an update query to update the
attended field for the newly added records. In fact, it would actually be
possible to do both in one query if you are fairly up on SQL.

 

[SammyB]

Dave, thank you for your suggestion. I think I understand what you are saying
but I'm still a bit unsure how to get there.

First the append query needs to run.

INSERT INTO tbl_Attendance ( STUDENT_ID, CLASS_ID )
SELECT tbl_Student.STUDENT_ID, [Forms]![frm_Attendance_Entry]![CLASS_ID]
FROM tbl_Student
WHERE (((tbl_Student.YEAR_ID)="YEAR1"));

This query will append the tbl_Attendance table with all the eligible
students for the class.


Next an update query has to run in the tbl_Attendance in order to find the
STUDENT_ID's and CLASS_ID's that were created in the append query and update
the [ATTENDANCE] field with an "X". This is where I get confused with syntax.
How do I incorporate the selected STUDENT_ID's from the listbox into the
query?

I appreciate any help you can give me.

 

[Klatuu]

Here is a function I use just for that purpose:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function