link form, query and report together

[taopeng]

Hi,
I want to base my query on checkbox values from a form and then automatically opens a report that shows the query result. How do I detect checkbox values and how do I put it in my query dynamically and link it to report? There are more than 15 checkboxes in the form. I can't do 15 if then statement. Plus there are combinations of answers. Is there any code example/web site there I can refer to?
Thanks a lot!!!

 

[demoman]

Howdy

You should be able to change your report's query definition prior to calling the OpenReport action. I do this often for misbehaving sub-reports. I'm sure I have NOT done this for 15 different 'where' clause conditions, but I would be surprised if Access has a limit. Even if Access has a limit, you could split the query (and conditions) into two parts.

If you need help with the changing of the query definition, post back, and I will fire up the Server and send you some code examples.

Cheers!

 

[taopeng]

Yes! Please give me some code examples! I really appreciate it.
THANK YOU.

 

[demoman]

taopeng

Essentially, what you are doing is appending to the SQL definition of your query. Therefore, you want to make the basic query definition as vanilla as possible. If you can avoid ANY 'where' condition it is preferrable.

Condsider this very basic SQL statement:

SELECT FirstName,LastName from qryContacts;

If we had a report based on this query, it would return ALL rows. Now, let's say, in a program we include the following code:

Dim dbs As Database
Set dbs = CurrentDb
Dim strSQLDef As String 'default query SQL definition
Dim strSQLHold As String 'Keep default query SQL definition
Dim qdfContacts As DAO.QueryDef

Set qdfContacts = dbs.QueryDefs("Contacts&quot
strSQLDef = qdfContacts.SQL
strSQLHOLD = qdfContacts.SQL 'need old definition to reset

AT THIS POINT YOU HAVE THE CURRENT QUERY DEFINTION HELD IN 2 VARIABLES; strSQLDef you will modify, and you will reset the query with strSQLHOLD.

So, lets add a condititon to the query 'where' clause.

strSQLDef = strSQLDef & " where ContactCity = 'Seattle'"

Normally 'seattle' would be a variable, or a control.value. To change the query definition use the following syntax:

qdfContacts.SQL = strSQLDef

After you get the query correct use the :
DoCmd.OperReport &quot;<report name>&quot; to print your report. then reset your query definition:

qdfExtra.SQL = strSQLHold

I hope this all makes sense. If not, send me an e-mail and I will do my best to help you out!

Cheers
Steve