Too few parameters

Topic

New Reply

Access 2007 sp2

I am using an export to write values from a query out to a text file.

the source query returns data based on the choices from a form.
All standard stuff.

I have included the form fields as parameters in the query.

When I use the vba to try and export I am constantly getting “Too few parameters Expected:4”

This is directly related to the parameters in the query but try as I might I cannot get the parameters recognised with the vba code output.

I have reduced and replaced the query criteria with fixed text and integer values and proven that the code does work when no parameter criteria are entered.

Has anyone experienced/resolved this prviously?

(previously found a thread on “Too Few Parameters” but it did not cover this issue”

Thanks
Alan

Reply | Quote

Replies

Does your export code use DAO?
Have you explicitly declared the parameters in the query Parameters Dialog?
30608-queryParameters

You don’t always need to do this, but if things are not working in can help.

Reply | Quote

Thanks John,
specified the parameters at the start so I thought that would not be part of this problem, so I then wrote the criteria direct into the query and removed the query parameters.
the export worked fine when using no form parameters.
Entering 1 form sourced parameter causes the code to error with “Too few parameters Expected:1”, so the error is being driven by the number of parameters in the actual query.

the query is not being coded in the vba but is a standard query.

short section of code in the vba is:

Code:

    Dim strfile As String
    Dim intOutputfile As Integer
    Dim strOutput As String
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Set db = CurrentDb
    

    strfile = “H:test4.txt” 
            intOutputfile = FreeFile 
            Open strfile For Output As #intOutputfile   
    Set rs = db.OpenRecordset(“qry_MT103_Output”, dbOpenDynaset)

the final line above is the point of the debug error

Cheers

Reply | Quote

Forgot to add: form parameters are in the style of:
[Forms]![frm_Choice]![cmbEmployerRef]

where cmbEmployerRef is a drop down selection on the form ‘frmChoice’

Reply | Quote

Weirdly enough, the parameters are in an earlier query which feeds the end query, but I added the following in:

Code:

 Set qdf = db.QueryDefs(“qry_MT103_Output”)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm

then used:

Code:

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

and I get a result without the Too few parameters error.

Reply | Quote

The short explanation is that you just can’t use form controls as parameters in queries used by DAO. DAO operates at a lower level and does not know about Access forms.

So generally the solution is to write the SQL on the fly using the value of the form parameter.
But it looks like you have found another way by specifying the prm.value.

Reply | Quote