[AccessD] Query problem in VBA

[Kaup, Chester]

Thank you everyone for your suggestions. Problem solved.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Monday, January 08, 2018 10:48 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query problem in VBA

[This email message was received from the Internet and came from outside of Kinder Morgan]


 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