I've been pulling my hair out for 2 weeks trying to overcome this issue. I am creating a calendar form in an access 2010 database. I have a set of text boxes that populate the date in the month depending on which month is displayed. That part works good. Underneath each text box, I have a list box to populate last name, first initial and text.

I am using the code from http://www.tek-tips.com/faqs.cfm?fid=838 to build the text boxes for the calendar. I am reusing the same code for the listboxes, except I changed curday to boxday and I am putting it into a different sub. I can get the correct information to display for 5 of the dates, which are the same type of text. I have 3 other dates which display a different text after the name and I can't get those to work. I know I am going about this the wrong way.

[FirstDate] is the current month and year in mmmm /yyyy format.

Here's the code that works:
Sub StillWorkingItOut()

' Dim variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim boxday As Variant
Dim i As Integer
Dim fEmpty As Boolean
Dim strRowSource As String

' Assign variables
Set db = CurrentDb
Set rs = db.OpenRecordset("qryAppointmentPatient")

' Date handler
boxday = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), 1) ' first day of month
boxday = DateAdd("d", 1 - Weekday(boxday), boxday) ' back to sunday
' need to loop thru 42 textboxes
For i = 0 To 41
Me("box" & i).ColumnCount = 5
Me("box" & i).ColumnWidths = ".6 in;.120 in;.195 in;.120 in; .120in"
fEmpty = (rs.BOF And rs.EOF)
If fEmpty Then
    strRowSource = Mid(Nz("", "1234567 "), 8)
Else
    strRowSource = "SELECT qryAppointmentPatient.Lname, qryAppointmentPatient.Fname, " & Chr(34) & "SV" & Chr(34) & " FROM qryAppointmentPatient WHERE (((qryAppointmentPatient.SVA)= #" & Format(boxday, "mm/dd/yyyy") & "#) OR ((qryAppointmentPatient.SVB)= #" & Format(boxday, "mm/dd/yyyy") & "#) OR ((qryAppointmentPatient.SVC)= #" & Format(boxday, "mm/dd/yyyy") & "#) OR ((qryAppointmentPatient.SVD)= #" & Format(boxday, "mm/dd/yyyy") & "#) OR ((qryAppointmentPatient.SVE)= #" & Format(boxday, "mm/dd/yyyy") & "#));"
End If
Me("box" & i).RowSource = strRowSource
Me("box" & i).Visible = False
If Month(boxday) = Month(Me!FirstDate) Then Me("box" & i).Visible = True
boxday = boxday + 1 ' nextday
Next i
End Sub
I figured once I could get one of the next dates to populate along with the SVA, SVB, SVC, SVD, and SVE dates, I could add the remaining date fields I need. I know my ColumnCount is wrong, but that doesn't seem to affect how it works. What I am trying to add is another SELECT statement. I've tried using UNION, but then nothing displays. I've learned that UNION statements have to have the same amount of fields so I tried it this way, but nothing displays in the calendar:
' adding this to above code (no need to repeat it all)
Dim strRcrt As String
strRcrt = " UNION SELECT qryAppointmentPatient.Lname, qryAppointmentPatient.Fname, " & Chr(34) & "R" & Chr(34) & " FROM qryAppointmentPatient WHERE (((qryAppointmentPatient.Recert)= #" & Format(boxday, "mm/dd/yyyy") & "#))"
strRowSource = "SELECT qryAppointmentPatient.Lname, qryAppointmentPatient.Fname, " & Chr(34) & "SV" & Chr(34) & " FROM qryAppointmentPatient WHERE (((qryAppointmentPatient.SVA)= #" & Format(boxday, "mm/dd/yyyy") & "#))" & strRcrt
I am guessing that my UNION statement can't have this type of WHERE clause in it. If I remove the WHERE clause, then all the records return with the name and R for every date.

Let me know if you need any further info to help me out. Thanks!