VBA to display records on a continuous form

Mr Retirement

New Member
Joined
Nov 12, 2016
Messages
46
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.

VBA Code:
.HTMLBody = Me!FrmNotesSubform.Form.NoteTextbox.Value

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

Thanks,
Mr R
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 12, 2016
Messages
46
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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...

VBA Code:
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

New Member
Joined
Nov 12, 2016
Messages
46

ADVERTISEMENT

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.

VBA Code:
    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:

VBA Code:
strBody = "Insert other body text" & strEntryNotes

.HTMLBody = strBody

Thanks again Micron!!

Mr R
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Do Until rs.EOF
  If rs("FIELDNAMEWANTINGTOMATCH") = Me.IDONMAINFORMTRYINGTOMATCH Then
     strEntryNotes = strEntryNotes & rs.Fields("INSERTFIELDNAME") & "<br>"
  End If

  rs.MoveNext
            
Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,126,940
Messages
5,621,732
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top