Generate string from selected checkboxes on continuous form

[Ben]

I have a continuous form with a bound checkbox ("ExportSelect") on
each line. I would like to have a command button on the form footer
with an onClick event that finds all the records with checked
checkboxes. Each time a checkbox is true I'd like the string to add to
itself the value of another field on that record....

I've used the "count(iif([ExportSelect]=true,1,null))" on an unbound
field in the footer to successfully count the checked records, but I
can't apply this to create a string I want.

Example of my continuous form:
[ ] dog
[x] cat
[x] zebra
[ ] horse
[x] fish

In the onClick event of the button at the footer I'd like to create
the string: "cat,zebra,fish"

Private Sub TargetExcel_Click()
Dim frm As Form
Dim strTarget as String
Set frm = Forms!frmSearchResults
For Each Record In frm
If Me.ExportSelect = True Then
strTarget = strTarget & "," & Me.TestNum_
End If
Next Record
End Sub

However, this just code would just create the string
"fish,fish,fish,fish,fish"

Thanks in advance.

 

[Ken Snell]

I'm not sure what you're going to do with strTarget value, but, using your
code design:

Private Sub TargetExcel_Click()
Dim frm As Form
Dim strTarget as String
Dim rst As DAO.Recordset
Set frm = Forms!frmSearchResults
Set rst = frm.RecordsetClone
strTarget = ""
With rst
If .EOF = False and .BOF = False Then
.MoveFirst
' Loop through the form's records
Do While .EOF = False
If .Fields("ExportSelect").Value = True Then
strTarget = strTarget & "," & .Fields("TestNum").Value
End If
.MoveNext
Loop
End If
End With
' Strip trailing comma
If Right(strTarget, 1) = "," Then strTarget = Left(strTarget,
Len(strTarget))
Set rst = Nothing
Set frm = Nothing
End Sub

 

[Ben]

Thanks! Works perfectly.

 

[Ken Snell]

After some further thought, here is some revised code (using
RecordsetClone.RecordCount property to test if there are any records in the
recordset, instead of using the EOF and BOF properties; and stripping a
leading comma instead of a trailing comma [I'd misread your code
originally]):

Private Sub TargetExcel_Click()
Dim frm As Form
Dim strTarget as String
Dim rst As DAO.Recordset
Set frm = Forms!frmSearchResults
Set rst = frm.RecordsetClone
strTarget = ""
With rst
If .RecordCount <> 0 Then
.MoveFirst
' Loop through the form's records
Do While .EOF = False
If .Fields("ExportSelect").Value = True Then
strTarget = strTarget & "," & .Fields("TestNum").Value
End If
.MoveNext
Loop
End If
End With
' Strip leading comma if you don't want it to remain
If Left(strTarget, 1) = "," Then strTarget = Mid(strTarget, 2)
Set rst = Nothing
Set frm = Nothing
End Sub