VBA to display records on a continuous form

[Mr Retirement]

Hello all!

I'm trying to display notes entered on a Subform within the body of an outlook e-mail through VBA. I'm able to get the first one displayed with the code below but not sure how to get all of them displayed as it is a continuous form and the number of entries could be different.

.HTMLBody = Me!FrmNotesSubform.Form.NoteTextbox.Value

Not sure how to accomplish this so any ideas would be helpful!!

Thanks,
Mr R

 

[Micron]

Your title suggests you're looking for a vba type answer so...
if the notes you want are contained in several records then you will have to loop through the form records and add each note to a string variable and perhaps add a line break after every note, then output the variable. If a record contains no note, then I guess you'll want to skip the break for that record.

Perhaps you have other options such as OutputTo or SendObject?

 

[Mr Retirement]

Hi Micron,

Is there any chance you could explain how looping to add each note to a string variable would work? Whether it is code or pointing me in the right direction

I think that methodology makes sense!

I appreciate the help!

Mr R

 

[Micron]

Maybe this will get you started. If not you can research how to loop through a DAO recordset. Cant' recall if I've ever done this with a form recordset clone but it should work. This is untested air code of course. Suggest you always step through code at least for the first time where it does ANY looping. It is not uncommon to find yourself stuck in a never ending loop the first time. I always save any design edits before running as well, in case I have to shut down via Task Manager.

If it is to be a function that returns the string to your sub...

Function BuildString() As String
Dim db As DAO.Database, rs As DAO.Recordset
Dim strText As String

On Error GoTo errHandler
Set db = CurrentDb
Set rs = Me.Recordset.Clone

If Not (rs.EOF And rs.BOF) Then
  rs.MoveFirst
  Do Until rs.EOF
    strText = strText & rs.Fields("nameOfYourNotesField") & vbCrLf
    rs.MoveNext
  Loop
End If

exitHere:
rs.Close
Set db = Nothing
Set rs = Nothing
BuildString = strText
Exit Function

errHandler:
Msgbox "Error " & err.Number & ": " & err.Description
BuildString = "ERROR" 'remove line if not wanted
Resume exitHere

End Function

EDIT - I didn't do any checking for a null in the notes records. No idea if that's a possibility or not.

 

[Mr Retirement]

Hi Micron -

Your response was extremely helpful in crafting the solution I came up with! I just wanted to share what I ultimately did for anyone else trying to something similar. There is probably an easier way (potentially exactly what Micron wrote above but I couldn't get through the code exactly as I'm still a novice).

I created a query that had all of the records from the subform I wanted to pull from for the body of my email. I wanted each record to be on its own line. So I looped through the query and if it matched the main forms ID then it would record it in the string. Eventually it would return something like this: "Record 1 <br> Record 2 <br> Record 3 <br>" and when I added that to the .htmlbody that worked perfectly.

    Set db = CurrentDb
    Set rs = db.OpenRecordset("QUERYNAME")
    Dim strEntryNotes As String
    
    Do Until rs.EOF
        
        If rs("FIELDNAMEWANTINGTOMATCH") <> Me.IDONMAINFORMTRYINGTOMATCH.Value Then GoTo Skip
        
        strEntryNotes = strEntryNotes & rs.Fields("INSERTFIELDNAME") & "<br>"

    Skip:
        
        rs.MoveNext
            
        Loop

So then I used "strEntryNotes" as the body of my html email code:

strBody = "Insert other body text" & strEntryNotes

.HTMLBody = strBody

Thanks again Micron!!

Mr R

 

[Micron]

Glad I could assist. FWIW, unnecessary GoTo's are considered poor form. You don't need that one if you reverse the logic (comparison). Also don't need Value as it's the default property of a control and recordset field.

Do Until rs.EOF
  If rs("FIELDNAMEWANTINGTOMATCH") = Me.IDONMAINFORMTRYINGTOMATCH Then
     strEntryNotes = strEntryNotes & rs.Fields("INSERTFIELDNAME") & "<br>"
  End If

  rs.MoveNext
            
Loop

 

[Mr Retirement]

Awesome! Thank you so much!!

Much appreciated!