CODEWITHSUNDEEP
formsms-access
Don George
Don George

Reputation: 1328

Access queries cannot use form controls when vba script is running

I have a form with some list boxes that I can use to select a value from a list. I have a query that uses the form value as a filter. The combination works fine - until I try to use a VBA script to open the query. At that point, I get an error message that the query is expecting parameters. The code segments are:

Query - [Entity Selections] - that uses the form (only one of the list boxes will be be used at a time - this finds out which one it is and selects the appropriate items from the table [entity list]):

SELECT val(forms.selections.select_year) as Yr, * 
FROM [entity list] 
where [group]=forms!selections!select_group
UNION select val(forms.selections.select_year) as Yr, * from [entity list] where [G_SG]=forms!selections!select_subgroup
UNION select val(forms.selections.select_year) as Yr, * from [entity list] where [ID_Name]=forms!selections!select_entity
UNION select val(forms.selections.select_year) as Yr, * from  [entity list] where forms!selections!select_group like "All*";

Query - that uses the result of the prior query:

SELECT ... 
FROM [DTXX Detail] 
INNER JOIN [Entity selections] ON ([DTXX Detail].Yr = [Entity selections].Yr) AND ([DTXX Detail].entityid = [Entity selections].EntityID)
ORDER BY ...

These work fine - the [entity selections] query returns the appropriate rows and the 2nd query successfully uses the 1st on as a filter and returns the appropriate rows.

Then, I added a VBA script to output a number of reports like the one above to an Excel file (this script has worked in other databases without any forms). The relevant part of the script is

Source = "[" & Exports.Fields("Source") & "]"
Source = "Select * from " & Source & ";"
If ((Source <> "") And (WS_Name <> "")) Then
    'Get the source data
    Set source_data = DB.OpenRecordset(Source)

Source ends up as Select * from [report - details], which is the name of the 2nd query above. Nothing complicated. The error I get is:

Runtime error 3601.  Too few parameters.  Expected 4

It looks like the query cannot access the form while the script is active. But in the watch window, the script knows the values of all of the form controls.

Can you please help? Thank you.

Upvotes: 1

Views: 1402

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider pulling the form value [report - details] outside your SQL statement. Refer to this for more information: SO answer.

As stated, the form value is not available to a recordset used in VBA.

Upvotes: 1

Related Questions