Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Too few parameters - RESOLVED

    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
    Last edited by ase001; 2012-04-13 at 10:42.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Does your export code use DAO?
    Have you explicitly declared the parameters in the query Parameters Dialog?
    queryParameters.gif

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



  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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'

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •