Form of Check Boxes to Filter a Query...HELP

[maytime]

Ok, so here is my situation...(and bear with me, I don't have that much experience with VBA for Access)

I want to make a form that gives my client the ability to select from a bunch of 'check boxes' and when the client hits a command button "Run Query" it will filter a master Query (named "Query for Query Builder") I have made for their exact sought after info.

These check boxes are unbound to any controls and I just need to use them to activate the filters for the master query.

For now, I just have one check box to try to get the code right and the check box is named "GBSize_1X1X1" which should filter a control in the master query called "Description". Here is the code I have right now:

Private Sub GBSize_1X1X1_Click()
If [GBSize_1X1X1] = 1 Then

DoCmd.ApplyFilter , "Query![Query for Query Builder]![Description] = '1X1X1'"

End If
End Sub

Can anyone help me? I don't get any error messages when this code is run, but when I goto the query it is supposed to filter there is no applied filter. Also, I tried putting "If [GBSize_1X1X1] = Yes" in the if statement since I forget how Access reads check boxes, same result.

 

[MarkK]

There seem to me to be two approaches you can use.
- You can update the query results immediately when the user clicks a check box, or
- You can wait until the user makes a series of selections and then hits your "Run Query" button.
From your description it seems like you're doing both. If you want to use the "Run Query" button, you don't need to handle the _Click() event for each check box.

Another thing to decide is are you using AND or OR between your search terms? Let's say you have an option group fraAndOr where And = 1 and Or = 2
Now, at RunQuery_Click() you can construct a where clause to use as a filter...

Private Sub RunQuery_Click()
  dim strWhere as string
  dim opText as string

[COLOR="Green"]  'get your logic operator[/COLOR]
  if me.fraAndOr = 1 then
    opText = "AND "
  else
    opText = "OR "
  end if
[COLOR="green"]  'construct a where clause[/COLOR]
  if me.GBSize_1X1X1 then strWhere = "Description = '1x1x1' " & opText
  if me.GBSize_2x4x8 then strWhere = strWhere & "Description = '2x4x8' " & opText
[COLOR="green"]  '  ...  more statements that add to strWhere[/COLOR]

[COLOR="green"]  'add the word 'Where' and drop the trailing and/or as required[/COLOR]
  if strWhere <> "" then
    strWhere = left(strWhere, len(strWhere) - 4)
    strWhere = "WHERE " & strWhere
  end if
[COLOR="green"]  'assign to the record source of the form[/COLOR]
  me.recordsource = _
    "SELECT * FROM QueryForBuilder " & strWhere

end sub

Alternatively, have every checkbox click event call this routine, and then the recordsource is updated immediately when the user clicks a checkbox and you don't need the 'Run Query' button.

 

[maytime]

Thanks for the advice. I'll give it a try and let you know how it works out. I think I'd rather have the query filters all update when the RunQuery button is clicked so in case someone just halfway clicks a bunch of filters and leaves the database then the query isn't saved like that.

Since I have the query I am trying to filter as the record data for the form, do I not need to have code to open said query before manipulating its filters so the "Me." statements are connected to the right data?

 

[maytime]

Okay so here is how I tweaked your code:

Private Sub RunQuery_Click()
Dim strWhere As String
  Dim opText As String

  'get your logic operator
  If Me.fraAndOr = 1 Then
    opText = "AND "
  Else
    opText = "OR "
  End If
  'construct a where clause
  If Me.GBSize_1X1X1 Then strWhere = "Description = '1x1x1' " & opText
  If Me.GBSize_1X2X1 Then strWhere = strWhere & "Description = '1x2x1' " & opText
  '  ...  more statements that add to strWhere

  'add the word 'Where' and drop the trailing and/or as required
  If strWhere <> "" Then
    strWhere = Left(strWhere, Len(strWhere) - 4)
    strWhere = "WHERE " & strWhere
  End If
  'assign to the record source of the form
  Me.RecordSource = _
    "SELECT * FROM [Query for Query Builder] " & strWhere

End Sub

I tried to run it but Access gets held up at "If Me.fraAndOR = 1 Then". It doesn't recognize what "fraAndOR" is. What should I do to define it?

 

[gemma-the-husky]

a different approach

i find that trying to allow for every eventuality is unreasonabnle and unfeasibly hard and time consunming.

Where users havent the expertise to be able to create their own queries (most of the time!), then instead, give them a single query with all the relevant data on, and save it to a spreadsheet - then they can open the spreadsheet and fiddle and sort however they need to.

 

[maytime]

Yeah, it would be easier to just have the end users know how to make simple queries but my boss thinks that this is the best way to give the client a nice print-out of data that they want without them screwing up the database by fiddling around in it.

I don't need "AND " statements for the filter within each control source since I want to display multiple types of GBSize in this example.

After the RunQuery function is run the number of records is obviously filtered shown by the number of records at the bottom of the form. However, when I try to open the "Query for Query Building Form" (the recordsource that is being filtered) there are no filters present. I tried adding a 'DoCmd.Save acQuery, "Query for Query Building Form" ' statement at the end of the code but I guess I need to add a DoCmd.OpenQuery command in there too somewhere.

Basically what I need the form to do is filter the query like I have said, save the query with the filters specified, and then open up a report using the filtered query as its source. Can this be done?

 

[maytime]

Okay, so I think I figured this out by rethinking things.

Now I am using "IIf" statements in the criteria fields for my query such as this going into the criteria field in the query design view for the 'Description' control I was talking about earlier:

IIf([forms]![Query Builder Form]![GBSize_1x1x1]=-1,"1x1x1") Or 
IIf([forms]![Query Builder Form]![GBSize_1x2x1]=-1,"1x2x1") Or 
IIf([forms]![Query Builder Form]![GBSize_1x3x1]=-1,"1x3x1") Or 
IIf([forms]![Query Builder Form]![Description_All]=-1,"Is Not Null")

This seems to work but I cannot get the last IIf statement to work properly. If the 'Description_All' check box is checked I want the query to return all records. For some reason no matter how I try to write "Is Not Null" (I've used brackets, apostrophes, quotation marks) that IIf statement seems to not want to work. Is there another way to put in an IIf statement to return all records if the 'Description_All' check box is checked? Thanks.