Displaying information in a text box

[SarahBash]

Hi,

I am hoping that someone can offer some words of wisdom. I have designed and coded a calendar for my database. Now I am needing to display information from a query in the calendar text boxes (ie, I would like the calendar to display which employee is off on annual leave when). I have the following code (which I believe has no bugs as there are no errors being displayed) however it is not displaying any information. Does anyone have any ideas as to what I have missed to make it display the information????

Private Sub Form_Current()
    Dim strQuery1 As String
    Dim strQuery2 As String
    Dim I As Long
    Dim tmpText As String
    Dim tmpText2 As String
    Dim eventID(10000) As String
    Dim eventDate(10000) As Date
    Dim eventTitle(10000) As String
    Dim events As Long
    Dim db As DAO.Database
    Dim myset As DAO.Recordset
    Dim Criteria As String
    Dim EmpID As Integer
    Dim intDays(42) As Integer
    Dim intTemp As Integer
    If Nz([cboEmployee], "") = "" Then
        strQuery1 = "qryCalendar1"
        strQuery2 = "qryCalendar2"
    Else
        EmpID = Me.cboEmployee
        strQuery1 = "SELECT * FROM qryCalendar1Filter WHERE EmployeeID=" & EmpID & ";"
        strQuery2 = "SELECT * FROM qryCalendar2Filter WHERE EmployeeID=" & EmpID & ";"
    End If
    events = 0
 
    Set db = CurrentDb()
    Set myset = db.OpenRecordset(strQuery1)
    Criteria = "[StartDate] <> Null"
    myset.FindFirst Criteria
    Do Until myset.NoMatch
        events = events + 1
        If IsNull(myset![EndDate]) Then
            eventID(events) = myset![ID]
            eventDate(events) = myset![StartDate]
            eventTitle(events) = myset![EmployeeType]
        Else
            For I = 1 To Int(DateDiff("d", myset![StartDate], myset![EndDate])) + 1
                eventID(events + I - 1) = myset![ID]
                eventDate(events + I - 1) = DateAdd("d", I - 1, myset![StartDate])
                eventTitle(events + I - 1) = myset![EmployeeType]
            Next I
            events = events + I - 1
        End If
        myset.FindNext Criteria
    Loop
 
    Set myset = CurrentDb.OpenRecordset(strQuery2)
    Criteria = "IsDate([EndDate])=True"
    myset.FindFirst Criteria
    Do Until myset.NoMatch
        events = events + 1
        eventID(events) = myset![ID]
        eventDate(events) = myset![EndDate]
        eventTitle(events) = myset![EmployeeType]
        events = events + I - 1
        myset.FindNext Criteria
    Loop
 
    For I = 1 To 42
        Me("Day" & Trim$(I)) = ""
    Next I
 
    If myset.RecordCount > 0 Then
        For I = 1 To 42
            If Me("Box" & Trim(I)) = "" Then
                intDays(I) = 0
            Else
                intDays(I) = Me("Box" & Trim(I))
            End If
            Me("Day" & Trim(I)) = ""
        Next I
 
        myset.MoveFirst
 
        tmpText = ""
        tmpText2 = ""
 
        For I = 1 To events
            If intPubMonth = Format(eventDate(I), "mmmm") _
            And intPubMyYear = Int(Format(eventDate(I), "yyyy")) _
            Then
                tmpText = tmpText + Chr(13) + Chr(10) + eventTitle(events)
                tmpText2 = eventID(events) + " or [ID] = " + tmpText2
                Me("Day" & Trim(I)).BackColor = 16777215
            End If
        Next I
        If Len(tmpText2) > 0 Then tmpText2 = Left(tmpText2, Len(tmpText2) - 11)
        Me("Day" & Trim(I)).Caption = tmpText
        Me("id" & Trim(I)).Caption = tmpText2
        End If
 
  myset.Close
End Sub

 

[SarahBash]

Oh boy, I'm in trouble if no one has any ideas on how to solve this one!!!!

 

[vbaInet]

I don't see how feasible it would be for staff names to show on your calendar text boxes? What if they were 200 names?