Solved Filling form fields from recordset (1 Viewer)

[ClaraBarton]

I'm completely at a loss here... I want a different note for each date of the week and this is where I am so far:

Public Sub UpdatePrepNote()
Dim sql As String
Dim vDate(6) As String
Dim vPrepNote(6) As Field
Dim rst As DAO.Recordset
Dim frm As Form

Set frm = Me!frmCalendarWeek.Form
    vPrepNote(0) = frm!NoteSun
    vPrepNote(1) = frm!NoteMon
    vPrepNote(2) = frm!NoteTues
    vPrepNote(3) = frm!NoteWed
    vPrepNote(4) = frm!NoteThurs
    vPrepNote(5) = frm!NoteFri
    vPrepNote(6) = frm!NoteSat
    vDate(0) = Me.txtDate
    vDate(1) = Me.txtDate + 1
    vDate(2) = Me.txtDate + 2
    vDate(3) = Me.txtDate + 3
    vDate(4) = Me.txtDate + 4
    vDate(5) = Me.txtDate + 5
    vDate(6) = Me.txtDate + 6
    
sql = "SELECT MenuDate, PrepNote " & _
        "FROM qryAppointments " & _
        "MenuDate Between (#" & Me.txtDate & "#) AND (#" & (Me.txtDate + 6) & "#)" & _
        "ORDER BY MenuDate"
        
Set rst = CurrentDb.OpenRecordset(sql)
    For Each Field In vPrepNote
        rst!

How do I say put the PrepNote in the matching field by date?

 

[tvanstiphout]

Do you have a table with 7 fields for the 7 notes for a week?
We frown upon that, because it is a "repeating group" and thus violates proper database design principles ("the rules of Normalization"). It would be MUCH better to spin off these fields in a Notes table with AppointmentID long int required FK, NoteDate datetime required, NoteText long text required.

 

[MajP]

I cannot make heads or tails of what you are trying. That looks super convoluted. Rude Golberg design. What is the deal with the Arrays?

I would think
1. You open the form and select a date
2. This returns a query with records in that date range.
3. Loop the records and put the note in the correct date control.
I assume you are normalized so there is a single field with a date and a note field, but the display is denormalized.
4. When the form closes do an upsert query.
If the value exists for that date then update it
If the value does not exist do an insert query
4. Run a delete query where notes is null because you cleared it.

 

[ClaraBarton]

You may be right. I may be nuts but here's the deal: I'm creating a grocery list from the menu calendar and some menu items require prep notes. It's not the actual recipe... just things to do ahead of time. I had it working with a convulated sql and I'm just trying to refine it. Here's where I am so far and I want the individual note right beside the date with the recipe. A better way?

 

[MajP]

Can you provide either the DB or really just the appointment table with the notes. I think I can demo with just a blank form and 7 textboxes for the notes. The reason to do this makes sense, it was the code with the arrays that seemed overly complicated.

 

[ClaraBarton]

 

[ClaraBarton]

The recordsource for the calendar is only the recipes from tblWeekData. This is Peter Hibbs and your calendar.

 

[MajP]

That is good. You are using a normalized appointment table. And I assume you only use the notes field to display not adding and edit. You edit using the form in thread #6. That makes it easy if you just need to load the notes fields.

 

[ClaraBarton]

It is not editable except by the form appointments or back to the recipe

 

[MajP]

Public Sub UpdatePrepNote(frm As Access.Form)

Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT MenuDate, PrepNote " & _
        "FROM qryAppointments where " & _
        "MenuDate Between (#" & Me.txtDate & "#) AND (#" & (Me.txtDate + 6) & "#)" & _
        "ORDER BY MenuDate"
    Debug.Print sql
 Set rs = CurrentDb.OpenRecordset(sql)
 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.noteThur = rs!prepnote
    Case vbFriday
      frm.NoteFri = rs!prepnote
    Case vbSaturday
      frm.notesat = rs!prepnote
   End Select
   rs.MoveNext
  Loop
End Sub

works for me.

 

[ClaraBarton]

WHOA! Works for me too! Thank you so much!

 

[MajP]

one thing to be careful about is appointments have a time component. If menudate has a time component from the appointment then it could fall outside the between.

If menu date is 11/11/2023 2:34 PM
this is actually less than
txtDate+6 which is 11/11/2023
need to turn menuday into just the dates component if you have not done that already

 

[MajP]

Further I assume you have modified this somehow that you only have a maximum single appointment for each day of the week.

 

[ClaraBarton]

No... I have the appointments (recipes) indexed 1 - 6 so I have room for 6 appointments each day. I cut out the day calendar and the year calendar. Why would you think there's only one? I think I have the dates are working properly.

 

[ClaraBarton]

Good advice... removing all time components. Don't use them.

 

[MajP]

No... I have the appointments (recipes) indexed 1 - 6 so I have room for 6 appointments each day. I cut out the day calendar and the year calendar. Why would you think there's only one? I think I have the dates are working properly

If the query returns more than 1 record per day then the second record will overwrite the first record. Maybe you concatenated ahead of time.
If not then I would think you would need to concatenate when loading the textboxes.
frm.NoteSun = rs!prepnote
to
frm.NoteSun = frm.notesun & vbcrlf & rs!prepnote

 

[ClaraBarton]

Oh Rats! I'll have to rethink this... easy fix... Thank you

 

[ClaraBarton]

My skills are one tiny detail at a time. One project is turning into a career!

 

[ClaraBarton]

Turns out I don't know how to do this. How do I concatenate all notes by date? Do I need to do each one 6 times? Because in testing, if I have 3 notes, the successive ones overwrite the previous note. Is there a simpler way to do this?

 

[MajP]

frm.NoteSun = frm.notesun & vbcrlf & rs!prepnote

I would think if you clear all the textbox before loading them, then use the above code it should work. You basically loop all the returned records and simply add to the note already in the textbox.

If not you could make a query that concatenates all notes, but that query is trickier than most because you have multiple date time blocks. Normally we concatenate records by a foreign key. See code to concatenate child records.

Code Snippets