[AccessD] Query problem in VBA

[Dan Waters]

I agree with not trusting form variables.

Long ago I started setting the value of a standard variable to a form control value, and then used the standard variable in a query.  Did seem to work better.

stgPersonName = frmMainName.txtPersonName

Then use stgPersonName in the query.

Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: January 8, 2018 15:13
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query problem in VBA

Just off the cuff...I do not trust the reliability of Form variables especially in complex queries. In your situation, if this problem continues try using a compound query...one query assembling the core data and next query applying just to the core data results. If necessary the results can be generated layer by layer. Regardless, this method is a good way isolate an intermittent problem.

HTH
Jim   

----- Original Message -----
From: "Kaup, Chester" Kaup at kindermorgan.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Monday, January 8, 2018 6:30:58 AM
Subject: [AccessD] Query problem in VBA

The following query runs fine when executed from the design window or query list. When I try to run it from vba it complains about a missing parameter. When It runs in code is it not picking up the list box value? Thanks for any assistance.

SELECT PIH_Monthly_Allocated_Volumes.ProductionMonth, Sum(PIH_Monthly_Allocated_Volumes.Oil) AS SumOfOil INTO [tbl PIH Monthly Oil] FROM PIH_Monthly_Allocated_Volumes GROUP BY PIH_Monthly_Allocated_Volumes.ProductionMonth
HAVING (((PIH_Monthly_Allocated_Volumes.ProductionMonth)=[Forms]![Form1]![PIH_Date_List]));
--
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