Indirect control reference

[Fredrated]

I need to build 4 queries to feed 4 report subforms.
Each query is built from the user choices of an identical set of controls,
in this case, 3 list boxes and 13 check boxes. (The list boxes will have
different entries between the 4 sets and the check boxes tell me in the user
wants to filter and/or sort the queries).

For each of the 4 sets of controls, I have named them similarly, for
example, chkA1, chkA2, chkA3 and chkA4 for 4 check boxes representing the
same thing in each of the 4 sets of controls.

My question is: is there a way (I am weak on the Access object model) to
indirectly refer to the controls, so that I need build only 1 query generator
which I can run 4 times, once for each set of choices, or will I need to code
4 basically identical query generators?

Thanks in advance for any assistance.

Fred

 

[strive4peace]

Hi Fred,

it seems best to make a separate query for each report subform -- but
without knowing specific details, I am only guessing on this. Once you
collect the choices from the user, you would do something like this:

'~~~~~~~~~~~~~~
dim strSQL as string

strSQL = "SELECT " _
& me.choice1_controlname & " as chkA1" _
& me.choice2_controlname & " as chkA2" _
& me.choice3_controlname & " as chkA3" ' ... etc


MakeQuery strSQL, "qry_subreport1"

strSQL = "..." 'create SQL for second query

MakeQuery strSQL, "qry_subreport2"

'once the queries are made, open the report
DoCmd.OpenReport "ReportName", acPreview
'~~~~~~~~~~~~~~~~~~~~~

this code goes in a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

to use the MakeQuery procedure, put this in your code:

MakeQuery strSQL, "YourQueryName"


*** How to Create a Standard (General) Module ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen
-- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have
several procedures (Subs and Functions) in a module, which gives you a
way to categorize them ... ie: basic procedures that would be useful in
any database; procedures that are specific to a particular database;
procedures for converting data; etc


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*