Solved Filling form fields from recordset (1 Viewer)

[ClaraBarton]

Is there a way I can combine these 2 ?

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

 

[MajP]

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

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.

See FrmDemo.

 

[ClaraBarton]

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

 

[ClaraBarton]

Ok, went back and redid the code. It all works beautifully now. Many thanks for your help.