Retrieve Data from a Subform VBA (1 Viewer)

andy1968 · Jul 12, 2019

I have a main for and a subform.

Both forms have the e-mail addresses of people on them.

On the main form, I have a procedure to e-mail a report to the person.

I would like to add a procedure to also e-mail the people on the subform, if there are any.

I think this would involve using recordset of the subform, but I am not experience in using this.

theDBguy · Jul 12, 2019

Hi. Sounds about right. Maybe along these lines?

Code:

Dim rs As Object
Set rs=Me.SubformControlName.Form.RecordsetClone
With rs
    'do the loop here

End With
Set rs = Nothing

andy1968 · Jul 12, 2019

I get a compile error:

Method or data member not found.

It is highlighting "Form"

Code:

Dim rs As Object
Set rs = Me.Reviewer1.Form.RecordsetClone
With rs
    mailto = mailto & ";" & rs
End With

theDBguy · Jul 12, 2019

andy1968 said:
I get a compile error:

Method or data member not found.

It is highlighting "Form"

Code:

Dim rs As Object Set rs = Me.Reviewer1.Form.RecordsetClone With rs mailto = mailto & ";" & rs End With

It has to be a loop... Maybe something like:

Code:

Do While Not .EOF
    strMailto = strMailto & !EmailAddressField & ";"

Loop

andy1968 · Jul 12, 2019

Still doing something wrong.

Code:

Dim rs As Object
Set rs = Me.[SupplementalReviewer].Form.RecordsetClone
With rs
    Do While Not .EOF
    mailto = mailto & ![SupplementalReviewer] & ";"
    Loop
End With
Set rs = Nothing
Debug.Print mailto

theDBguy · Jul 12, 2019

andy1968 said:

Still doing something wrong.

Code:

Dim rs As Object
Set rs = Me.[SupplementalReviewer].Form.RecordsetClone
With rs
    Do While Not .EOF
    mailto = mailto & ![SupplementalReviewer] & ";"
    Loop
End With
Set rs = Nothing
Debug.Print mailto

Hi. "something wrong" is a little vague. What happened? Did you get an error? If so, what was the error message.

andy1968 · Jul 12, 2019

Same error message. At the same location.

andy1968 · Jul 12, 2019

Actually, it doesn't add any of the e-mail addresses from the subform, and does not create the e-mail.

There was not "error" generated.

isladogs · Jul 12, 2019

Change the first line to Dim rs as DAO.Recordset

Is SupplementalReviewer the name of the subform control on the main form?
This seems unlikely as you get an error on that line
Or is it an email address?

Whichever it is, the same item can't be used in both lines of code you have now

andy1968 · Jul 12, 2019

OK. I modified the code.

Code:

mailto = Me.Reviewer.Column(1) & ";"
Debug.Print mailto
Dim rs As DAO.Recordset
Set rs = Me.frmSubmittalCriteriaNew.Form.RecordsetClone
With rs
    Do While Not .EOF
    mailto = mailto & !email & ";"
    Loop
End With
Set rs = Nothing
Debug.Print mailto

The immediate window gives me the e-mail address from the mainform only. The procedure does not continue.

isladogs · Jul 12, 2019

That's the third set of names you've used in this thread and I'm a bit lost.
Have you stepped though the code to see if its actually looping?
If not try adding the line .MoveFirst after the line With rs
Also add Debug.Print !email before the mailto line

andy1968 · Jul 15, 2019

Yeah, sorry for the confusion. Was trying to do too much at once.

frmSubmittalCriteriaNew is the subform

"Reviewer" is the control on the main from that has an e-mail address in one of the columns.

email is a control on the subform with email addresses as well. It is also the name of the field in the underlying table.

I added the .MoveFirst, and the Debug.Pring !email

Nothing in the immediate window, so I guess it is not looping.

isladogs · Jul 15, 2019

That suggests your recordset is incorrect or empty.
What is the name of the subform container control on the main form?
It may not be the same as the subform name but that is what you need to use.

In the VBE, select step through. This will allow you to follow the code line by line and confirm whether it is looping

andy1968 · Jul 15, 2019

Thanks!

Well, looks like I had the wrong name for the subform. Fixed that.

Did the step through next.

It seems like the code keeps looping through the loop. It adds the e-mail address from the subform over and over again.

So I added the missing .MoveNext.

Thanks Colin!

Solved my issue, and even better learned a lot of new tricks.

Now I will come up with even bigger issues.

theDBguy · Jul 15, 2019

andy1968 said:
Thanks!

Well, looks like I had the wrong name for the subform. Fixed that.

Did the step through next.

It seems like the code keeps looping through the loop. It adds the e-mail address from the subform over and over again.

So I added the missing .MoveNext.

Thanks Colin!

Solved my issue, and even better learned a lot of new tricks.

Now I will come up with even bigger issues.

Hi. Sorry, I guess that was my fault because I forgot to include it earlier. Glad to hear you got it sorted out. Cheers!

isladogs · Jul 15, 2019

Oops. I really should have noticed that MoveNext was missing.

Anyway, congratulations on fixing your problem

Retrieve Data from a Subform VBA (1 Viewer)

andy1968

Registered User.

theDBguy

I’m here to help

andy1968

Registered User.

theDBguy

I’m here to help

andy1968

Registered User.

theDBguy

I’m here to help

andy1968

Registered User.

andy1968

Registered User.

isladogs

MVP / VIP

andy1968

Registered User.

isladogs

MVP / VIP

andy1968

Registered User.

isladogs

MVP / VIP

andy1968

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread