Run-time 3061 error ‘too few parameters'

[steve21nj]

I’m doing some work on my day off and I’m having some problems with transferring data from one form to another. Currently I am getting a run-time 3061 error ‘too few parameters. Expected 1.’

I’ve had the help from mr boblarson prior in the development of this code and looking to take it a step futher.

The first form that displays the data is called ‘ReviewPurchaseOrder’ and has a command button that opens another form with the following code:

Option Compare Database
Private Sub btnDHS_Click()
 Dim stDocName As String
        stDocName = "DHS1501"
        DoCmd.OpenForm stDocName
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("SELECT * FROM tblPurchaseOrderDetails WHERE RequisitionID=" & RequisitionID)
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
     Forms!DHS1501.Controls(i & "txtItem1") = rst("ItemDescription")
     Forms!DHS1501.Controls(i & "txtItem2") = rst("StockNum")
     Forms!DHS1501.Controls(i & "txtItem3") = rst("Quantity")
     Forms!DHS1501.Controls(i & "txtItem4") = rst("UnitofIssue")
     Forms!DHS1501.Controls(i & "txtItem5") = rst("UnitPrice")
     Forms!DHS1501.Controls(i & "txtItem6") = rst("Quantity") * rst("UnitPrice")
    i = i + 1
   rst.MoveNext
   Loop
End If
 
rst.Close
Set rst = Nothing
End Sub

I then get the above error message. I am trying to take the current record of the ‘ReviewPurchaseOrder’ form and move to a new custom form to display the data. This form has linked criteria from what the user selects on the previous form. Currently the form pulls ALL the data from the table, not just the current record. The form also displays an error after 11 entries have been made because the ‘Do Until rst.EOF’ continues past my 10th ItemDescription.

Anyone have any suggestions on what I could do or where I could read?

Thank you.

 

[dansc0tt]

In

WHERE RequisitionID=" & RequisitionID

what are you referencing as the criteria 'RequisitionID'?

Should this not be a value from the ‘ReviewPurchaseOrder’ form? If so I think you'd need to reference the control that holds the value of the PO that you are trying to get more detail for - forms!reviewpurchaseorder.NameOfControl

 

[steve21nj]

I ended up changing the following code, which works fine for the subform:

Set rst = db.OpenRecordset("SELECT * FROM tblPurchaseOrderDetails WHERE fk_RequisitionID=" & RequisitionID)

but it displays the first record from the table, not the current.
If I want to link to the current record where RequisitionNumber = RequisitionNumber, how would I go about putting that in the code and where?

"[RequisitionNumber]=" & "'" & Me![RequisitionNumber] & "'"

Thank you

 

[steve21nj]

I was working with this, but kept getting run-time 3075 ' syntax error in string in query expression.

DoCmd.OpenForm stDocName, , , "[RequisitionNumber]=""'" & Me![RequisitionNumber] & "'"

 

[steve21nj]

I think I have it... another few hours of testing and slamming my head against the desk will see if it still works...

Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "DHS1501"
    stLinkCriteria = "[RequisitionNumber]=" & "'" & Me![RequisitionNumber] & "'"
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
        
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

 

[pr2-eugin]

Is RequisitionNumber a TEXT? If so.. replace the code as,.,

DoCmd.OpenForm stDocName, , , "[RequisitionNumber]='" & Me![RequisitionNumber] & "'"

If it is a Number..

DoCmd.OpenForm stDocName, , , "[RequisitionNumber]=" & Me![RequisitionNumber]

 

[steve21nj]

Thank you for the reply. It works fine.
One issue that I have been unable to figure out is when my subform goes to an 11th line, which is fine for reporting numbers. But... my custom form is only for 10 lines, which is also fine. The user could but 20 lines in the previous form, but only 10 need to show on the audit form. I get a run-time error '2465 ms acces can't find the field '11txtitem1' because there is no such thing. I am trying to figure out how to stop the loop before going to 11txtitem1. Any suggestions?

Set rst = db.OpenRecordset("SELECT * FROM tblPurchaseOrderDetails WHERE fk_RequisitionID=" & RequisitionID)
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
     Forms!DHS1501.Controls(i & "txtItem1") = rst("ItemDescription")
     Forms!DHS1501.Controls(i & "txtItem2") = rst("StockNum")
     Forms!DHS1501.Controls(i & "txtItem3") = rst("Quantity")
     Forms!DHS1501.Controls(i & "txtItem4") = rst("UnitofIssue")
     Forms!DHS1501.Controls(i & "txtItem5") = rst("UnitPrice")
     Forms!DHS1501.Controls(i & "txtItem6") = rst("Quantity") * rst("UnitPrice")
    i = i + 1
   rst.MoveNext
   Loop
End If

 

[pr2-eugin]

Try..

Set rst = db.OpenRecordset("SELECT * FROM tblPurchaseOrderDetails WHERE fk_RequisitionID=" & RequisitionID)
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
     If i>=11 Then Exit Do
     Forms!DHS1501.Controls(i & "txtItem1") = rst("ItemDescription")
     Forms!DHS1501.Controls(i & "txtItem2") = rst("StockNum")
     Forms!DHS1501.Controls(i & "txtItem3") = rst("Quantity")
     Forms!DHS1501.Controls(i & "txtItem4") = rst("UnitofIssue")
     Forms!DHS1501.Controls(i & "txtItem5") = rst("UnitPrice")
     Forms!DHS1501.Controls(i & "txtItem6") = rst("Quantity") * rst("UnitPrice")
    i = i + 1
   rst.MoveNext
   Loop
End If

 

[steve21nj]

I had something similar but without the equals and to 11. Yours works great. Thank you!

Do you know how to reference a subforms footer on the previous form?
On the main form, 'ReviewPurchaseOrder' with subform 'sfrmPurchaseOrderDetails i can calculate the total by using:

[COLOR=black][FONT=Verdana]=[RequisitionSubForm].[Form]![txtSubTotal][/FONT][/COLOR]

That works fine for the first form, but when I open my DHS form, using the code from my very first post and the same line from above, I get the #Name?

I’ve also tried using

[COLOR=black][FONT=Verdana]=[Forms]![Me]![sfrmPurchaseOrderDetails]![txtSubTotal][/FONT][/COLOR]

I put the ‘Me’ there because I have three forms similar to ‘ReviewPurchaseOrder’ that all have the same subform, that all pass data to the DHS form.