Form checkbox coding query

[happywitchie]

I have a query that returns the following info from my table:
FldName (text)
FldDesc (text)
Show (yes/no)

I have then created a subform based on this query and the form looks
like:

Name | Description | ChkBox
Name | Description | ChkBox
Name | Description | ChkBox
Name | Description | ChkBox
etc

I have two problems that may be related:

1) When I click on a "ShowAll" button on my main form, I need all the
checkboxes on the Subform to automatically be filled. I tried the
following but this only ticks the first line and none of the others:

Forms!frmMyMainForm!frmMySubForm!NameOfCheckBoxField = True

2) What I also want to do is to be able to click on any number of
checkboxes on this subform and store the Names of only the items that
have been ticked.

My pigeon-logic is:
For Each CheckBox in SubformName.Checkboxes
strText = strText + " , " + Name of this Record
Next

I don't know how to do this in Access.

I'd be grateful if anyone could help with this.

Thanks

 

[Douglas J Steele]

Since your form is based on a table, you're best off working with the table,
not the form.

For the "ShowAll" button, use an Update query to set the checkboxes to True:

CurrentDb.Execute "UPDATE MyTable SET Show = True", dbFailOnError

(replace MyTable with the actual table name)

To get the names of the checked rows, use:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim strText As String

strSQL = "SELECT FldName FROM MyTable WHERE Show = True"
strText = vbNullString

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While Not rsCurr = EOF
strText = strText & rsCurr.FldName & ", "
Loop

If Len(strText) > 2 Then
strText = Left$(strText, Len(strText) - 2)
End If

 

[happywitchie]

Thank you very much for this - it helped a lot!