checkbox control ... creating a filtered database

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); ""
         
      .MoveNext
   Loop
   .Close
End With
Close #intNumFile
MsgBox "Finished", vbOKOnly

Data1.RecordSource = "Select * from OUTFILE"
Data1.Refresh
Data1.Recordset.Close
trudymareschAsked:
Who is Participating?
 
nico5038Connect With a Mentor Commented:
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.

Nico
0
 
nico5038Commented:
Trudy,

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 ?

Nico
0
 
nico5038Commented:
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 !
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
trudymareschAuthor Commented:
Yes, still questions.

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

So...

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 & "
0
 
trudymareschAuthor Commented:
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)
0
 
nico5038Commented:
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.
0
 
trudymareschAuthor Commented:
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?

0
 
nico5038Commented:
Just mail it (see my profile)
0
 
trudymareschAuthor Commented:
Its on its way.
0
 
nico5038Commented:
How does the sample look ?
0
 
trudymareschAuthor Commented:
Forgive me for sounding dumb but how does the query get set.
0
 
trudymareschAuthor Commented:
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)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.