checkbox control ... creating a filtered database

Posted on 2000-04-13
Last Modified: 2010-04-16
I need to created a "filtered" database where
1) The user decides what to filter
2)The filter is more than one condition.

So I have a vb form which a bunch (35) of checkboxes for the user to choose from (any number thereof) each checkbox.label represents a value that the field contains (for the filter)....

The part I can't seem to work out is how to put the checkboxes in a loop to run through the database.

Heres what I have so far.
'Creates the text file
Open "C:\temp\dofile.txt" For Output As #intNumFile

'heres where I need help...
'I want to include the record in the filtered database for all checked boxes if the checkbox.label is equal to data1.recordset![specflag].

'the rest of the code spits out the filtered database to the text file
With Data1.Recordset

   Do Until .EOF
      Print #intNumFile, Chr$(34) & !CustNum & Chr$(34); "," & _
      Chr$(34) & Chr$(34); "," & _
      Chr$(34) & Chr$(34); "," & _
      Chr$(34) & !CustName & Chr$(34); "," & _
      Chr$(34) & Chr$(34); "," & _
      Chr$(34) & Chr$(34); "," & _
      Chr$(34) & !Address1 & Chr$(34); "," & _
      Chr$(34) & !Address2 & Chr$(34); "," & _
      Chr$(34) & !City & Chr$(34); "," & _
      Chr$(34) & !Province & Chr$(34); "," & _
      Chr$(34) & !Code & Chr$(34); "," & _
      Chr$(34) & "CA" & Chr$(34); "," & _
      Chr$(34) & "0" & Chr$(34); "," & _
      Chr$(34) & !PhoneNum & Chr$(34); "," & _
      Chr$(34) & Chr$(34); "," & _
      Chr$(34) & !HomeEmail & Chr$(34); ""
End With
Close #intNumFile
MsgBox "Finished", vbOKOnly

Data1.RecordSource = "Select * from OUTFILE"
Question by:trudymaresch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
LVL 54

Expert Comment

ID: 2713108

Normally you would specify the selection criteria in the WHERE clause of the SQL statement so:
Data1.RecordSource = "Select * from OUTFILE WHERE field1 = " & num-checkbox-substitution-value1 & " field2 = '" & text-checkbox-substitution-value2 & " field3 = #" & text-checkbox-substitution-value2 & "#;"

Your programming will thus be reduced to constructing a WHERE string that does the selection !

Still questions ?

LVL 54

Expert Comment

ID: 2713125
BTW why 35 checkboxes?
Normally (when single values are to be choosen) you use a combobox or a listbox with single select.
For multiple values you can use a multiselectable listbox.

Thus you can take the value from the combo/list-box and place it in the WHERE string. With checkboxes you will have to keep the checked box and the value aligned !

Author Comment

ID: 2713164
Yes, still questions.

There is only one field to filter on within that select query...


Select * from OUTFILE where OUTFILE.Specflag = to what???

Checkbox 1 to 35 If the checkbox.value=1
The checkboxes aren't in an array...
I'm looking for the easiest way to write the select query then.

Not sure what this means
" & num-checkbox-substitution-value1 & "
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


Author Comment

ID: 2713171
Check boxes at the users request.  I do believe that a multselectable listbox would be more difficult to keep track of what the user is selecting, this way he doesn't have to type the actual value requested.  (Its right in front of him in the checkbox)
LVL 54

Expert Comment

ID: 2713397
OK Trudy,

First you need:
Select * from OUTFILE where OUTFILE.Specflag IN (flag1, flag2, etc);

This gives you the opportunity to specify multiple values for one field.

Still the listbox approach can work. Just place the checkbox text and the selection value, but hide the selection value from the user. (set column width to zero)
Now you can loop through the ItemsSelected and place the hidden value in the IN ( ) part of the WHERE.

If both "checkbox-text" and "corresponding-value" are in a seperate table you can modify the contents of the listbox and you don't have to maintain your code! (Even your users could do so if you want them to!)

The: " & num-checkbox-substitution-value1 & "
means that numerics are placed "straight away", for text you need to enclose the field with an extra ' (single quote) and dates should be enclosed by # to make the query work.

Author Comment

ID: 2713427
Hi Nico5038 and thank you for your help, I don't follow you on the listbox approach.

If I do a form up for you with a listbox on it containing all of the values and email it to you can you show me what you mean?

LVL 54

Expert Comment

ID: 2713449
Just mail it (see my profile)

Author Comment

ID: 2713481
Its on its way.
LVL 54

Expert Comment

ID: 2728559
How does the sample look ?

Author Comment

ID: 2728896
Forgive me for sounding dumb but how does the query get set.
LVL 54

Accepted Solution

nico5038 earned 200 total points
ID: 2728939
I was referring to the checkbox samples on the form. (The use of arrays)

For the query I think:
Dim rs as recordset
set rs = data1.OpenRecordset("<the query>")

But I'm mixing up with access regularly, as that's most of the coding I write at the moment.


Author Comment

ID: 2732607
I like the use of arrays.  I'm now just working out the SQL.  I will be posting your comment as the answer... (soon I hope)


Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question