trudymaresch
asked on
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
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
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 !
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 !
ASKER
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-
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-
ASKER
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)
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-
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.
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-
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.
ASKER
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?
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?
Just mail it (see my profile)
ASKER
Its on its way.
How does the sample look ?
ASKER
Forgive me for sounding dumb but how does the query get set.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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-
Your programming will thus be reduced to constructing a WHERE string that does the selection !
Still questions ?
Nico