[AccessD] One report using alternate sources

Susan Harkins ssharkins at bellsouth.net
Wed Apr 13 06:56:56 CDT 2005


I've not paid attention to this thread, but thought I'd chime in here with a
simple solution -- but it may not be adequate. OpenReport's openargs method
is a good way to pass criteria or really any value to a report. 

Susan H. 

It's tough to pass parms into a report in any intelligent way. I suggest
instead that you use static functions to set the values your report
requires.

(A static function is one that remembers its value from call to call. 
Typically a static function is called in two ways -- one to set it and one
to get itl This will become apparent below.)

Here is a template static function to use and modify to suit your needs.

<code>
Static Function CurrentX(Optional lngNew As Long) As Long 'Copy this
template each time you need a new set/get function 'Then Replace "X" with
the name of your object, i.e.,"Employee"
'Replace all in current proc and you're done.

    Dim lngCurrent As Long
    If lngNew <> 0 Then lngCurrent = lngNew
    CurrentX = lngCurrent
    #If conDebug = 1 Then
        Debug.Print "Current X: ", CurrentX
    #End If
End Function
</code>

You call such a function in two ways:
Set: CurrentX( 123 )     ' sets the value of CurrentX
CurrentX()                    ' gets the current value of CurrentX

Thus you set the function before running your report and then either tie the
value of CurrentX() to a control or include it in your query, then set the
control to said value. Doesn't matter which you do. This way you obviate all
the hassles.

HTH,
Arthur

Heenan, Lambert wrote:

>That is indeed an alternative approach. The difference is that the 
>report needs to figure out which query to use (by reading parameters 
>from a form
>probably) rather than the calling code which opens the form determining 
>which query to use.
>
>Lambert
>
>  
>
>>-----Original Message-----
>>From:	accessd-bounces at databaseadvisors.com
>>[SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
>>Sent:	Monday, April 11, 2005 1:56 PM
>>To:	'Access Developers discussion and problem solving'
>>Subject:	RE: [AccessD] One report using alternate sources
>>
>>Lambert had a neat idea.  What I do is to have the different queries 
>>(either SQL text or actual queries).  I then set the recordsource for 
>>the report in the OnOpen event of the report.
>>
>>In your case, I would have a select statement that gets it's values 
>>form the calling form.  The select statement would then set a variable 
>>to the query name or actually set the recordsource.
>>
>>Bobby
>>
>>-----Original Message-----
>>From: accessd-bounces at databaseadvisors.com
>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, 
>>Lambert
>>Sent: Monday, April 11, 2005 1:39 PM
>>To: 'Access Developers discussion and problem solving'; 'John Clark'
>>Subject: RE: [AccessD] One report using alternate sources
>>
>>
>>
>>Sure can.  Design you single, master report, and all the various 
>>versions of the query needed to select the subsets of results. Then 
>>bind the report to a copy of one of the queries. You could name the 
>>query copy "FinalOutPut_qry", or whatever.
>>
>>Then, when you want to run any one version of the report, your code 
>>will check which version of the query is needed and then use...
>>
>>DoCmd.CopyObject ,"FinalOutput_qry",acQuery, 
>>"NameOfTheQueryThatSelectsWhatYouWant"
>>
>>i.e. you copy the query that select the records you need to the 
>>'disposable'
>>query named ,"FinalOutput_qry", or whatever, to which the report is bound.
>>The nice thing about this is that it works for MDE as well as MDB files.
>>
>>Lambert
>>
>>    
>>
>>>-----Original Message-----
>>>From:	accessd-bounces at databaseadvisors.com
>>>[SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
>>>Sent:	Monday, April 11, 2005 12:52 PM
>>>To:	accessd at databaseadvisors.com
>>>Subject:	[AccessD] One report using alternate sources
>>>
>>>I've got this program (A2K) that tracks many of the various programs, 
>>>and subsequent "sub-programs," that are provided by our Office for 
>>>the Aging department. I have recently been asked to add about 8 or 9 
>>>reports to the program. The reports for these programs are identical 
>>>to one another except for a single criteria (i.e. they all have name, 
>>>address, age, etc., but one may be specifically for "prog A" and 
>>>another "prog B"). I went ahead and whipped up 9 very similar queries 
>>>and it occurred to me, because the form is starting to get crowded 
>>>w/buttons, that there may be a way to put these together somehow. I 
>>>have created an option group, which lists the programs, but now I 
>>>would like to click on a button that runs a report based on the 
>>>option group choice. I know I can do this, by calling different 
>>>reports, but is there a way to actually create one single report that 
>>>uses the different queries based on this same option choice?
>>>
>>>Thanks,
>>>
>>>John W Clark
>>>--
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>      
>>>
>>--
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>--
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>    
>>


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 4/12/2005

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list