Parameter Problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shane - 10 Jul 2005 04:57 GMT

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

Signature

Thanks,
Shane

Reply to this Message

John Nurick - 10 Jul 2005 07:04 GMT

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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Shane - 10 Jul 2005 13:06 GMT

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] & ""*"""
[quoted text clipped - 25 lines]
>
>Please respond in the newgroup and not by email.

Signature

Thanks,
Shane

Reply to this Message