Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: Error: Too few parameter. Expected 1

    Hi,

    I am trying to create a recordset in within a form and when I click on the button I get the error "Too few parameters. Expected 1". The recordset is created from a saved query which runs OK by itself. I have included the code and also the SQL from the query. Can some one look at it for me and tell me what I am doing wron. I am new to this. Thanks.

    Code:

    Sub Save_Click()
    On Error GoTo Err_Save_Click

    DoCmd.SetWarnings False

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim mySql As String

    ' stock = [Description]

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SalesMaturity", dbOpenDynaset)

    stock = [Qty]
    If rs1.RecordCount <> 0 Then
    Do While [Qty] <> 0
    [seed] = rs1![Seedling Id]
    If rs1![Qty in Stock] >= [Qty] Then
    mySql = "INSERT INTO [Reduction Transaction] ([Seedling Id], [Reduction Type], [Reduction Qty], [Reduction Date]) VALUES ([seed], [Reduction], [Qty], [Date])"
    DoCmd.RunSQL (mySql)
    [Qty] = 0
    Else
    [new qty] = rs1![Qty in Stock]
    mySql = "INSERT INTO [Reduction Transaction] ([Seedling Id], [Reduction Type], [Reduction Date], [Reduction Qty]) VALUES ([seed], [Reduction], [Date], [new qty])"
    [Qty] = [Qty] - rs1![Qty in Stock]
    DoCmd.RunSQL (mySql)
    End If
    rs1.MoveNext
    Loop
    rs1.Close
    Set rs1 = Nothing
    End If


    SQL in the saved query (SalesMaturity):

    SELECT Plants.[Seedling ID], Plants.Seedling, [Plant Category].[Plant Code], [Seedling Category].Description, Plants.[Date Planted], Plants.[Qty in Stock], [Date planted]+([Maturity Period (Wks)]*7) AS [Maturity Date]
    FROM [Plant Category] INNER JOIN ([Seedling Category] INNER JOIN Plants ON [Seedling Category].[Seedling ID]=Plants.Seedling) ON [Plant Category].[Plant ID]=Plants.[Plant Type]
    WHERE (((Plants.Seedling)=Forms.[Seedling Sales]!Description) And (([Plant Category].[Plant Code])="S") And ((Plants.[Qty in Stock])>0) And (([Date planted]+([Maturity Period (Wks)]*7))<=Now()))
    ORDER BY [Date planted]+([Maturity Period (Wks)]*7);
    Last edited by jrmellis; 06-04-08 at 18:06.

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    usually i get that same error when i've typed something in my sql statement incorrectly, like tblEmployee vs tblEmployees. try copying your sql, pasting it into sql view in your query designer, and then flip to design view and see if it labels any of your fields as Expr1: etc.

  3. #3
    Join Date
    Jun 2008
    Posts
    4
    jmahaffie, The SQL statements did come from the query design as I created teh query first and then copy it and paste in into the code. Any other ideas?

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    i'm talking about the INSERT INTO statements

    is the saved query at the bottom the one called Reduction Transaction?

  5. #5
    Join Date
    Jun 2008
    Posts
    4
    The INSERT INTO statements works fine as those were tested with a simple recordset() SELECT statement. It's the "SalesMaturity" query that I think is the problem. That's the one at the bottom of the post.

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Not sure if this would cause your problem, but it stikes me as odd... Some of your table/query names are enclodes in [] and some are not, some of your field names are enclosed in [] and some are not.

  7. #7
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    what line is it giving the error on?

    rogue, i think the [] are enclosing those table/field names with spaces in them (which I personally try to avoid)

  8. #8
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    That's why it looked strange.

    The other thing that caught my eye is DoCmd.RunSQL (mySql).

    Have you tried DoCmd.RunSQL mySql?

  9. #9
    Join Date
    Jun 2008
    Posts
    4
    I have found a workaround. I removed the criteria "[Forms[.[Seedling Sales]![Description]" from the "SalesMaturity" query and then use this query to feed another query "Sales". "Sales" will now have individual fields with no calculations. I then used the SELECT statement in my code to create the recordset() filtering the records from "Sales" with the WHERE " & [Forms].[Seedling Sales]![Description]". This works. I know the this is not the most efficient way so if anyone can still decifer the problem I'd appreciate it. Thanks for all you responses.

  10. #10
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    if you can tell us what line it was having the problem on, it will make it much easier to solve, as we don't know anything about your tables, field names, relationships, etc.

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The easiest way to test SQL statements (in my opinion) is to put a break at the RunSQL line then run the code. When the code breaks, in the Immediate Window type ?mySql, you should get the constructed SQL statement. Then copy that SQL statement, design a new query in SQL view, paste the SQL statement and see if you can run it.

    When you run it you will see the problem. Even switching from SQL view to Design view will usually show you the problem.

Posting Permissions

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