Multiple lines of input in calender text box

[jambet]

Hello, I have a calender composed of 37 text boxes (one for each day of a month) which when run, uses the following code to extract job#s from a table for the appropriate date.

    Set f = Forms!frmCalender
    For i = 1 To 37
        f("text" & i) = Null
    Next i
    sql = "SELECT [Job#],[Date Scheduled] FROM [Job Information] WHERE ((MONTH([Date Scheduled]) = " & f!month & " AND YEAR([Date Scheduled]) = " & f!year & ")) ORDER BY [Date Scheduled];"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
    If rs.RecordCount > 0 Then
        For i = 1 To 37
            If IsDate(f("date" & i)) Then
                rs.FindNext "[Date Scheduled]=#" & f("date" & i) & "#"
                If Not rs.NoMatch Then
                    f("text" & i) = rs![job#]
                End If
            End If
        Next i
    End If

My problem is that frequently there are multiple jobs scheduled for a particular date and I am not able to display more than one job for the date. I need to be able to display multiple job#s on separate lines within a single text box.
thanks for any help.

 

[PaulF]

have you tried changing

If Not rs.NoMatch Then
f("text" & i) = rs![job#]
End If

to
If Not rs.NoMatch Then
if len(trim(nz(f("text" & i),"")))>0 then
f("text" & i) = f("text" & i) & vbcrlf & _
f("text" & i) = rs![job#]
else
f("text" & i) = rs![job#]
end if
End If



PaulF

 

[jambet]

Thanks, Paul
When I inserted your code directly, still only received one job# for each date. Tried inserting a 'do..loop until rs.nomatch' before and after the "if not rs.nomatch" segment and received a reply of "False" for those dates that had multiple jobs scheduled. Any more ideas?
Regards, Jim

 

[PaulF]

try changing to

If rs.RecordCount > 0 Then
For i = 1 To 37

If IsDate(f("date" & i)) Then
With rs
.movefirst
Do While Not .EOF
If rs![Date Scheduled]= f("date" & i)
If len(trim(nz(f("text" & i),"")))>0 then
f("text" & i) = f("text" & i) & vbcrlf & _
f("text" & i) = rs![job#]
Else
f("text" & i) = rs![job#]
End if
End If
.MoveNext
Loop
End If
Next i
End If


PaulF