[AccessD] Query problem in VBA

[Bill Benson]

Jim, doesn't the parameters collection require a refresh when setting? Or
is that only when parameters are being added at runtime?

On Mon, Jan 8, 2018 at 11:48 AM, Jim Dettman <jimdettman at verizon.net> wrote:

>
>  Or you can do:
>
> 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)=Eval(
> [Forms]![Form1]![PIH_
> Date_List])));
>
>  Or you can open the querydef (or create one) and assign the parameter
> value
> directly:
>
> Dim db As Database
> Dim qdf As QueryDef
> Dim rs As Recordset
>
>
> Set db = CurDb()
> Set qdf = db.QueryDefs("myQuery")
> qdf.Parameters(0) = Eval(qdf.Parameters(0).Name)
> Set rs = qdf.OpenRecordset()
>
> Or in a loop if you had more than one:
>
> Dim prm as Parameter
>
> For Each prm in qdf.Parameters
>   prm.Value = Eval(prm.Name)
> Next prm
> Set rs = qdf.OpenRecordset()
>
> Eval() works because the name of the parameter is a valid reference.
>
> Or set the SQL string on the fly with the actual value:
>
> strSQL = " 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] & "));"
>
> Jim.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Gustav Brock
> Sent: Monday, January 08, 2018 09:59 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query problem in VBA
>
> Hi Chester
>
> In VBA, the SQL doesn't know about the form.
>
> The easy workaround is a small helper function:
>
> Public Function GetDateListMonth()
>     GetDateListMonth = [Forms]![Form1]![PIH_Date_List].Value
> End Function
>
> And then use this:
>
> 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 = GetDateListMonth()
>
> /gustav
>
> -----Oprindelig meddelelse-----
> Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af
> Kaup,
> Chester
> Sendt: 8. januar 2018 15:31
> Til: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Emne: [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
>