MULTIPLE CHECK BOXES IN SEARCH FORM

[BillA]

I am using Allen Browne’s great Search Form
[http://allenbrowne.com/ser-62.html]. This form's Supporting Query (SQ)
brings data from multiple tables. My question regards developing code to use
multiple check boxes (7 of them from a single table) to work with the Allen’s
Search Form’s code.

I’ve concatenated the multiple check boxes and created a single field called
“Sections†in the SQ. I used a pre-query to convert the Y/N of each of the 7
check boxes to their field namesakes; I did this for form display purposes
and ease of reading.

I want to introduce the 7 check boxes in the Search Form, but I’m struggling
to find the right code that would capture which box (or boxes) was checked,
and when the search/filter is run; bring up only the records that include the
selected check box(es) field data.

These are the check boxed field names: 20A; 20B; 60E; 100; 110B; 200 &
BELL. (pretty random stuff) Any one of these will be selected individually
or in any variation, but have been placed in the SQ "Sections" field in the
above order.

Example of SQ “Sections†field from various records:
60E, 200, BELL
20A, 200
20A, 20B
20A, BELL
20B, 60E, 200, BELL
20A

As you can see the data has been placed from left to right. The code I’m
looking for must take into consideration that the user may randomly select
the check boxes in any order. They may check 60E, 20A and BELL then go back
and uncheck 20A.

Any suggestion as to how to approach this?

 

[Douglas J. Steele]

Rather than building the string in SQ as each box is checked, build it once
you know the user's done. You can simply checked each of the seven check
boxes in the correct order:

SQ = vbNullString

If Me.chk20A = True Then
SQ = SQ & "20A, "
End If
If Me.chk20B = True Then
SQ = SQ & "20A, "
End If
If Me.chk60E = True Then
SQ = SQ & "60E, "
End If
' repeat for the remaining checkboxes...

' Remove the ", " from the end of the string (if necessary)
If Len(SQ) > 0 Then
SQ = Left$(SQ, Len(SQ) - 2)
End If

 

[BillA]

Thanks Douglas. Seems logical, thats probably why I didn't think of it! I
will attempt and let you know of my progress.

Take care,
Bill

Rather than building the string in SQ as each box is checked, build it once
you know the user's done. You can simply checked each of the seven check
boxes in the correct order:

SQ = vbNullString

If Me.chk20A = True Then
SQ = SQ & "20A, "
End If
If Me.chk20B = True Then
SQ = SQ & "20A, "
End If
If Me.chk60E = True Then
SQ = SQ & "60E, "
End If
' repeat for the remaining checkboxes...

' Remove the ", " from the end of the string (if necessary)
If Len(SQ) > 0 Then
SQ = Left$(SQ, Len(SQ) - 2)
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using Allen Browne's great Search Form
[http://allenbrowne.com/ser-62.html]. This form's Supporting Query (SQ)
brings data from multiple tables. My question regards developing code to
use
multiple check boxes (7 of them from a single table) to work with the
Allen's
Search Form's code.

I've concatenated the multiple check boxes and created a single field
called
"Sections" in the SQ. I used a pre-query to convert the Y/N of each of
the 7
check boxes to their field namesakes; I did this for form display purposes
and ease of reading.

I want to introduce the 7 check boxes in the Search Form, but I'm
struggling
to find the right code that would capture which box (or boxes) was
checked,
and when the search/filter is run; bring up only the records that include
the
selected check box(es) field data.

These are the check boxed field names: 20A; 20B; 60E; 100; 110B; 200 &
BELL. (pretty random stuff) Any one of these will be selected
individually
or in any variation, but have been placed in the SQ "Sections" field in
the
above order.

Example of SQ "Sections" field from various records:
60E, 200, BELL
20A, 200
20A, 20B
20A, BELL
20B, 60E, 200, BELL
20A

As you can see the data has been placed from left to right. The code I'm
looking for must take into consideration that the user may randomly select
the check boxes in any order. They may check 60E, 20A and BELL then go
back
and uncheck 20A.

Any suggestion as to how to approach this?