Query for all

[Guest]

Hi, I have a drop down menu which itself is a list made up of ID's from a
query, this drop down then helps filter another query, the user selects the
ID they want to run the report, how can I have an ALL option in the same drop
down list to make the query and subsequent report show all the results.

 

[strive4peace]

Hi Matt,

one way to do this would be to replace the SQL for the query before the
report is opened.

'~~~~~~~~~~~~~~~~~~~~~~~~~~

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

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'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
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

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

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

The MakeQuery code goes into a general module. Here is a simple
example to use it:

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

strSQL = "SELECT .... FROM ..."

if not isnull(me.combo_controlname) then
strSQL = strSQL & " WHERE IDfieldname = " _
& me.combo_controlname
end if


MakeQuery strSQL, "QueryName"

DoCmd.OpenReport "Reportname", acPreview

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*