MS Access VBA open a record set using a union query in which each query in the union reference two form date fields: FromDt and ToDt [duplicate]

Question

When creating a recordset I get this error => 3061 To Few parameters, expected 2. How can I resolve this error?

I have a union query and it runs fine:

SELECT * FROM q_trip_usa
UNION
SELECT * FROM q_trip_uk
UNION
SELECT * FROM q_trip_thailand
UNION
SELECT * FROM q_trip_mexico
UNION
SELECT * FROM q_trip_japan
UNION
SELECT * FROM q_trip_france;

Query results are this (when run manually):

Each child query has the same form as this shown below:

SELECT trips.trip, trips.when, trips.country
FROM trips
WHERE (((trips.when) Between forms!Form3!FromDt And forms!Form3!ToDt) And ((trips.country)="france"));

Table 'trips' looks like this:

|trip         |when      |country |
|-------------|----------|--------|
|hawaii       |12/19/2022|usa     |
|zion         |6/1/2025  |usa     |
|bangkok      |6/1/2004  |thailand|
|utah         |7/1/2023  |usa     |
|virginia     |7/1/2022  |usa     |
|cancun       |5/1/2019  |mexico  |
|paris        |6/1/2020  |france  |
|manchester   |6/1/2021  |uk      |
|tokyo        |12/1/2023 |japan   |
|florida      |6/1/2024  |usa     |
|san fransisco|1/1/2019  |usa     |
|london       |5/1/2020  |uk      |
|liverpool    |4/1/2020  |uk      |

Form3 look like this:

For reasons I won't get into I need to open a recordset based on this union query. When I attempt this I get the error => 3061 Too Few parameters, expected 2.

The error is on this line:

Here is the button click event code:

Private Sub RunUnionQuery_Click()

    Dim db As dao.Database
    Dim rs As Recordset
    
    Set db = CurrentDb()
    
'   31-Aug-2025
'   q_trips is a union query that references two form fields on form3.
'   this test was able to duplicate the error I received from the C.A.D. database
'   Is it a problem with from date and to date parameters?
'   Too few parameters, expected 2.  Error 3061.

    Debug.Print Forms!form3!FromDt
    Debug.Print Forms!form3!ToDT

    Set rs = db.OpenRecordset("q_trips")
    
    If Not (rs.BOF And rs.EOF) Then
    
    '   Added 29 - Aug - 2025: Lenny
        DoCmd.RunSQL "delete * from trips_stage"

        With rs
            .MoveFirst
            Do While Not .EOF
                DoCmd.RunSQL "insert into trips_stage (trip, when, country) values ('" & !trip & "', '" & !when & "', '" & !country & "');"
                .MoveNext
            Loop
        End With
    Else
    End If

    rs.Close
    db.Close
    Set qd = Nothing
    Set rs = Nothing
    Set db = Nothing
    
    
End Sub

Reference your query as its QueryDef, supply the parameter values, and call the OpenRecordset method as in this answer — HansUp
– HansUp, Commented Sep 1 at 1:11
Whatever your reason for using a UNION query, it makes no sense to do so from the information provided. If the same date range is applied to each country, just query from the table. — June7
– June7, Commented Sep 1 at 2:33
This error means that two column names were not recognized and instead were assumed to be parameters. — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Sep 1 at 12:34
@OlivierJacot-Descombes, error is because of trying to open a Recordset object from a query that has dynamic parameters. Parameters work fine when query object is opened but Recordset SQL cannot process. Nothing wrong with column names. — June7
– June7, Commented Sep 1 at 18:05
Try SELECT * FROM Trips WHERE country IN ("uk","usa","france","mexico","thailand","japan") instead of UNION's. — ValNik
– ValNik, Commented Sep 1 at 21:12

ValNik · Accepted Answer · 2025-09-01 21:21:21Z

1

It looks like you can't pass "Forms!Form3!FromDt" to the query used in VBA.

Try query with parameters (for example "q_trips_B") like this

PARAMETERS FromDt DateTime, ToDt DateTime;
SELECT * FROM Trips 
WHERE trips.when Between FromDt And ToDt
  and country IN ("uk","usa","france","mexico","thailand","japan")

Query

SELECT * FROM Trips 
WHERE country IN ("uk","usa","france","mexico","thailand","japan")

can be used instead your query with UNION.

And use in VBA

    Set qd = CurrentDb.QueryDefs("q_trips_B")
    qd.Parameters!FromDt = Forms!form3!FromDt
    qd.Parameters!ToDt = Forms!form3!ToDt
    Set rs = qd.OpenRecordset()

answered Sep 1 at 21:21

ValNik

7,8381 gold badge8 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

MS Access VBA open a record set using a union query in which each query in the union reference two form date fields: FromDt and ToDt [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related