[AccessD] Querydef weirdness

Stuart,

Thanks for the idea!

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, July 07, 2005 3:44 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Querydef weirdness


On 7 Jul 2005 at 15:23, Bobby Heid wrote:

> This works fine when running the query by itself or from the report.  
> Well the user wanted the sum of the data on the chooser form when a 
> project (or '(All)' is selected.  So I run the query via a querydef 
> and I get the infamous 'expected 1 parameters' error message.  If I 
> remove the where part of the query, it runs fine from the querydef.
> 
> I talked to a co-worker and he has had problems accessing forms from a 
> query in a querydef too.  Has anyone else run into this?

Frequently :-(

> Anyone have a  work-a-round?
> 

I use a static function to hold the value from the control which I set 
immediately before calling the query. Something like 

Static Function TempStore(Optional varInput As Variant) As Variant
    Dim varStore As Variant
    
    'Initialise the variant if the function is called
    'the first time with no Input
    If varStore = Empty Then varStore = Null
    
    'Update the store if the Input is present
    If Not IsMissing(varInput) Then varStore = varInput
    
    'return the stored value
    TempStore = varStore
End Function

Replace [forms]![FormData]![ProjectID] with TempStore() in the query and 
use

"TempStore [ProjectID]" just before you call the query.







-- 
Stuart