Hello,
I was wondering what the most efficient way of capturing check box
values from those records in a form which are checked so that I can
use the values in code.

I have a form with 5 controls, 1 is a checkbox. The user will check
the records he wants chosen and then click a CommandButton. In the
commandButton code I want to be able to display the selected control
values like this:

Dim Control4 as String
Control4 =
"SelectedRec1Value,SelectedRec2Value,SelectedRec3V alue,SelectedRec4Value"
etc

Any assistance Much appreciated
Tahnk you
Regards
Kevin

Kev wrote:

Public Function ListCheckBoxes(frm As Form) As String
'get the names of checkboxes on a form and the value
Dim ctl As Control
Dim s As String

For Each ctl In frm.Controls
If ctl.ControlType = acCheckBox Then
s = s & ctl.Name & " " & NZ(ctl.Value, 0) & ","
End If
Next
ListCheckBoxes = s

End Function

I called it from the form. Ex:

Private Sub Command`_Click()
MsgBox ListCheckBoxes(Me)
End Sub

It simply lists the checkbox name and their values. Modify to suit.

Thanks for this Salad,
I'm not sure if I have applied your code correctly or explained my
issue sufficiently. It only seems to be listing one checkbox.
I have seen where I need to expand my explaination:

I want to capture the value of another control (say Cont4) when Cont1
= Yes, Cont1 being the checkbox:

Cont1 Cont2 Cont3 Cont4 Cont5

Record1 Yes abc 1623 fred 5456

Record2 No xabc 1823 bill 4526

Record3 Yes aybc 7123 mike 4h56

Record4 No abzc 9123 peter 4h56

Record5 Yes abcw 5123 jerry 4g56

Result of variable would give me: variable1 = "fred, mike, jerry"

I wish to then use this variable in future code.
Apologies re confusion of explaination
Regards
Kevin


On Aug 9, 1:59*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

Kev wrote:

It's late. Have you F1'd ControlType? I'm only looking at checkboxes
in the above code. You can check for various types; textbox, combo,
list, command, etc. Did you also F1 Value as well?

You can try the following SAMPLE UNTESTED VBA code to get the list.

You will have to modify it for your particular situation.

Public Function fGetValues()
Dim rst as DAO.Recordset
Dim strSQL as String
Dim strReturn as String

strSQL ="SELECT Cont4 FROM [SomeTable] WHERE Cont1=True"

Set rst = Currentdb().OpenRecordset (strSQL)

If rst.RecordCount > 0 then
While Not rst.EOF
strReturn = ", " & rst!Cont4 & strReturn
rst.NextRecord
Wend
strReturn = Mid(strReturn,3)
fGetValues = strReturn
Else
fGetValues = Null
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Kev wrote: