Checkbox filter query

[_rob_]

Hey i have this really annoying bug, theres several tick boxes that im using to filter records but if i tick more than one i get a syntax query error? any idea what this could be? i have had several people look at the code and tell me theres nothing wrong.

Option Compare Database
Option Explicit
Const strSQL = "SELECT * FROM issues"

Private Sub filter_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String

If check_os98 = True Or check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strSQL & " WHERE "
End If

If (check_os98 = True) Then
strFilterSQL = strFilterSQL & "os98 = True"
If check_osnt = True Or check_os2k = True Or check_osnt = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_osnt = True) Then
strFilterSQL = strFilterSQL & "osnt = True"
If check_os2k = True Or check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_os2k = True) Then
strFilterSQL = strFilterSQL & "os2k = True"
If check_osxp = True Or check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_osxp = True) Then
strFilterSQL = strFilterSQL & "osxp = True"
If check_fxpda = True Or check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxpda = True) Then
strFilterSQL = strFilterSQL & "fxpda = True"
If check_fxpc = True Or check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxpc = True) Then
strFilterSQL = strFilterSQL & "fxpc = True"
If check_fxas = True Or check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxas = True) Then
strFilterSQL = strFilterSQL & "fxas = True"
If check_fxrs = True Then
strFilterSQL = strFilterSQL & " AND "
End If
ElseIf (check_fxrs = True) Then
strFilterSQL = strFilterSQL & "fxrs = True"
End If

strFilterSQL = strFilterSQL & ";"
Me.RecordSource = strFilterSQL
Me.Requery
End Sub

 

[Mile-O]

There's nothing wrong with the code - the problem is your table design. You have created a repeating group Windows98, Windows2000, WindowsNT, WindowsXP etc. which goes against database normalisation.

When you have this sort of sequence appear in a database then it is an indication that you require a new table. In this case a new table for operating systems.

I have no idea what the rest of the checkboxes represent but it is possible you have made this error throughout a number of different entities.

 

[Mile-O]

Also, as it stands, your SQL will always end in " AND ;" which makes no sense.

 

[_rob_]

right restructuring it really isnt an option, fx are also check boxes, could you give me an example of how to write it with out producing AND. VB isnt my strong point unfortunately. thank you for your help.

 

[Mile-O]

Why is restructuring one table not an option?

