Error Message: Too few parameters.

  • Thread starter Thread starter Tally Ho
  • Start date Start date
T

Tally Ho

Thank You for responding Allen.

Considering how poorly worded my original question was (okay, I dashed it
off five minutes before leaving work) I'm surprised that you could
interpret exactly what I was trying to relay.

Tomorrow, I should be able to plug your answer into my code and make it
work. I'll let you know if it's a success, if not I'll probably be back with
more questions. At any rate, I learned something new today. I'm trying
(slowly) to learn VBA and would say that I am now approching the "dangerous"
stage. Thanks for making the world a safer place for us code manglers.


Allen Browne said:
Your "qryProductSort" probably contains a reference such as:
[Forms].[Form1].[Textbox1]
The Expression Service within Access can resolve that reference when you run
the query directly or use it as the source for a form or report, but it does
not work when you try to OpenRecordset in code.

You can assign a value to the parameter before you OpenRecordset, and that
assignment can come from the value of the text box on the form. This kind of
thing:
Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryProductSort")
qdf.Parameters("[Forms].[Form1].[Textbox1]") = Forms.Form1.Textbox1
Set rst = qdf.OpenRecordset...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Steve P. said:
Table CATEGORY
id (pk)
CategoryName
etc...

Table PRODUCTS
id (pk)
cid (fk to CATEGORY id)
seqnum
etc...

I have frmCategory with sfmProduct. On frmCategory I have a button that I
want to increment have increment the seqnum field for any products that
are
relatred to the current category. When I run the following code I get an
error message "Too few parameters. Expected 1"

It looks like this is happening at:
Set rstProductSort = db.OpenRecordset("qryProductSort")

After the code fails I am left with a query "qryProductSort" that does
select the proper records. Your help is appreciated.

Steve P.

Code............

Dim lngFirstIncrement As Long, lngCurrentIncrement As Long
Dim strSQL As String

lngFirstIncrement = 10
lngCurrentIncrement = lngFirstIncrement

Set db = CurrentDb
Set rstProductSort = db.OpenRecordset("qryProductSort")
Click to expand...
Click to expand...
 
Table CATEGORY
id (pk)
CategoryName
etc...

Table PRODUCTS
id (pk)
cid (fk to CATEGORY id)
seqnum
etc...

I have frmCategory with sfmProduct. On frmCategory I have a button that I
want to increment have increment the seqnum field for any products that are
relatred to the current category. When I run the following code I get an
error message "Too few parameters. Expected 1"

It looks like this is happening at:
Set rstProductSort = db.OpenRecordset("qryProductSort")

After the code fails I am left with a query "qryProductSort" that does
select the proper records. Your help is appreciated.

Steve P.

Code............

Dim lngFirstIncrement As Long, lngCurrentIncrement As Long
Dim strSQL As String

lngFirstIncrement = 10
lngCurrentIncrement = lngFirstIncrement

Set db = CurrentDb
Set rstProductSort = db.OpenRecordset("qryProductSort")

rstProductSort.MoveFirst

With rstProductSort
Do While Not rstProductSort.EOF
.Edit
!seqnum = lngCurrentIncrement
.Update
.MoveNext
lngCurrentIncrement = lngCurrentIncrement + lngFirstIncrement
Loop
End With

Me.Requery

ProductSort_Exit:
rstProductSort.Close
Set db = Nothing
Exit Sub

ProductSort_Err:
'if the query is missing recreate it
If Err.Number = 3078 Then
strSQL = "SELECT PRODUCTS.seqnum, PRODUCTS.gid " _
& "FROM PRODUCTS " _
& "WHERE (((PRODUCTS.cid)= [Forms]![frmCategory]![id])) " _
& "ORDER BY PRODUCTS.seqnum;"


Set qdf = db.CreateQueryDef("qryProductSort", strSQL)
Set qdf = Nothing
Resume
Else
MsgBox Err.Description
Resume ProductSort_Exit
End If
End Sub
 
Your "qryProductSort" probably contains a reference such as:
[Forms].[Form1].[Textbox1]
The Expression Service within Access can resolve that reference when you run
the query directly or use it as the source for a form or report, but it does
not work when you try to OpenRecordset in code.

You can assign a value to the parameter before you OpenRecordset, and that
assignment can come from the value of the text box on the form. This kind of
thing:
Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryProductSort")
qdf.Parameters("[Forms].[Form1].[Textbox1]") = Forms.Form1.Textbox1
Set rst = qdf.OpenRecordset...
 
Back
Top