Need your help with VBA

Roman F
Roman F used Ask the Experts™
on
GM, gurus!

need your help with the code. The code below is working fine. It prints every control on email. No problem. I have let say 10 controls, and all of them will be printed
I do not want everything, want only some, let's say only three of them. How to exclude those that I do not need. May be by name, let's say I do not want REcID and Category,
or I like to have only with the index 1,4,7,9.



Dim ctl As Control
Dim strBody As String
           For Each ctl In Me.Controls
                          strBody = strBody & ctl.Name & " : " & ctl.Value & "<BR>"
          Next
SendHTMLEmail Me.SEmail, "Follow-Up Item", strBody, False
Me.SendMark.Value = "Yes"
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Personally when i have such cases i Tag the controls..
I use the Tag property ...and i place some text for marking...in your case "Email" should be a good Tag
Then on the Iteration
Dim ctl As Control
Dim strBody As String
           For Each ctl In Me.Controls
                       If ctl.Tag = "Email" then
                          strBody = strBody & ctl.Name & " : " & ctl.Value & "<BR>"
                      End If
          Next
SendHTMLEmail Me.SEmail, "Follow-Up Item", strBody, False
Me.SendMark.Value = "Yes"
End Sub

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
you can either explicitly set those controls you want or use an IF statement to exclude them

Option 1
    Dim strBody               As String

    strBody = strBody & Me.Control1Name.Name & " : " & Me.Control1Name.Value & "<BR>"
    strBody = strBody & Me.Control2Name.Name & " : " & Me.Control2Name.Value & "<BR>"
    strBody = strBody & Me.Control3Name.Name & " : " & Me.Control3Name.Value & "<BR>"
    '...

    SendHTMLEmail Me.SEmail, "Follow-Up Item", strBody, False
    Me.SendMark.Value = "Yes"

Open in new window


Option 2
    Dim ctl                   As Access.Control
    Dim strBody               As String

    For Each ctl In Me.Controls
        If ctl.Name <> "Control1ToIgnore" And ctl.Name <> "Control2ToIgnore" And ctl.Name <> "Control3ToIgnore" Then
            strBody = strBody & ctl.Name & " : " & ctl.Value & "<BR>"
        End If
    Next
    SendHTMLEmail Me.SEmail, "Follow-Up Item", strBody, False
    Me.SendMark.Value = "Yes"

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
John's strategy of using the Tag property is another good approach used.
ste5anSenior Developer

Commented:
Well, exactly as you said. You can filter by any criteria..

Dim ctl As Access.Control

Dim strBody As String

For Each ctl In Me.Controls
  If ctl.TabIndex In (1, 4, 7, 9) And Not ctl.Name = "REcID and Category" Then
    strBody = strBody & ctl.Name & " : " & ctl.Value & "<BR>"
  End If
Next

SendHTMLEmail Me.SEmail, "Follow-Up Item", strBody, False
Me.SendMark.Value = "Yes"

Open in new window

But the question remains: What is your use-case? Why not using a list of controls instead? Why not using the Tag property?

Author

Commented:
thank you, how to tag the controls? Under property?
John TsioumprisSoftware & Systems Engineer

Commented:
Yes right click the Control/s  -->Properties-->Other (tab) -->Tag
Clipboard02.jpgClipboard01.jpg.

Author

Commented:
thank you, all guys.
now i want to check every single way you propose;
first it shows the error in If ctl.TabIndex In (1, 4, 7, 9) then
John TsioumprisSoftware & Systems Engineer

Commented:
I don't think that TabIndex is a safe indicator...for example a simple Tab Order --> Auto Order and everything goes kaboom...
Nevertheless is an interesting option.

Author

Commented:
I changed to If ctl.TabIndex = 1 Or ctl.TabIndex = 4 Then-----------works!
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
You shouldn't rely on the TabIndex, this can change.  You would be much better served to use either the control's name or tag property.

Author

Commented:
thank you very much, all worked!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial