Can anyone explain why this VBA breaks when I change the Const Domain to a differently formed query? 1

[keun]

I have this VBA that loops through records and splits the printing of a report based on the LoopedField. I need to add date range parameters to the underlying query and I want to store the date values in a form.

When I change the Const Domain to point to the query with the date parameters I get the error message

Run-time error 3061: Too few parameters. Expected 2.

The reason I need to change the Const Domain is that since I am applying a date filter I need to filter the looped field so that I only create reports for UserOrgs that have data during the filtered date range. Without filtering the looped field I get reports for every UserOrg and up with blank reports.

When executed on their own, not through the report, the two query results look exactly the same. Same fields, same values, same record count. I cannot figure out why I am having the problem. I am sure you need information that I have not provided. If you can tell me where I should investigate I certainly appreciate it!

I am using a query as the const domain. The working query references a query which references another query which references tbl1. The broken query references a query which references a query (which picks up the date parameter) which references tbl1. Like I said, if I leave the date range wide open I get the same results when I run the working Const Domain and the broken Const Domain. Since the underlying queries are presenting the same data to the report I cannot figure out why one is not working.

I joined this forum in 2005. I am still a hack.

 

[jrbarnett]

Does your broken query run standalone without errors? Does it have the fields referenced in your LoopedField, NewFilename and Reportname constants?

John

 

[SkipVought]

So if you [highlight #FCE94F]change the Constant[/highlight] (oxymoron?) to a variable, what happens?

 

[keun]

Does your broken query run standalone without errors? Does it have the fields referenced in your LoopedField, NewFilename and Reportname constants?

Yes. The broken query runs ok. It contains the LoopedField which is MinOfID and the NewFileName which is UserOrg. The Reportname constant is not contained in either query.



I joined this forum in 2005. I am still a hack.

 

[MajP]

What is the sql of the query? Certain queries will execute from the vbe, but not resolve in code. As john stated, a reference to a form control could be the issue.

 

[keun]

working query:

SELECT qrySPU01.[User Org] AS UserOrg, qrySPU01.MinOfID
FROM qrySPU01
GROUP BY qrySPU01.[User Org], qrySPU01.MinOfID;

broken query:

SELECT qryAAALogWithFilters.[User Org] AS UserOrg, Min(qryAAALogWithFilters.ID) AS MinOfID
FROM qryAAALogWithFilters
GROUP BY qryAAALogWithFilters.[User Org];

I joined this forum in 2005. I am still a hack.

 

[MajP]

How about qryAAAlogwithFilters? Please post.

 

[keun]

SELECT
[audit_session_2014-09-16T15-42-].ID, 
[audit_session_2014-09-16T15-42-].SessionID, 
[audit_session_2014-09-16T15-42-].Duration, 
[audit_session_2014-09-16T15-42-].DurationDec, 
[audit_session_2014-09-16T15-42-].[Event Date], 
[audit_session_2014-09-16T15-42-].Username, 
[audit_session_2014-09-16T15-42-].[Full Name], 
[audit_session_2014-09-16T15-42-].[User Role], 
[audit_session_2014-09-16T15-42-].[User Org] AS [User Org], 
[audit_session_2014-09-16T15-42-].Type, 
[audit_session_2014-09-16T15-42-].Via, 
[audit_session_2014-09-16T15-42-].Vault, 
[audit_session_2014-09-16T15-42-].[Patient MRN], 
[audit_session_2014-09-16T15-42-].[Patient Vault], 
[audit_session_2014-09-16T15-42-].[Patient Name], 
[audit_session_2014-09-16T15-42-].[Patient Last], 
[audit_session_2014-09-16T15-42-].LastMRN, 
[audit_session_2014-09-16T15-42-].[Result Title], 
[audit_session_2014-09-16T15-42-].[Document Source], 
[audit_session_2014-09-16T15-42-].[IP Address]
FROM [audit_session_2014-09-16T15-42-]
WHERE ((([audit_session_2014-09-16T15-42-].[Event Date]) Between [Forms]![Form1]![beg] And [Forms]![Form1]![end])
AND (([audit_session_2014-09-16T15-42-].[User Org])<>"OrgA" And ([audit_session_2014-09-16T15-42-].[User Org])<>"OrgB" And ([audit_session_2014-09-16T15-42-].[User Org])<>"OrgC" And ([audit_session_2014-09-16T15-42-].[User Org])<>"OrgD")
AND (([audit_session_2014-09-16T15-42-].Type)="Extended Search" Or ([audit_session_2014-09-16T15-42-].Type)="Community Search"));

I joined this forum in 2005. I am still a hack.

 

[keun]

I think I solved the problem, so those of you who are staying up late thinking about it, you can get some rest!

Here is what is odd - when I set the Const Domain to a query, I get the error. But if I use that same query to make a table and then set the Const Domain to the table things work. Can anyone explain that?

I joined this forum in 2005. I am still a hack.

 

[PHV]

You have to resolve the parameters:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("NameOfQuery")
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

As everyone was hinting the issue has to do with referencing parameters or controls interpretted as parameters. Here is a more detailed explanation to explain PHVs solution

In all likelihood, the query itself makes reference to a control on a
form (probably as a criterion) or has some other parameter, or an
unrecognized name that the query engine interprets as a parameter. When
you run queries via the Access user interface, Access fills in the
form/control parameter references for you and prompts you for any other
parameter values, but when you use DAO to open a recordset on a query,
the parameters are not automatically resolved. You have to supply a
value for each one. You can do that by name:

oQuery.Parameters("MyParmName").Value = "foo"

or you can use a handy trick to assign values to any parameters that are
control references:

Dim prm As DAO.Parameter

For Each prm in oQuery.Parameters
.Value = Eval(.Name)
Next prm

oQuery.OpenRecordset(dbOpenSnapshot)

Dirk Goldgar, MS Access MVP

 

[keun]

I am so far out of my depth. I thank you all for responding. As things go, I will probably understand this two years from now.

I joined this forum in 2005. I am still a hack.

 

[lameid]

A fix, although not pretty and not using your query objects...

Dim strSQL as String ' Add this declaration
'Build a SQL string without paramaters
strSQL = "SELECT " & _
"[audit_session_2014-09-16T15-42-].[User Org] AS [User Org], " & _
"Min([audit_session_2014-09-16T15-42-].ID) " & _
"FROM [audit_session_2014-09-16T15-42-] " & _
"WHERE ((([audit_session_2014-09-16T15-42-].[Event Date]) Between [red]#" & [Forms]![Form1]![beg] & "# And #" & [Forms]![Form1]![end] & "#[/red]) " & _
"AND (([audit_session_2014-09-16T15-42-].[User Org])<>""OrgA"" And ([audit_session_2014-09-16T15-42-].[User Org])<>""OrgB"" And ([audit_session_2014-09-16T15-42-].[User Org])<>""OrgC"" And  " & _"([audit_session_2014-09-16T15-42-].[User Org])<>""OrgD"") " & _
"AND (([audit_session_2014-09-16T15-42-].Type)=""Extended Search"" Or ([audit_session_2014-09-16T15-42-].Type)=""Community Search"")) " & _
"Group By [audit_session_2014-09-16T15-42-].[User Org]; "

'Open the SQL string instead
Set rs = Currentdb.Openrecordset(strSQL)

I assumed that [audit_session_2014-09-16T15-42-].[Event Date] is an actual date and hence the # delimiters... Hopefully I didn't make any mistakes translating the query.

In case you missed it, the [red]red piece[/red] is pulling the parameter out of the query and is one possible fix. Personally I'm a little surprised it is unhappy about it but then again I can see that I have not done anything like that in many years if ever and overlooked this nuance.

 

[jrbarnett]

I would also make another change to your Const definitions from:

Const Folder = "C:\Path\"

to

Const Folder As String = "C:\Path\"

This will ensure the VBA interpreter treats them as strings rather than variants. You put the data types on your variables (Dim x as String etc), but not on the constants. It may speed up your code slightly.

John

 

[keun]

Cool! Thanks!

I joined this forum in 2005. I am still a hack.

 

[strongm]

Actually, John, as far as I am aware VBA treats non-explicit declarations of Constants a little differently from variables, and gives them (normally) the most appropriate type rather than making them variants. In fact Variant is the one type it is more-or-less impossible for a Const to be without explicitly declaring it as such!