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
  • 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 & "

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?

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Report events not being triggered 8 100
Problem to setup 18 80
count8 challlenge 13 87
base64 decode encode 12 100
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A short article about problems I had with the new location API and permissions in Marshmallow
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now