All that needs done is one table added called tblOperationSystems with the fields: OperatingSystemID and OperatingSystem. The latter field contains records with the operating system names. Then, you remove all the checkbox fields for operating system and replace them with one field called OperatingSystemID[/b (related the the other one) wherein you just select the operating system. Then you can use a nice SELECT CASE statement to tidy up that code.

You say that there's an error when you select more than one checkbox - at the moment the code you have only deals with the selection of one checkbox. Unless you don't mean the operating system checkboxes.

Maybe if you could clarify exactly what you are trying to do it would be more beneficial/

 

[_rob_]

right if ill see if i can restructure it. how would the case work surely that can only be used for radio buttons?

 

[Mile-O]

It would be something like this. Select Case is a coding structure and is therefore not particular to radio buttons.

i.e.

Select Case MyField
    Case Is = 1 ' Windows 98
        ' do something here
    Case Is = 2 ' Windows 2k 
        ' do something else here
    Case Else
        ' otherwise do this
End Select

 

[_rob_]

but if 2 cases are true will it run both? i want multiple check boxes to be ticked then the appropriate code added to the query

 

[_rob_]

is there a way i can stop it from appending 'AND ;' i cant seem to get a case statement working.

what i want to do, is have a load of records once i check the boxes to select the criterea and clicked filter it makes query and only shows records where its ticked.

vbs really confusing, if only it was php/mysql it would be easy.

 

[godofhell]

In the following line you are adding the check_OSNT 2 times, any particular reason why?
check_osnt = True Or check_os2k = True Or check_osnt = True

 

[godofhell]

If possible post your database with minimum amount of records so that we can see what you are trying to do. This should allow for better interaction with the features you are trying to use and more ideas as to how to approach your issue.

 

[_rob_]

Right i have attached an example, hopefully this will give you an idea of what im trying to do.

thanks so much for any help it really is appreciated i am a complete newb to access.

 

[Mile-O]

What are the acronyms fxrs, fxas, fxpc, and fxpda signifying?

 

[_rob_]

its what the issue effects (fx) and then the thing it effects ie PC or PDA. i renamed it to acronyms because some of them were a little confidential.

 

[godofhell]

Check Boxes used to execute query

Rob, attached is a file with the code I wrote for what you are trying to do. Note that I did not attempt to recreate your idea, simply wrote the code in a manner that will allow you to see what is happening to your query. The check boxes work fine I just did not write all the code for every possible combination of checkboxes that can be selected at any given time. For example if the user selects os2k and osxp, I did not write the code but you can easily add it by following the examples that I already wrote. Here is the code used on the database.

Option Explicit
Const strSQL = "SELECT * FROM issues"

Private Sub filter_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String

'Basic SQL Statement
strFilterSQL = strSQL
'Note that the OR is being used in the SQL Filter instead of the AND since using the AND
'Will look to at each row and only bring up the records that have those check boxes checked
'Using the OR will bring up all records with any of those boxes checked. Only needs 1 box
'To match to be pulled up.

'This Sections checks for the OS Check boxes

'Check if os98 check box is checked
If check_os98 = True And check_osnt = False And check_os2k = False And check_osxp = False Then
strFilterSQL = strFilterSQL & " WHERE os98 = True"

'Check if osnt check box is checked
ElseIf check_os98 = False And check_osnt = True And check_os2k = False And check_osxp = False Then
strFilterSQL = strFilterSQL & " WHERE osnt = True"

'Check if os2K check box is checked
ElseIf check_os98 = False And check_osnt = False And check_os2k = True And check_osxp = False Then
strFilterSQL = strFilterSQL & " WHERE os2k = True"

'Check if osXP check box is checked
ElseIf check_os98 = False And check_osnt = False And check_os2k = False And check_osxp = True Then
strFilterSQL = strFilterSQL & " WHERE osxp = True"

'Check if os98 and osnt check boxes are checked
ElseIf check_os98 = True And check_osnt = True And check_os2k = False And check_osxp = False Then
strFilterSQL = strFilterSQL & " WHERE os98=True OR osnt = True"

'Check if os98 and osnt and os2K check boxes are checked
ElseIf check_os98 = True And check_osnt = True And check_os2k = True And check_osxp = False Then
strFilterSQL = strFilterSQL & " WHERE os98=True OR osnt = True OR os2k = True"

'Check if os98 and osnt and os2K and osXP check boxes are checked
ElseIf check_os98 = True And check_osnt = True And check_os2k = True And check_osxp = True Then
strFilterSQL = strFilterSQL & " WHERE os98=True OR osnt = True OR os2k = True OR osXP=True"

End If
'End of the OS Check boxes check

'This Sections checks for the FX Check boxes

'Check if fxpda check box is checked
If check_fxpda = True And check_fxpc = False And check_fxas = False And check_fxrs = False Then
strFilterSQL = strFilterSQL & " WHERE fxpda=True"

'Check if fxpc check box is checked
ElseIf check_fxpda = False And check_fxpc = True And check_fxas = False And check_fxrs = False Then
strFilterSQL = strFilterSQL & " WHERE fxpc=True"

'Check if fxas check box is checked
ElseIf check_fxpda = False And check_fxpc = False And check_fxas = True And check_fxrs = False Then
strFilterSQL = strFilterSQL & " WHERE fxas=True"

'Check if fxrs check box is checked
ElseIf check_fxpda = False And check_fxpc = False And check_fxas = False And check_fxrs = True Then
strFilterSQL = strFilterSQL & " WHERE fxrs=True"

'Check if fxpda and fxpc check boxes are checked
ElseIf check_fxpda = True And check_fxpc = True And check_fxas = False And check_fxrs = False Then
strFilterSQL = strFilterSQL & " WHERE fxpda=True or fxpc=True"

'Check if fxpda and fxpc and fxas check boxes are checked
ElseIf check_fxpda = True And check_fxpc = True And check_fxas = True And check_fxrs = False Then
strFilterSQL = strFilterSQL & " WHERE fxpda=True or fxpc=True or fxas=True"

'Check if fxpda and fxpc and fxas and fxrs check boxes are checked
ElseIf check_fxpda = True And check_fxpc = True And check_fxas = True And check_fxrs = True Then
strFilterSQL = strFilterSQL & " WHERE fxpda=True or fxpc=True or fxas=True or fxrs=True"

End If
'End of FX Check boxes check

'Checks the value of the Problem Text box and creates appropiate string
'Left the same no changes made here.
If IsNull(probtxt) Or (probtxt) = "" Then
strFilterSQL = strFilterSQL & ";"
Else
strFilterSQL = strFilterSQL & " WHERE problem CONTAINS '" & probtxt & "' ;"
End If

'Sets the Filter for the Record set
Me.RecordSource = strFilterSQL
'Requeries the rocord set with the new Filter set
Me.Requery

'Added to see the Query string that was being produced. Very usefull for testing.
'Remove in live production
Me.txtQuery = strFilterSQL

End Sub

 

[_rob_]

thanks ever so much but i get query error messages with any selection now :S doesnt seem to work.