Run time query creation 1

[SiJP]

I want to build a query at run time based on criteria specifed on a form.

Step 1: I have 10 checkboxes which I want to have as column headings, depending on which boxes the user selects

Step 2: I want to filter the query based on criteria specified in text boxes on the form (could be more than one).

I have all the validation on the form done.. I am struggling with how to build this query!

Can anyone suggest somewhere to read up on this please?

TIA
Simon

 

[saroyal]

here is an idea for step 1 (assuming i've understood what u want)

are these check boxes related to some fields in a table so that checkbox 1 represents field 1 in table x?

if so then each checkbox could be named as chkBox1, chkBox2, etc., and in the tag property of each checkbox could be the actual fieldname which it represents. then this code could build step1 of the query:

-begin code-
Dim strSQL as String, intI as Integer

strSQL = ""

For intI = 1 to 10
If Me("chkBox" & intI).Value Then
strSQL = strSQL & Me("chkBox" & intI).Tag & ", "
End If
Next intI

If strSQL <> &quot;&quot; Then
strSQL = Left(strSQL, Len(strSQL) - 2)
strSQL = &quot;SELECT &quot; & strSQL & &quot; FROM tablename&quot;
End If
-end code-

the code loops through the 10 checkboxes checking if the value is true. if the value is true then the fieldname is appended to the sql statement from the tag property. afterwards the trailing comma is removed and the other parts of the sql statement are added.

from there strSQL could be used to open a recordset or saved to a querydef.

Does this help with step 1?

simon

 

[SiJP]

Hia, I've done so far already with the checkboxes and getting the strSQL part not prob at all.. its the querydef part I suppose I need help with.. guess I'll have to read up on this somewhere!!

Thanks for the tip thou!

 

[saroyal]

if creating a new query def then:

Set querydef = CurrentDB.CreateQueryDef(queryname, strSQL)

if amending an existing querydef then:

Set querydef = CurrentDB.QueryDefs(queryname)
querydef.sql = strSQL

querydef.close

 

[SiJP]

Perfect! Thats what I needed - thanx!