Combo box in a form to filter multiple queries (command buttons)

[Ed in Aus]

Ok I am fairly new to access, I will try to explain this fully... keep in mind my wording will be pretty bad.

What I have is a form on this form there are 5 reports that the user can generate, Eurobodalla, Shollhaven, ACT etc.etc

what I want to be able to do is filter these queries into what job type is in the table, each of the job types are in the table headers with the value being true/false depending on what has been entered for the rows = names of people.

I am not to sure how to do this but it will save huge amounts of time please help.

 

[jal]

We just had a thread on this.

http://www.access-programmers.co.uk/forums/showthread.php?t=158558

In that thread is a link to an old Access 2000 example - an example published by Microsoft. We helped the poster update Microsoft's old example to a modern Access DB.

Keep in mind, as you read that thread, that the variable "strSql" is the filter string being built. A filter is basically a WHERE clause

WHERE LastName = "Smith"

This would filter as to limit the records returned to the "Smith" records.

 

[jal]

Here's a tip - a crucial line in Microsoft's example is this:

"In Design view, display the property sheet of the report, and set the FilterOn property to Yes. "

This will allow the filter string to be used.

 

[Ed in Aus]

Thanks for your reply Jal, I am still having lots of trouble, first thing I can find that I stated incorrectly was they are actually queries not reports...

The database is huge so I can't really post it, I may be able to get what the table looks like if that helps.

I have tried for a few hours to get a filter into this but no luck.

 

[Ed in Aus]

Here is a text file of the way the table is set up, basically the job types are a check box on the form, so are the locations

I want to know who would have a certain job type for a location.

Or for multiple locations/job types

I think this is databse is going to cause headaches in the future.

Thanks for everyones help

 

[jal]

I sure hope a guy can only have one job at a time - otherwise this approach is probably doomed.

You can use a subform to display the results of a query. Create a new form in DataSheet view based on a query "Select * From tblCandidates". Save it under the name "subform1"

On your main form drop a subform control and, using the subform wizard, base it on subform1, also change the control's name to "subform1". Then drop a button for "Search" onto the main form. In the On_click event, build your sql string. You can dynamically change the query in this event, like this:

Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("qryCandidates")
qry.SQL = "SELECT * FROM tbleCandidates WHERE "
qry.Sql = qry.Sql & " [Care Worker] = " & Me.CheckBoxCareWorker.Value " & " AND "
qry.Sql = qry.Sql & " Concreter = " & Me.CheckBoxConcreter.Value " & " AND "

Then at the end you will have to trim the last " AND " (see the thread I mentioned).

Then

Me.subform1.SourceObject = "Query.qryCandidates"
Me.subform1.Requery