Solved Filling form fields from recordset (1 Viewer)

Is there a way I can combine these 2 ?
Code:
Public Sub UpdatePrepNote()
Dim frm As Form
Dim sql As String
Dim cPrepNote As String
Dim rs As DAO.Recordset
Set frm = Me!frmCalendarWeek.Form

sql = "SELECT MenuDate, PrepNote, Weekday " & _
        "FROM qryAppointments where " & _
        "MenuDate Between (#" & Me.txtDate & "#) AND (#" & (Me.txtDate + 6) & "#)" & _
        "ORDER BY Weekday"
 Set rs = CurrentDb.OpenRecordset(sql)
    With rs
    Do While Not .EOF
        cPrepNote = cPrepNote & vbCrLf & .Fields(1)
        .MoveNext
    Loop
        .Close
    End With
    
 Do While Not rs.EOF
   Select Case Weekday(rs!menudate)
    Case vbSunday
      frm.NoteSun = rs!PrepNote
    Case vbMonday
      frm.NoteMon = rs!PrepNote
    Case vbTuesday
      frm.NoteTues = rs!PrepNote
    Case vbWednesday
      frm.NoteWed = rs!PrepNote
    Case vbThursday
      frm.NoteThurs = rs!PrepNote
    Case vbFriday
      frm.NoteFri = rs!PrepNote
    Case vbSaturday
      frm.NoteSat = rs!PrepNote & vbCrLf & rs!PrepNote
   End Select
   rs.MoveNext
  Loop
End Sub
 
Not sure what that is supposed to do and why that is needed. It would give you a single note for the whole week.
If I do the following
Code:
Private Sub Form_Load()
  Me.txtDate = #11/5/2023#
End Sub
Private Sub ClearNotes()
  Dim ctl As Access.Control
  For Each ctl In Me.Controls
    If ctl.Tag = "NoteControl" Then ctl.Value = Null
  Next ctl
End Sub
Public Sub UpdatePrepNote(frm As Access.Form)

Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT MenuDate, PrepNote " & _
        "FROM qryAppointments where " & _
        "MenuDate >= #" & Me.txtDate & "# AND Menudate <= #" & (Me.txtDate + 6) & "# " & _
        "ORDER BY MenuDate, apptstart"
    Debug.Print sql
Set rs = CurrentDb.OpenRecordset(sql)
Do While Not rs.EOF
   Select Case Weekday(rs!menudate)
    Case vbSunday
      frm.NoteSun = frm.NoteSun & rs!prepnote & vbCrLf
     Case vbMonday
      frm.notemon = frm.notemon & rs!prepnote & vbCrLf
    Case vbTuesday
      frm.notetues = frm.notetues & rs!prepnote & vbCrLf
    Case vbWednesday
      frm.noteWed = frm.noteWed & rs!prepnote & vbCrLf
    Case vbThursday
      frm.noteThur = frm.noteThur & rs!prepnote & vbCrLf
    Case vbFriday
      frm.NoteFri = frm.NoteFri & rs!prepnote & vbCrLf
    Case vbSaturday
      frm.notesat = frm.notesat & rs!prepnote & vbCrLf
   End Select
   rs.MoveNext
  Loop
End Sub

Then in my demo you can see I had two appts for Sun and 3 for Mon.
concat.png


See FrmDemo.
 

Attachments

Last edited:
yes, I did that but when I had more than two, it overwrote the second note. I added a weekday column that I though would help. I thought by combining the two I could go thru each day and combine them and put them in the field all at once but I couldn't figure how to loop to the next day. It always makes sense in my head. Not so much in action. I'll go back and work with yours. Thanks
 
Ok, went back and redid the code. It all works beautifully now. Many thanks for your help.
 

Users who are viewing this thread

Top Bottom