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 & "
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select specific duplicate row based on specific criteria 4 86
Is online banking safe? 11 127
Ruby or Python 7 133
SQL Server Database Inventory 7 32
A short article about problems I had with the new location API and permissions in Marshmallow
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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 …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

752 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