Error Message: Too few parameters.

[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.

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.

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")

 

[Steve P.]

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

 

[Allen Browne]

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...

 

[Steve P.]

I had to change the .(dots) to !(bangs) but it works.

Thnaks again,
Steve P.