asked on

Need your help with VBA

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

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Pineault

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 Pineault

John's strategy of using the Tag property is another good approach used.

ste5an

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?

Roman F

ASKER

thank you, how to tag the controls? Under property?

John Tsioumpris

Yes right click the Control/s -->Properties-->Other (tab) -->Tag

Roman F

ASKER

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 Tsioumpris

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.

Roman F

ASKER

I changed to If ctl.TabIndex = 1 Or ctl.TabIndex = 4 Then-----------works!

Daniel Pineault

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.

Roman F

ASKER

thank you very much, all worked!!!