Too Few Parameters... Form Search Causing the Issue

Question

I have a sub that exports tables from my Query in Access to Excel. In Access, I have a form that runs the Query and allows user to update the Query as needed. If I run the sub with set parameters (not using the form), for instance, "Austria" the sub works fine. But when I use the form, and write in "Austria" I get the area message:

3601 Too few Parameters. Expected 1.

The issue appears to be with the HAVING ((([Game Categories].Lottery)=[Forms]![TransferToExcel]![listLotteryName]) (If replaced with Austria, the sub works fine)

Sub Mysub()
Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Dim qdfQUERY2014sales As QueryDef
Dim rsQUERY2014sales As Recordset

Set qdfQUERY2014sales = CurrentDb.QueryDefs("QUERY2014sales")
Set rsQUERY2014sales = qdfQUERY2014sales.OpenRecordset()

Set objexcel = CreateObject("excel.Application")
objexcel.Visible = True


On Error GoTo Openwb
wbExists = False
Set wbexcel =  objexcel.Workbooks.Open("C:\Users\MORTBANKER\Documents\test.xlsm")
wbExists = True

Openwb:
On Error GoTo 0
If Not wbExists Then
    Set wbexcel = objexcel.Workbooks.Add()
End If

CopyToWorkbook wbexcel, rsQUERY2014sales

'need to save the workbook, make it visible or something.
End Sub

Private Sub CopyToWorkbook(objWorkbook As Excel.Workbook, rsQRY As Recordset)
Dim newWorksheet As Excel.Worksheet
Set newWorksheet = objWorkbook.Worksheets.Add()

 With newWorksheet
    .Range("A1").CopyFromRecordset rsQRY   '<-magic happens here!
End With
'Copy stuff to the worksheet here'
End Sub

Anyone have any thoughts how I can fix this?

HansUp HansUp · Accepted Answer · 2015-07-14T21:18:39

When you have a query which references form controls, and use that query as the data source for OpenRecordset, Access does not resolve the form controls and retrieve their values. Instead it treats the control references as just regular query parameters ... which means it expects you to supply the parameter values separately.

Fortunately that is easy to do. Since you have the QueryDef open, just loop through its Pararmeters collection and supply the values for each of them.

Set qdfQUERY2014sales = CurrentDb.QueryDefs("QUERY2014sales")
Dim prm As DAO.Parameter
For Each prm In qdfQUERY2014sales.Parameters
    prm.Value = Eval(prm.Name)
Next
Set rsQUERY2014sales = qdfQUERY2014sales.OpenRecordset()

That approach works because Access will tell you prm.Name is "[Forms]![TransferToExcel]![listLotteryName]" per your example. Then Eval("[Forms]![TransferToExcel]![listLotteryName]") will give you the value of that control.

Actually, if you're certain there will only ever be one such parameter in your query, you don't need to loop through its Parameters collection. You could just do this instead:

With qdfQUERY2014sales
    .Parameters(0).Value = Eval(.Parameters(0).Name)
End With

Too Few Parameters... Form Search Causing the Issue

1 Answers