Item not found in collection - Error 3265 (1 Viewer)

RickHunter84

Registered User.
Local time
Today, 17:57
Joined
Dec 28, 2019
Messages
85
hello friends,

I need your help to spot an issue that I've tried to figure out for at least 2 hours. Im trying to export a query to a csv file based on a criteria. The code works fine until it reached the parameter to filter the query, see code below:

Code:
Public Function ExportQueryToCSV(searchCriteria As String)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef
    Dim filePath As String
    Dim fileName As String

    Const queryName As String = "DisposetQ"
    
    
    Const exportFolder As String = "C:\Users\Megaman\CSVAccessSearch\"
    
  
    fileName = "PNstoSearch.csv"
    
    ' Combine the folder and file path
    filePath = exportFolder & fileName
    
    ' Open the query
    Set db = CurrentDb
    Set qdf = db.QueryDefs(queryName)
 
  
    qdf.Parameters("[DS]").Value = searchCriteria
    
    Set rst = qdf.OpenRecordset()
    

    If Not rst.EOF Then
        DoCmd.TransferText acExportDelim, , queryName, filePath, True
        MsgBox "Query results exported to " & filePath, vbInformation, "Export Successful"
    Else
        MsgBox "No matching records found.", vbInformation, "Export Aborted"
    End If
    
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Function

The line that is causing the issue is qdf.Parameters("[DS]").Value = searchCriteria - i've checked the Query and the field does exist as DS. Not sure why access is telling me that is not part of the collection.

Any feedback will be greatly appreciated.
thank you in advance.

DS table example.png
 

Josef P.

Well-known member
Local time
Today, 23:57
Joined
Feb 2, 2023
Messages
848
Where is the parameter defined? A field is not a parameter.
 

RickHunter84

Registered User.
Local time
Today, 17:57
Joined
Dec 28, 2019
Messages
85
Where is the parameter defined? A field is not a parameter.
Not sure how to apply that. This is my first time using QueryDef to be honest, i used a template code and slightly adjusted...any feedback on how to define it is greatly appreciated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
43,513
qdf.Parameters("[DS]").Value = searchCriteria
The query doesn't have a parameter. You have to create one first.

Where DS = [EnterDS]

Then the parameter line would be

qdf.Parameters!EnterDS = searcriteria

If you don't use special characters and embedded spaces, you can use the shorthand notation
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
43,513
I updated my post. I hadn't looked at your query first
 

RickHunter84

Registered User.
Local time
Today, 17:57
Joined
Dec 28, 2019
Messages
85
I updated my post. I hadn't looked at your query first
hey Pat,

I adjusted the code, but when i run the code i actually get the prompt from the query to enter the parameter, so searchCriteria is not being picked up by the code. The comment you made regarding the Where DS = [EnterDS] , Do I need to put this in the Query design screen (criteria field)?

Rick
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
43,513
The query doesn't have a parameter. You have to create one first.

Where DS = [EnterDS]

You need to modify the saved querydef to add the argument. I typically make my arguments [EnterSomething] so I always know that this is MY argument and I want the user to enter something. Of course the queries are always run using VBA rather than opened directly unless it is me testing. And in that case, I'm testing the query and am prepared to provide a value for the prompt.

If you want to use the query without the embedded prompt, then the best option is to create a second querydef that opens the first one but adds the argument. That way, if you end up changing the base querydef, your second querydef with the argument also gets changed.
 

MarkK

bit cruncher
Local time
Today, 14:57
Joined
Mar 17, 2004
Messages
8,187
I routinely reference the parameters collection using the index, rather than the name of the parameter, like...
Code:
qdf.Parameters(0) = searchCriteria
Here's a code sample from a system I have open on my desk right now...
Code:
    With CurrentDb.CreateQueryDef("", SQL_UPDATE_SHIPDATE)
        .Parameters(0) = Shipdate
        .Parameters(1) = Split(OrderNumber, "-")(1)
        .Execute dbFailOnError
    End With
You just need to know what order the query expects them in, and the index of the first one is zero.
 

sonic8

AWF VIP
Local time
Today, 23:57
Joined
Oct 27, 2015
Messages
1,000
You just need to know what order the query expects them in, and the index of the first one is zero.
There you stated a strong reason not to do as you just suggested.
I very much prefer named parameters, as they are less prone to hidden bugs. If a parameter name was changed, there will be an visible/catchable error. If just the order of parameters was changed, the code might continue to run but with incorrect results.
 

MarkK

bit cruncher
Local time
Today, 14:57
Joined
Mar 17, 2004
Messages
8,187
I'm not saying don't use named parameters, but in a case like this, which is common in how I code stuff...
Code:
   Const SQL_INSERT_RECORD As String = _
        "INSERT INTO tProperty " & _
            "( ParentType, Name, Description ) " & _
        "VALUES " & _
            "( p0, p1, p2 ) "
    
    With CurrentDb.CreateQueryDef("", SQL_INSERT_RECORD)
        .Parameters(0) = "Database Maintenance"
        .Parameters(1) = Sys.User.FirstName & " executed at " & Now()
        .Parameters(2) = m_msg
        .Execute
        .Close
    End With
... there is no confusion about what is going on. So if referencing the parameter by index works for you, it's an option.
 

Josef P.

Well-known member
Local time
Today, 23:57
Joined
Feb 2, 2023
Messages
848
[OT]
... there is no confusion about what is going on
But you have to look up again when reading the code to know what value needs to go in at .Parameters(1).


[Back to the topic]

When I create a parameter query, I declare the parameters including data type.

Example:
Parameter list in query editor:
ParamQuery.png

SQL:
Code:
PARAMETERS [@N] Long, [@T] Text (255);
SELECT TestTab.id, TestTab.N, TestTab.T
FROM TestTab
WHERE TestTab.N = [@N] AND TestTab.T = [@T];
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Feb 19, 2002
Messages
43,513
I would never used indexed parameters unless I was filling them using a loop. Named parameters are ever so much clearer and as Sonic pointed out, less prone to errors. This is one of those cases of - just because you can do something doesn't mean you should.

Declaring the parameters in the query is also good practice. One which I admit to not following except when forced to by the crosstab query:(
 

Users who are viewing this thread

Top Bottom