Solved Form with filters giving parameter errors (1 Viewer)

BeardedSith

Member
Local time
Today, 08:50
Joined
Feb 5, 2020
Messages
73
I have a couple procedures I use throughout several of my databases to filter a listbox based on the value in a textbox. I stole these ideas from someone, and honestly can't remember who (so if you're here, thanks and sorry!).

I've tried to determine where the error is coming from, but I'm stumped. I could really use a fresh pair of eyes.

Code:
Option Compare Database
Const LIST_SQL As String = "SELECT ID, ItemList FROM qryAuctionList WHERE ItemList LIKE p0 ORDER BY DateListed"
Private m_qdf As DAO.QueryDef
'''''''''''''''''''''''''''''
Private Sub Form_AfterUpdate()
    Call validateSave(Me.Dirty)
End Sub
'''''''''''''''''''''''''''''
Property Get ListQuery() As DAO.QueryDef
If m_qdf Is Nothing Then Set m_qdf = CurrentDb.CreateQueryDef("", LIST_SQL)
    Set ListQuery = m_qdf
End Property
'''''''''''''''''''''''''''''
Private Sub FilterList(Optional criteria As String)
    criteria = "*" & criteria & "*"
    With Me.ListQuery
        .Parameters(0) = criteria
        Set Me.lstAuctions.Recordset = .OpenRecordset
    End With
End Sub
Private Sub cmdClear_Click()
    DoCmd.GoToRecord , "", acNewRec
    txtFilter.Value = ""
    FilterList
End Sub
'''''''''''''''''''''''''''''
Private Sub lstAuctions_DblClick(Cancel As Integer)
    DoCmd.SearchForRecord , "", acFirst, "[ID] = " & Str(Nz(Screen.ActiveControl, 0))
    DoCmd.Requery "lstAuctions"
    
End Sub
'''''''''''''''''''''''''''''
Private Sub Form_Load()

On Error GoTo Form_Load_Err

FilterList
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub
'''''''''''''''''''''''''''''

Private Sub txtFilter_Change()
    FilterList Me.txtFilter.Text
End Sub

The error exists when I 1) Open the form or 2) Double-Click lstAuctions.
Capture.PNG

I've been able to trace it down to this line:
Code:
Private Sub FilterList(Optional criteria As String)
    criteria = "*" & criteria & "*"
    With Me.ListQuery
        .Parameters(0) = criteria
        Set Me.lstAuctions.Recordset = .OpenRecordset 'DEBUG FIRES BACK THE ERORR IS HERE
    End With
End Sub

Having too few parameters seems like an easy one to figure out, but, like eluded to earlier, this is code that I've used in several projects with perfect results, so I'm not entirely sure where I'm going wrong.
 

bob fitz

AWF VIP
Local time
Today, 12:50
Joined
May 23, 2011
Messages
4,717
Is the "Multi Select" property of the listbox set to None
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 28, 2001
Messages
26,996
The issue is that .OpenRecordset is a function to open a recordset and you have not told it either the name or the type of the recordset. That error says it thinks you have not. Or at least that is how I would interpret the behavior you reported.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:50
Joined
Oct 29, 2018
Messages
21,357
Hi. It looks like you're setting one parameter, but the error expects two (2). I would take the SQL statement and paste it in the query designer to get a clue of what is missing.
 

Micron

AWF VIP
Local time
Today, 08:50
Joined
Oct 20, 2018
Messages
3,476
you have not told it either the name or the type of the recordset.
That is not a requirement. If you don't provide a name, the query def is temporary and dies when the code ends. It's a way of creating a temporary one in memory. You can do the same with a table def - a table in memory only. If you don't provide a type, it defaults (dynaset?) so I'd advise the OP to not go down that rabbit hole for this problem. Besides, whoever wrote the code has designed it to work with a temp qdef, and if you alter that it will cascade into more issues.

The message is saying that 2 parameters are expected but I can't recall if it means 2 out of n are not being provided, or 0 out of 2 were provided. The cause could be a lot of things so I'd advise posting a db copy
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:50
Joined
Sep 21, 2011
Messages
14,040
I have just tried your code, amending names to suit of course, and it works fine for me, so thank you for that technique.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:50
Joined
Sep 21, 2011
Messages
14,040
One thing to try as I only found this by a mistake I made myself.
Take your constant sql string and paste it into the query SQl window and run it and see what it prompts for besides p0
 

BeardedSith

Member
Local time
Today, 08:50
Joined
Feb 5, 2020
Messages
73
Well, I'm an idiot. lol...

My query qryAuctionList, I forgot to tick the checkbox to have DateListed displayed. oops.

Pasting the SQL statement into a query editor showed me the "way". Thanks all! And @Gasman - anytime buddy!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:50
Joined
Oct 29, 2018
Messages
21,357
Well, I'm an idiot. lol...

My query qryAuctionList, I forgot to tick the checkbox to have DateListed displayed. oops.

Pasting the SQL statement into a query editor showed me the "way". Thanks all! And @Gasman - anytime buddy!
Hi. Glad to hear you got it sorted out. I hope what I said in post #4 helped. Good luck with your project.
 

Users who are viewing this thread

Top Bottom