none
"Runtime error 3061. Too few parameters. Expected 1" RRS feed

  • Question

  • Please someone tell me what Access has a problem with here:
    -------------------------------------------------------
    Private Sub Form_BeforeInsert(Cancel As Integer)
    'Set SR number
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim filt_ID As Long
    Dim SR As Integer

    filt_ID = Form_frmSales_Orders_Header!txtID

    Me.txtSales_Orders_Header_ID.Value = filt_ID

    Set db = CurrentDb

    'I get the the error: "Runtime error 3061.  Too few parameters. Expected 1"
    Set rs = db.OpenRecordset("SELECT MAX(SR) + 1 AS SR_new FROM qrySales_Orders_Deliveries WHERE qrySales_Orders_Deliveries.Sales_Orders_Header_ID = " & filt_ID & "")
    rs.MoveFirst
    MsgBox IIf(IsNull(rs!SR_new), 1, rs!SR_new)
    Me.txtSR = IIf(IsNull(rs!SR_new), 1, rs!SR_new)

    rs.Clone
    db.Close
    End Sub
    -------------------------------------------------------

    I copied this from another event on another form which fires fine.  I really don't get it.

    Thanks,

    Charles

    Monday, June 25, 2012 3:30 PM

Answers

  • Hi Charles__Toray,

    if qrySales_Orders_Deliveries.Sales_Orders_Header_ID is a number your statement must be as follows

    set rs=db.openrecordset("SELECT MAX(SR) + 1 AS SR_new FROM qrySales_Orders_Deliveries WHERE qrySales_Orders_Deliveries.Sales_Orders_Header_ID = " & filt_ID)

    otherwise if it's text

    set rs=db.openrecordset("SELECT MAX(SR) + 1 AS SR_new FROM qrySales_Orders_Deliveries WHERE qrySales_Orders_Deliveries.Sales_Orders_Header_ID = """ & filt_ID & """")

    in both statement you can add, before the closing parenthisis a comma and the kind of recordset that you wanna open (e.g. , dbopensnapshot or , dbopendynaset etc.)

    HTH Paolo

    • Proposed as answer by Imb-hb Monday, June 25, 2012 7:18 PM
    • Unproposed as answer by Imb-hb Monday, June 25, 2012 7:19 PM
    • Marked as answer by Charles__Toray Monday, July 2, 2012 1:41 PM
    Monday, June 25, 2012 6:28 PM
  • Does qrySales_Orders_Deliveries have a parameter or refer to a control on a form?

    Regards, Hans Vogelaar

    Monday, June 25, 2012 5:27 PM

All replies

  • Does qrySales_Orders_Deliveries have a parameter or refer to a control on a form?

    Regards, Hans Vogelaar

    Monday, June 25, 2012 5:27 PM
  • Hi Charles__Toray,

    if qrySales_Orders_Deliveries.Sales_Orders_Header_ID is a number your statement must be as follows

    set rs=db.openrecordset("SELECT MAX(SR) + 1 AS SR_new FROM qrySales_Orders_Deliveries WHERE qrySales_Orders_Deliveries.Sales_Orders_Header_ID = " & filt_ID)

    otherwise if it's text

    set rs=db.openrecordset("SELECT MAX(SR) + 1 AS SR_new FROM qrySales_Orders_Deliveries WHERE qrySales_Orders_Deliveries.Sales_Orders_Header_ID = """ & filt_ID & """")

    in both statement you can add, before the closing parenthisis a comma and the kind of recordset that you wanna open (e.g. , dbopensnapshot or , dbopendynaset etc.)

    HTH Paolo

    • Proposed as answer by Imb-hb Monday, June 25, 2012 7:18 PM
    • Unproposed as answer by Imb-hb Monday, June 25, 2012 7:19 PM
    • Marked as answer by Charles__Toray Monday, July 2, 2012 1:41 PM
    Monday, June 25, 2012 6:28 PM
  • Hi Paolo,

    Without judging the contents, I hit the "Propose As Answer" button by accident, and for that reason Unproposed it.

    Excuses.

    Imb.

    Monday, June 25, 2012 7:23 PM
  • Dear Hans,

    You are on the right track, qrySales_Orders_Deliveries did contain a parameter.  I don't know why it wasn't being passed to the form as it is actually the same as the parameter is looking for Form_frmSales_Orders_Header!txtID, which is = filt_ID.  And it can find Form_frmSales_Orders_Header!txtID, so why did it have a problem?!  I don't care really.  You might also be wondering why I was filtering Sales_Orders_Header_ID when qrySales_Orders_Deliveries had already been filtered by Sales_Orders_Header_ID , and the reason is because I started out relating things one way and this code was looking for another field in the qrySales_Orders_Deliveries query.  Whey I changed it I didn't think to not bother filtering as the query was already filtered.

    I resolved the issue by just using the underlying SQL from qrySales_Orders_Deliveries and deleting all unnecessary fields to make it shorter:
    Set rs = db.OpenRecordset("SELECT MAX([tblSales_Orders_Deliveries].[SR]) AS SR_new FROM [tblSales_Orders_Deliveries] WHERE [Sales_Orders_Header_ID]=" & filt_ID)

    Thanks again!!

    Charles 

    Tuesday, June 26, 2012 7:55 AM
  • Hi Paolo,

    You and Hans solved it for me!!

    Really appreciate it.  When I get a bit better at this hopefully I can repay you one day and help you overcome something.

    Thanks again.

    Charles
    Tuesday, June 26, 2012 7:56 AM
  • Hi Paolo,

    You and Hans solved it for me!!

    Really appreciate it.  When I get a bit better at this hopefully I can repay you one day and help you overcome something.

    Thanks again.

    Charles

    Hi Charles,

    You marked my reply as an answer, but I do not earn the credits.
    So unmark my reply, and do mark Paolo's reply as an answer.

    Thanks, Imb.

    Tuesday, June 26, 2012 8:38 AM
  • OK, done.
    Monday, July 2, 2012 1:42 PM
  • What could be wrong here:

    ' Copy the latest edit from temp table as "EditFrom".
            sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
                " WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
            db.Execute sSQL

    Monday, July 3, 2017 9:22 AM