Access queries cannot use form controls when vba script is running

Question

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.

Community · Accepted Answer · 2017-05-23 12:12:37Z

1

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.

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered May 1, 2013 at 20:16

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Don George Over a year ago

Unfortunately, the reference to forms!... is not in the VBA script. The forms!... references are in other queries, which are then referenced by the query [report - details] called by the VBA script. So the actual VBA doesn't references the forms!... at all.

Parfait Over a year ago

But you are trying to access a query that uses parameters from a form control. VBA does not allow parameterized action queries to do this. See this link. Maybe develop the earlier queries in VBA that passes the form control values into variables that output querydefs into query objects and even opens the needed recordset.

Don George Over a year ago

Thanks - the 2nd link helped. I had to change the logic around a bit to make it work - but I am all set now. I'm new to Stack Overflow - do I need to do something to close out this question?

Collectives™ on Stack Overflow

Access queries cannot use form controls when vba script is running

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related