Parameter Problems

[Shane via AccessMonster.com]

I'm getting a "Too Few Parameters" Expected 1 error message

Below is the code I'm trying to get to work. If I leave out the db.
OpenRecordset part, it works. If I leave it in I get the error message. I
would appreciate it if someone could throw some suggestions my way.

If Me.chkSectionEquipment = True Then
If Trim(Nz(Me.txtEquip, "")) = "" Then
Eval ("Msgbox('Equipment Name Is Missing@Before I can filter the Orders List
" & _
"you must type an Equipment name!@@',0,'AMC Database Message System')")
Me.txtEquip.SetFocus
Exit Sub
Else
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Dim stS As String
stS = "SELECT * FROM Orders WHERE Orders.Equipment" & _
" LIKE ""*"" & Forms![frmOrdersFilter]![txtEquip] & ""*"""

Set rs = db.OpenRecordset(stS) 'Here is where it highlights for the too few
parameters error

If rs.RecordCount = 0 Then
Eval ("Msgbox('There Are No Matching Records!@There are no matching Orders
for the criteria(s) " & _
"you have entered!@@',0,'No Matching Orders')")
Else
Forms![frmOrdersNavigator]![frmOrderssub].Form.RecordSource = stS
Forms![frmOrdersNavigator]![frmOrderssub].Form.Refresh
DoCmd.Close acForm, "frmOrdersFilter"
Exit Sub
End If
End If
End If

 

[John Nurick]

stS = "SELECT * FROM Orders WHERE Orders.Equipment" & _
" LIKE ""*"" & Forms![frmOrdersFilter]![txtEquip] & ""*"""

You've lost count of the quotes. The statement above leaves stS
containing this string (ignore the line breaks):

SELECT * FROM Orders WHERE Orders.Equipment
LIKE "*" & Forms![frmOrdersFilter]![txtEquip] & "*"

What you need to achieve is

SELECT * FROM Orders WHERE Orders.Equipment
LIKE '*XXXXXXX*';

where XXXXXX is the current value of txtEquip.

Try something like this:

stS = "SELECT * FROM Orders WHERE Orders.Equipment LIKE" _
& "'*" & Forms("frmOrdersFilter").Controls("txtEquip").Value & "';"

Use Me.Controls("txtEquip").Value if this code is running in the form
that contains txtEquip. The short-form ! syntax will work too.

 

[Shane via AccessMonster.com]

Thanks John for your reply. Your code got me past all the error messages,
but it seems to be only doing exact matches. It will give me the message "no
records found" if it's any way but an exact match. Changed just slightly to:

stS = "SELECT * FROM Orders WHERE Orders.Equipment" & _
" LIKE '*" & Forms![frmOrdersFilter]![txtEquip] & "*'"

and all seems to be working well.

Thanks again for you time,
Shane

stS = "SELECT * FROM Orders WHERE Orders.Equipment" & _
" LIKE ""*"" & Forms![frmOrdersFilter]![txtEquip] & ""*"""

You've lost count of the quotes. The statement above leaves stS
containing this string (ignore the line breaks):

SELECT * FROM Orders WHERE Orders.Equipment
LIKE "*" & Forms![frmOrdersFilter]![txtEquip] & "*"

What you need to achieve is

SELECT * FROM Orders WHERE Orders.Equipment
LIKE '*XXXXXXX*';

where XXXXXX is the current value of txtEquip.

Try something like this:

stS = "SELECT * FROM Orders WHERE Orders.Equipment LIKE" _
& "'*" & Forms("frmOrdersFilter").Controls("txtEquip").Value & "';"

Use Me.Controls("txtEquip").Value if this code is running in the form
that contains txtEquip. The short-form ! syntax will work too.