Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Too Few Parameters

    I have this code in a module

    PHP Code:
    c Sub FillInFields2(strCofNo As StringOptional intIndex As Integer 1)
        
    Dim db As DAO.Database
        Dim rs 
    As DAO.Recordset
        Dim strSQL 
    As String
        Dim strSQL1 
    As String
        Dim strSQL2 
    As String
        Dim strSQL3 
    As String

        On Error 
    GoTo err_FillInFields2
        
        Set db 
    CurrentDb
                strSQL1 
    "SELECT qryEfacs3.orderid,qryEfacs4.partid,qryEfacs3.pdrawing,qryEfacs3.drawissno,qryEfacs4.id , qryEfacs3.traderorderreference, qryEfacs3.partdesc, qryEfacs3.Name, qryEfacs3.ItemNumber,qryEfacs.Cof "
                
    strSQL2 " FROM qryEfacs3 INNER JOIN qryEfacs4 ON (qryEfacs3.partid = qryEfacs4.partid) And (qryEfacs3.orderid = qryEfacs4.orderid)"
                
    strSQL3 " WHERE qryEfacs.Cof ='" strCofNo "'"
         
    strSQL strSQL1 strSQL2 strSQL3
        
        Set rs 
    db.OpenRecordset(strSQLdbOpenDynaset)
        If 
    Not rs.BOF Then
            
    ' fill in fields on form
            Select Case intIndex
            Case 1 To 4
                With Forms!frmCofCNew
                    .Controls("CofNo " & intIndex) = rs("Cof")
                    .Controls("Customer Order No " & intIndex) = rs("traderorderreference")
                    .Controls("Stock Code No " & intIndex) = rs("partid")
                    '
    If Not rs("partnum"Like "5S*" And Not rs("partid"Like "1O*" And Not rs("partid"Like "25S*" Then
                        
    '.Controls("Mob " & intIndex) = rs("salesorderid")
                    '
    End If
                    .
    Controls("Description " intIndex) = rs("partdesc")
                    
    '.Controls("Ext Description " & intIndex) = rs("descline2")
                    .Controls("DWG No " & intIndex) = rs("pdrawing")
                    .Controls("Issue " & intIndex) = rs("drawissno")
                    If intIndex = 1 Then
                        .Controls("Customer Name") = rs("name")
                    End If
                End With
            Case Else
                MsgBox "You have not entered a valid index number."
            End Select
        Else
            MsgBox "You must enter a valid Cof Number!", vbExclamation
        End If

    exit_FillInFields2:
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        Exit Sub

    err_FillInFields2:
        MsgBox Err.Description
        Resume exit_FillInFields2
    End Sub 
    When I enter a Cof No in the form, I get the error 'Too Few Parameters. Expected 1' As far as I can see, all the controls I need to be filled in on the form, are present in the code. Can anyone help me out. I don't understand.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think there will be some syntax error in the SQL.
    I would put in a Debug.print SQL line just before you open the recordset, so you can look at the SQL that has been assembled.
    (Debug.print displays it in the Immediate window)
    If you can't see what is wrong with it, copy it then paste it into a new query. Get it working there, then come back and fix the code.
    You may have a Text value not surrounded by " ' " for example (although I can't see that error).

    Another tactic is to set a Breakpoint early in the code then step through to see which line causes the error.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John. I'll give that a try when I get a minute, and I'll let you know what happens.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I put Debug.Print strSQL2 just before Set rs = db.OpenRecordset(strSQL, dbOpenDynaset), and all it does is prints the line of code. I've never used Debug.print before. Am I doing it wrong?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What you are looking for the Debug.Print statement to do is show you the full SQL statement before Access trys to produce the recordset. This will allow you to double check field and table names. Most of the time that is where the 'Too Few Parameters. Expected 1' error originates.

    Hope this helps.
    Richard

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by robm View Post
    I put Debug.Print strSQL2 just before Set rs = db.OpenRecordset(strSQL, dbOpenDynaset), and all it does is prints the line of code. I've never used Debug.print before. Am I doing it wrong?
    No that is all it does.

    I suggest you use Debug.Print strSQL rather than Debug.Print strSQL2 because strSQL is the whole SQL statement that the recordset is based on.

    Step 1: Can you see any obvious error in the SQL. Incorrect field or table name, as Richard suggest, perhaps a space missing between two elements. If you find any error adjust the code to fix it. Often Step 1 is enough to find the error, but not always. If not go to step 2

    Step 2: Copy the line of SQL from the immediate window, then start to create a new query. Choose not to add any tables, then switch to SQL view and paste in the SQL you copied. What happens if you try to view the query results? Can you switch to Design View? Eventually you should be able to get the query working. When you do, view its SQL and see what is different.
    You then need to work backwards..go back and change the code so it produces the correct SQL.
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I did as you suggested John, and got the SQL in a query, and amended it until it was working (I actually needed to put an extra field in qryEfacs4. In the query, it worked, but when I pasted the code into a module I got a Syntax Error, Missing Operator. So I again put the debug.print results in a query, and got the same error. Just by putting the FROM statement code on a new line, it worked again, but everytime I past the code into my module, I get the error again. The only code in the module, that's not in the query is
    PHP Code:
    strSQL3 " WHERE qryEfacs4.Cof = '" strCofNo "'" 
    here is the rest of the code from the query. I can't see what's wrong.

    PHP Code:
    strSQL1 "SELECT qryEfacs3.orderid, qryEfacs4.partid, qryEfacs3.pdrawing, qryEfacs3.casting, qryEfacs4.id, qryEfacs3.traderorderreference, qryEfacs3.partdesc, qryEfacs3.name, qryEfacs4.Cof"
                
    strSQL2 "FROM qryEfacs3 INNER JOIN qryEfacs4 ON (qryEfacs3.orderid = qryEfacs4.orderid) AND (qryEfacs3.partid = qryEfacs4.partid)" 

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you post the results of the Debug.Print statement?
    Richard

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This line
    strSQL3 " WHERE qryEfacs4.Cof = '" & strCofNo & "'"
    is missing an = sign

    strSQL3 = " WHERE qryEfacs4.Cof = '" & strCofNo & "'"
    Is that all that is wrong?
    Regards
    John



  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Finally sussed it. A missing 'space' between " and From on strSQL2, seems to have done the trick. Thanks for all your help and patience.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I posted this before seeing your response.

    I notice this in the SQL you posted:

    qryEfacs4.CofFROM
    Your code does not put in a space before "From". You can see in this code that there isn't a space at the end of the first line, nor at the start of the next.

    trSQL1 = "SELECT qryEfacs3.orderid, qryEfacs4.partid, qryEfacs3.pdrawing, qryEfacs3.casting, qryEfacs4.id, qryEfacs3.traderorderreference, qryEfacs3.partdesc, qryEfacs3.name, qryEfacs4.Cof"
    strSQL2 = "FROM qryEfacs3 INNER JOIN qryEfacs4 ON (qryEfacs3.orderid = qryEfacs4.orderid) AND (qryEfacs3.partid = qryEfacs4.partid)"
    Also I notice you have a field name of "name". Name is a reserved word in Access and should not be used as a field name. Using it is likely to generate unpredicatable results.

    The general strategy of using debug.print to track down errors in this sort of code is very useful. We all make errors writing code.
    Last edited by johnhutchison; 2011-03-23 at 07:53.
    Regards
    John



  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Unfortunately, 'name' is one of the fields from an linked table. I'm not getting any errors from the module now, even though a couple of the control names have changed. Now I get 'Compile error:Ambiguous name detected:f_intIndex. This is declared at the beginning, and I have code in the AfterUpdate event of controls which refer to it like
    PHP Code:
    Private Sub Cof_No_1_AfterUpdate()
    On Error GoTo err_Cof_No_1_AfterUpdate

        f_intIndex 
    1
        
    If Len([Cof No 1].Text) > 0 Then
            Call FillInFields2
    ([Cof No 1].Textf_intIndex)
        
    End If

    exit_Cof_No_1_AfterUpdate:
        Exit 
    Sub
        
    err_Cof_No_1_AfterUpdate
    :
        
    MsgBox Err.Description
        Resume exit_Cof_No_1_AfterUpdate
    End Sub 
    There are 3 more controls so they are f_intIndex = 2, f_intIndex = 3, and f_intIndex = 4. I'm at a total loss with this.

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Are there two declarations for f_index?

    Do a search within the code.

    Another thing that can happen when you rename controls is that you end up with duplicate procedure declarations:

    e.g. Private Sub Cof_No_1_AfterUpdate()

    That does not sound like the explanation in this case, but I am not sure.


    Last edited by johnhutchison; 2011-03-24 at 16:53.
    Regards
    John



  14. #14
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK. I messed around with the query, as I couldn't find where the f_intIndex was causing the problem, and got it to give me the results I wanted, and ran it through the SQL formatter. The last line of the code was

    PHP Code:
    WHERE ((([Orderid]&"/"& [orderitemnumber])='S75/16')); 
    with S75/16, being the criteria I typed in the query. Because I want choice to come from the form, I changed the strSQL3 statement from the module to

    PHP Code:
    strSQL3 " WHERE ((([Orderid]& " "&[Orderitemnumber]) = '" strCofNo "'" 
    And now I get the error Type Mismatch, but it doesn't say where.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have been through the debug.print sql process ?


    What is the SQL formatter?
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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