Link to home
Start Free TrialLog in
Avatar of kbay808
kbay808Flag for United States of America

asked on

How to use VBA to build a string consisting of multiple strings, but only adding the strings based on whether or not the corresponding check box is checked in MS Access?

I have multiple check boxes in an access form that I need to combine all of the values for those that are check so that I can put it into a table.  The problem is that the result has blank lines for the check boxes that are unchecked.

Example:
Check box 1: Missing contact name
Check box 2: Missing phone number
Check box 3: Missing email address

Private Sub Test_Click()
If Me.Check292 = -1 Then result1 = "Missing contact name"
If Me.Check294 = -1 Then result2 = "Missing phone number"
If Me.Check309 = -1 Then result3 = "Missing email address"
result = result1 & vbCrLf & result2 & vbCrLf & result3
MsgBox result
End Sub

Open in new window


If I have the 1st and 3rd check boxes check the value looks like this:
Missing contact name

Missing email address

How can I remove the blank lines, but still keep each string on a different line?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

test this

Private Sub Test_Click()
If Me.Check292 = -1 Then result1 = "Missing contact name"
If Me.Check294 = -1 Then result2 = "Missing phone number"
If Me.Check309 = -1 Then result3 = "Missing email address"
result =IIf(result1 & "" <> "", result1 & vbCrLf, "") & IIf(result2 & "" <> "", result2 & vbCrLf, "") _
    & IIf(result3 & "" <> "", result3, "") 
MsgBox result
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Eoin Ryan
Eoin Ryan
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kbay808

ASKER

That worked great...  Thanks