SQL to open a recordset (1 Viewer)

TanMan · Jun 19, 2012

I am trying to open a recordeset with the following SQL statement in VBA
Getting the following Error message: (Too few parameters.Expected 1), and am not sure where the problem is.
Set rs = dbinventory.OpenRecordset("SELECT [inv_item],[inv_In_Date] FROM qryInv WHERE [inv_item] = strInv_Item ORDER BY [inv_in_Date]")

jdraw · Jun 19, 2012

Show us the rest of the vba involved?

If strInv_Item is a control on a form, then you will have to adjust the sql.

TanMan · Jun 19, 2012

Public Sub InOut()
Dim dbinventory As DAO.Database
Dim rs As DAO.Recordset
Dim ingInv_out As Long
Dim sql As String
Dim strName As String
Dim strDate As Date
Dim strInv_Item As String
strInv_Item = Forms!frmData.Inv_Item
Set dbinventory = CurrentDb
Set rs = dbinventory.OpenRecordset("SELECT [inv_item],[inv_In_Date] FROM qryInv WHERE [inv_item] = strInv_Item ORDER BY [inv_in_Date]")
rs.MoveFirst
Do Until rs.EOF
strName = rs![Inv_Item]
strDate = rs!inv_in_Date
rs.MoveNext
Loop
rs.Close
dbinventory.Close
Set rs = Nothing
Set dbinventory = Nothing
End Sub

TanMan · Jun 19, 2012

This should be celar the the post before

Public Sub InOut()

Dim dbinventory As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim strName As String
Dim strDate As Date
Dim strInv_Item As String

strInv_Item = Forms!frmData.Inv_Item

Set dbinventory = CurrentDb

Set rs = dbinventory.OpenRecordset("SELECT [inv_item]," & _
"[inv_In_Date] FROM qryInv WHERE [inv_item] = " & _
"strInv_Item ORDER BY [inv_in_Date]")

rs.MoveFirst
Do Until rs.EOF
strName = rs![Inv_Item]
Debug.Print strName
strDate = rs!Inv_In_date
Debug.Print Inv_In_date
rs.MoveNext
Loop

rs.Close
dbinventory.Close

Set rs = Nothing
Set dbinventory = Nothing

End Sub

pbaldy · Jun 19, 2012

Easier to figure out on one line, they you can work with putting it on different lines. If inv_item has a numeric data type:

Code:

Set rs = dbinventory.OpenRecordset("SELECT [inv_item],[inv_In_Date] FROM qryInv WHERE [inv_item] = " & strInv_Item & " ORDER BY [inv_in_Date]")

TanMan · Jun 19, 2012

Paul
Inv_item is not Numeric, it is TXT

pbaldy · Jun 19, 2012

Then try

Set rs = dbinventory.OpenRecordset("SELECT [inv_item],[inv_In_Date] FROM qryInv WHERE [inv_item] = '" & strInv_Item & "' ORDER BY [inv_in_Date]")

TanMan · Jun 19, 2012

thanks Paul

pbaldy · Jun 19, 2012

No problem.

SQL to open a recordset (1 Viewer)

TanMan

Registered User.

jdraw

Super Moderator

TanMan

Registered User.

TanMan

Registered User.

pbaldy

Wino Moderator

TanMan

Registered User.

pbaldy

Wino Moderator

TanMan

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread