ACC2000: Year Calendar Report

[accessGod]

Error argument not optional occurs at Call getCalendarData

How to make your code work? Function getCalendarData(employeeID As Long) As Boolean
Dim rs As DAO.Recordset
Dim strDate As String
Dim strCode As String
Dim strSQL As String
Dim i As Integer
'// setup sql to retrieve one employee's attendance
strSQL = "SELECT * From t_employeeAttendance WHERE employeeID = " & employeeID
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Set colCalendarDates = New Collection
With rs
If (Not .BOF) Or (Not .EOF) Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
For i = 1 To .RecordCount
strDate = .Fields("attendanceDate")
strCode = .Fields("statusCode")
colCalendarDates.Add strCode, strDate
.MoveNext
Next i
End If
.Close
End With
'// return date collection
Set rs = Nothing
End Function

 

[RuralGuy]

I would start by removing the lines in RED and adding the lines in GREEN. I assume that colCalendarDates is a public variable defined outside of this function. You invoke this function like:
If getCalendarData(Me.employeeID) Then
...and it should fill out the colCalendarDates variable. BTW, it is much easier to read code posts if you use the code tags. (the # button)

Function getCalendarData(employeeID As Long) As Boolean
   Dim rs As DAO.Recordset
   Dim strDate As String
   Dim strCode As String
   Dim strSQL As String
   Dim i As Integer
   '// setup sql to retrieve one employee's attendance
   strSQL = "SELECT * From t_employeeAttendance WHERE employeeID = " & employeeID
   Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
   Set colCalendarDates = New Collection
   With rs
[COLOR="Red"]      If (Not .BOF) Or (Not .EOF) Then
         .MoveLast
         .MoveFirst
      End If
[/COLOR]      If .RecordCount > 0 Then
[COLOR="DarkGreen"]         .MoveLast
         .MoveFirst
[/COLOR]         For i = 1 To .RecordCount
            strDate = .Fields("attendanceDate")
            strCode = .Fields("statusCode")
            colCalendarDates.Add strCode, strDate
            .MoveNext
         Next i
      End If
      .Close
   End With
   '// return date collection
   Set rs = Nothing
End Function

 

[boblarson]

Allan -

I think that they will need the .MoveLast/.MoveFirst BEFORE they check the recordcount as it will not show up until they do.

 

[RuralGuy]

Hi Bob,
I know it is not necessarily accurate but I thought it is guaranteed to be NonZero if there are any records. Actually I guess that means the test shoud be:
If .RecordCount <> 0 Then
...rather than
If .RecordCount > 0 Then

 

[boblarson]

I guess you're right that DAO recordsets appear to return at least a 1 if there is something there. I must have been thinking ADO recordset.

 

[RuralGuy]

Hmmm...ADO huh. I've not played with that yet but I'll have to keep that in mind when I do.