Parameters in a form not recognized 1

[fryguy5049]

I am receiving the error "Item not found in this collection"
The parameters I am attempting to pass to this code are in 2 text boxes of the form(start date & end date). The form is open when I try to run the module & I am using Access 2000.
The code fails on this line:
qdf.Parameters(1) = Forms!fdr_batch!startdate
When I go into debug mode and hover the cursor over this line it shows the correct parameter from the form (start date). Any ideas why it doesn't work?
Here is the rest of the code:

Sub ExportTextFile()

Dim db As Database, rst As DAO.Recordset
Dim qdf As QueryDef
Dim Directory As String
Dim MyString As String, strSQL As String
Dim prm As DAO.Parameter
Dim rec_cnt As Integer
Set db = CurrentDb





Directory = (Mid(db.Name, 1, Len(db.Name) - Len(Dir(db.Name))))

Open Directory & "\TestOutput.txt" For Output As #1

Set rst = db.OpenRecordset("FDR batch xport")

Set qdf = db.QueryDefs("FDR batch xport")
qdf.Parameters(1) = Forms!fdr_batch!startdate
qdf.Parameters(2) = Forms!fdr_batch!enddate



Print #1, "HDAJFEBB" & Format(Date, "mmddyy") & Format(Time, "hhmmss")

Do While Not rst.EOF
    MyString = rst!CHaccount & rst!merc_num & rst!filler & rst!fee_text
    Print #1, MyString
    rst.MoveNext
    
Loop

Print #1, "TLAJFEBB" & Format(rst.RecordCount + 2, "0000000000") & Format(rst.RecordCount * 15, "000000000000000") & "00"


ExportTextFile_Exit:
' Close text file.
    Close #1
    rst.Close
    Set db = Nothing
    Exit Sub



End Sub

 

[PHV]

Provided the query already referenced the textbox:
Set db = CurrentDB
Set qdf = db.QueryDefs("FDR batch xport")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fryguy5049]

I don't see how that can be incorperated into my code. Should I just add this code? Where at? Sorry I am pretty new to this. I am not sure if the textboxes are already referenced. Thanks 4 your patience

 

[PHV]

So, replace this:
Open Directory & "\TestOutput.txt" For Output As #1
Set rst = db.OpenRecordset("FDR batch xport")
Set qdf = db.QueryDefs("FDR batch xport")
qdf.Parameters(1) = Forms!fdr_batch!startdate
qdf.Parameters(2) = Forms!fdr_batch!enddate
By this:
Set qdf = db.QueryDefs("FDR batch xport")
qdf.Parameters(0) = CDate(Forms!fdr_batch!startdate)
qdf.Parameters(1) = CDate(Forms!fdr_batch!enddate)
Set rst = qdf.OpenRecordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fryguy5049]

Tried that and I get the same error message-"item not found in this collection" Any other things I should check? Thanks!

 

[PHV]

What are the REAL names of the mainform and the textboxes ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fryguy5049]

The name of the form is "fdr_batch". The name of the first text box is "startdate" & the second is "enddate".

 

[PHV]

And what happens if you add this line just below the Set qdf = ... one:
MsgBox qdf.Parameters.Count

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fryguy5049]

message box pops & says 0.

 

[PHV]

So your query is not parametized ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fryguy5049]

Awesome! Too much back and forth testing and i forgot to put the parameter in the query back to :
Between [Forms]![fdr_batch]![startdate] And [Forms]![fdr_batch]![enddate]
Instead of a "hard" parameter. Thanks so much for taking the time troubleshoot w/ me & for the debugging tip.