Optional parameter query criteria

[raskew]

I've looked at this to the point of brain-death. Perhaps someone can help me through the fog.

I have a working parameter query that starts off like this:

PARAMETERS [Enter mm/yyyy] Text, [Enter SvcCode] Text;
SELECT …..
WHERE ((tblClientBilling.strMoYr)=[Enter mm/yyyy]) AND (tblClientBilling.strSvcCode)=[Enter SvcCode])

…please disregard mismatched parenthesis, etc., I've chopped this down to the essentials and left out about 30 lines of code.

The virtually identical query, when called from a form that includes control cboMoYr, looks like this:

PARAMETERS [Enter SvcCode] Text;
SELECT …..
WHERE ((tblClientBilling.strMoYr)=[Forms]![frmPrintBillingRpts]![cboMoYr]) AND ((tblClientBilling.strSvcCode)=[Enter SvcCode])

Both queries work properly in their individual contexts.

The problem I'm looking to resolve is that some users will use the print form (frmPrintBillingRpts), to call the report, while others will attempt to call the report directly from the database window.

What I need is, if the report is opened from the database window, dialog boxes asking for both [Enter mm/yyyy] and [Enter SvcCode], but if called from form frmPrintBillingReports, the mm/yyyy coming from the form's cboMoYr and only the [Enter SvcCode] dialog to appear.

Hopefully, the error of my ways will jump out and you'll say "…dumbass, here's all you have to do!" Thanks for your assistance.

Bob

 

[Jerry Stoner]

Private Function IsLoaded(strName As String, _
Optional lngType As AcObjectType = acForm) As Boolean

strName (String)
' lngType (Optional) (AcObjectType)

IsLoaded = (SysCmd(acSysCmdGetObjectState, _
lngType, strName) <> 0)
ExitHere:
Exit Function
End Function

Will tell you if the form is loaded (thanks to Getz, Litwin and Gilbert)

Use 2 queries, one asking for both parameters and one referencing the form for cboMoYr.Use an If statement changing record source based on whether or not the form is loaded should do it for you.


[This message has been edited by Jerry Stoner (edited 05-05-2002).]

 

[raskew]

Yup!! "Bob, you dumbass, here's all you have to do!"

Thanks to both Larry Steele (http://www.forumco.com/jmhare/topic.asp?TOPIC_ID=400&FORUM_ID=7&CAT_ID=7&Topic_Title=A+Multi%2DFunction+Parameter+Query&Forum_Title=Queries+from+A+to+Z) and Jerry Stoner (http://www.access-programmers.co.uk/ubb/Forum3/HTML/003643.html) for pointing out the obvious. Gotta say, without their help I'd still be struggling with it 2 years from now since this was a foreign solution to me.

As much as I'd love to make this all happen with just one query, it's obvious that I need two.

Thanks again,

Bob