Also Filtering using a check box (1 Viewer)

[Dayton]

So I'm trying to figure out how to filter a report using a check box.

I have created a form, which if you push an industry labled button on that form it will show you the report with what work was done for that industry.

Now I'm trying to modify it so that I have a group of check boxes; each check box being a different office location the company has.

What I want to make happen is if say out of office A. B. C. and D., A. and D. are checked and I hit the button of a specific industry it will bring me up a report of office A. and D. Combined for that industry.

From what I've figured I can create a bunch of reports of all the different possiblities and link those to that senerio, but that seems tedious and ridiculous.

Is there a way I can code VBA to do this for me? Or am I going to have to go through the proccess of creating a bunch of different querries and reports beyond what I have now to pull up an industry.

(Hope that makes sense. Having a hard time communicating it.)

 

[MarkK]

I don't see a question there, or even a problem to solve. Do you have more you want to say?

 

[Dayton]

I don't see a question there, or even a problem to solve. Do you have more you want to say?

Sorry! Fixed it a bit to try and make sense of it.

 

[MarkK]

What you can do is construct a where clause programmatically. Consider the following code . . .

Function GetWhere(SomeInteger As Integer, SomeBoolean as Boolean) as String
[COLOR="Green"]'   This function conditionally constructs and returns a custom SQL Where clause
[/COLOR]    Dim where as string

[COLOR="Green"]    'here we only include this clause if the item has a value[/COLOR]
    If SomeValue <> 0 then
        where = "AND intField = " & SomeInteger & " "
    End If

[COLOR="Green"]    'here we only include this clause if the item is true
    'which allows us to show all records--when the item is false
    'or only records where the field is true
[/COLOR]    If SomeBoolean then 
        where = where & "AND booField = " & SomeBoolean & " "
    End Id

    where = mid(where, 5)  [COLOR="Green"]'removes the leading 'AND '[/COLOR]

    where = "WHERE " & where
End Function

. . . so does that make sense? Can you see how with this approach you can have very fine grained control over the criteria you use to return record from a query?

 

[Dayton]

Kinda, I'm not exactly and expert in access, but I'll play around with this abit see if I can make something fly.

 

[Dayton]

My questions is. Do I place this code within the cmd button? How would I link this with a check box to acheive my filtering process?

 

[MarkK]

That code constructs the WHERE clause, which is a string. In some cases you might put this code in the report.

Private Sub Report_Open(Cancel As Integer)
   Me.Recordsource = "SELECT * FROM Table " & [COLOR="DarkRed"]GetWhere(12, False)[/COLOR]
End Sub

. . . but it's a way to programmatically customize the records returned by a query, so maybe your SQL is an action query and you create a querydef . . .

dim qdf as dao.querydef
set qdf = currentdb.createquerydef("", "SELECT * FROM Table " & [COLOR="DarkRed"]GetWhere(55, True)[/COLOR])
qdf.execute

Does that help? It's like a snap-in component to customize SQL.

It might also make a ton of sense to expose that as a property of the form that has the check boxes you are talking about since it sounds like you have a form that essentially functions as a filter-making interface. That form might expose a

Public Property SQLWhere as String
[COLOR="Green"]'   this routine uses the settings on the form to construct a where clause[/COLOR]
    Dim where as string

[COLOR="Green"]    'here we only include this clause if the item has a value[/COLOR]
    If Me.SomeValue <> 0 then where = "AND intField = " & Me.SomeValue & " "
[COLOR="Green"]    'here we only include this clause if the item is true
    'which allows us to show all records--when the item is false
    'or only records where the field is true[/COLOR]
    If Me.SomeBoolean then where = where & "AND booField = " & Me.SomeBoolean & " "

    where = mid(where, 5) [COLOR="Green"] 'removes the leading 'AND '[/COLOR]
    where = "WHERE " & where
End Function

. . . so then as long as that form is open, other objects can get the where clause immediately by doing . . .

Private Sub Report_Open(Cancel As Integer)
   Me.Recordsource = "SELECT * FROM Table " & [COLOR="DarkRed"]Forms("YourFilterForm").SQLWhere[/COLOR]
End Sub

Does that help?

 

[Dayton]

Here is a look at my form if you want an idea of what I'm doing.
http://i1155.photobucket.com/albums/p541/Dayton_Thorpe/ExperienceStatement.png

 

[Dayton]

And an idea of the lay out of the report
http://i1155.photobucket.com/albums/p541/Dayton_Thorpe/ExperienceStatementReport.png