recordset

R

Rocquey Rhodes

hi all,
I've got a problem with a recordset. the sql keeps giving
me error 3071. 'too few parameters. expected 1"
I wrote the query in design view and it works there. I
copies the sql for the recordset and keep getting the
above error. Code as followed. does anyone one see
anything wrong.
thanks in advance.
Rocquey
Private Sub cmdAccept_Click()

Dim dbs As Database
Dim rsf As DAO.Recordset
Dim frmQtyOnHandSF As Control
Set frmQtyOnHandSF = Forms!frmQtyOnHand!frmQtyOnHandSF
Me.frmQtyOnHandSF.Requery

Set dbs = CodeDb()
Set rsf = dbs.OpenRecordset("SELECT [qryIPRDups&Sums].
[HowActive], dbo_IMA.IMA_ItemID AS ItemID, " & _
"dbo_IMA.IMA_ItemName AS ItemName,
dbo_IMA.IMA_OnHandQty, dbo_IMA.IMA_PurInspQty, " & _
"[IMA_AcctValAmt]*[IMA_OnHandQty] AS ExtVal,
dbo_IMA.IMA_AcctValAmt AS AVA, " & _
"dbo_IMA.IMA_ProdFam AS ProdFam " & _
"FROM dbo_IMA LEFT JOIN [qryIPRDups&Sums] ON
dbo_IMA.IMA_ItemID = [qryIPRDups&Sums].CompItemID " & _
"WHERE (((dbo_IMA.IMA_ItemID)=[Forms]!
[frmQtyOnHand]![txtItemID]))", dbOpenDynaset)

If rsf.EOF Then
MsgBox ("No record found")
Else
Me.txtItemID = rsf!ItemId
Me.txtDesc = rsf!ItemName
Me.txtOHQ = rsf!ONHandQty
Me.txtPOI = rsf!IMA_PurInspQty
Me.txtHowAct = rsf!HowActive
Me.txtAVA = rsf!AVA
Me.txtExtval = rsf!ExtVal
Me.txtProdFam = rsf!ProdFam
End If
rsf.Close
dbs.Close
End Sub
 
A

Allen Browne

The problem is with the reference to the form inside the string.

As you say, this works fine if you try it in the query window, because the
Expression Service resolves the reference. But the ES is not available for
OpenRecordset. The solution is to concatenate the value from the form into
the string, i.e.:
"WHERE dbo_IMA.IMA_ItemID = " & [Forms]![frmQtyOnHand]![txtItemID] & ";"

If the dbo_IMA.IMA_ItemID is of type Text (not Number), you need extra
quotes as delimiters:
"WHERE dbo_IMA.IMA_ItemID = """ & [Forms]![frmQtyOnHand]![txtItemID] & """;"
 
R

Rocquey Rhodes

thanks Allen, the triple quotes worked.
-----Original Message-----
The problem is with the reference to the form inside the string.

As you say, this works fine if you try it in the query window, because the
Expression Service resolves the reference. But the ES is not available for
OpenRecordset. The solution is to concatenate the value from the form into
the string, i.e.:
"WHERE dbo_IMA.IMA_ItemID = " & [Forms]![frmQtyOnHand]! [txtItemID] & ";"

If the dbo_IMA.IMA_ItemID is of type Text (not Number), you need extra
quotes as delimiters:
"WHERE dbo_IMA.IMA_ItemID = """ & [Forms]![frmQtyOnHand]! [txtItemID] & """;"

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

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

hi all,
I've got a problem with a recordset. the sql keeps giving
me error 3071. 'too few parameters. expected 1"
I wrote the query in design view and it works there. I
copies the sql for the recordset and keep getting the
above error. Code as followed. does anyone one see
anything wrong.
thanks in advance.
Rocquey
Private Sub cmdAccept_Click()

Dim dbs As Database
Dim rsf As DAO.Recordset
Dim frmQtyOnHandSF As Control
Set frmQtyOnHandSF = Forms!frmQtyOnHand! frmQtyOnHandSF
Me.frmQtyOnHandSF.Requery

Set dbs = CodeDb()
Set rsf = dbs.OpenRecordset("SELECT [qryIPRDups&Sums].
[HowActive], dbo_IMA.IMA_ItemID AS ItemID, " & _
"dbo_IMA.IMA_ItemName AS ItemName,
dbo_IMA.IMA_OnHandQty, dbo_IMA.IMA_PurInspQty, " & _
"[IMA_AcctValAmt]*[IMA_OnHandQty] AS ExtVal,
dbo_IMA.IMA_AcctValAmt AS AVA, " & _
"dbo_IMA.IMA_ProdFam AS ProdFam " & _
"FROM dbo_IMA LEFT JOIN [qryIPRDups&Sums] ON
dbo_IMA.IMA_ItemID = [qryIPRDups&Sums].CompItemID " & _
"WHERE (((dbo_IMA.IMA_ItemID)=[Forms]!
[frmQtyOnHand]![txtItemID]))", dbOpenDynaset)

If rsf.EOF Then
MsgBox ("No record found")
Else
Me.txtItemID = rsf!ItemId
Me.txtDesc = rsf!ItemName
Me.txtOHQ = rsf!ONHandQty
Me.txtPOI = rsf!IMA_PurInspQty
Me.txtHowAct = rsf!HowActive
Me.txtAVA = rsf!AVA
Me.txtExtval = rsf!ExtVal
Me.txtProdFam = rsf!ProdFam
End If
rsf.Close
dbs.Close
End Sub
Click to expand...


.
Click to expand...
 
Top