File not found error 2

[WyldeCarde]

In this snippet of my code:

Function Filter_Proc2()

Dim isFound, TextLine1
Dim rst_read As Recordset
Dim rst_write As Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set rstread = dbs.OpenRecordset("Stored_Proc")
Set rstwrite = dbs.OpenRecordset("Filter_Proc")

rstread.MoveFirst
While Not rstread.EOF

Open rstread.Function_name For Input As #1

I am getting a file not found error on the last line.

I don't really get it, the recordset name is correct as is the field name, in fact I copied and pasted the lines from another piece of script, just changing the names of the field and variables.

I'm hoping I'm just going slightly blind and have made a silly typo. I work in a department by myself however so I can't get someone to cast their eyes over it.

The entirety of the code follows but I'm sure it must be the first bit that's at fault.

Option Compare Database

Function Filter_Proc2()

Dim isFound, TextLine1
Dim rst_read As Recordset
Dim rst_write As Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set rstread = dbs.OpenRecordset("Stored_Proc")
Set rstwrite = dbs.OpenRecordset("Filter_Proc")

rstread.MoveFirst
While Not rstread.EOF

Open rstread.Function_name For Input As #1

Do While Not EOF(1)
        
        Line Input #1, TextLine1
            isFound = InStr(1, TextLine1, "Exec", 1)
        'If found go to the output process
        If isFound > 0 Then GoTo found2
        'If not found go to the end of the loop
        GoTo Done_all2
found2:
            Debug.Print "Searching Procedure - "; rstread!Function_name
            varReturn = SysCmd(acSysCmdSetStatus, "Searching Procedure - " & rstread!Function_name)
            I = InStr(TextLine1, "Exec")
            'If not found go to end
            If I = 0 Then GoTo Done_all2
                    raw_name = TextLine1
                    'If found, crop the data outside of the quotes
                    'raw_start = Mid(TextLine1, I + 1)
                    'I = InStr(raw_start, Chr(34))
                    'If I = 0 Then GoTo Done_all2
                    'raw_name = Mid(raw_start, 1, I - 1)
            'Write found data to "Stored_proc" table
            rstwrite.AddNew
            rstwrite!Source_Proc = rstread!Function_name
            Debug.Print "Found Stored Procedure - "; raw_name
            varReturn = SysCmd(acSysCmdSetStatus, "Found Stored Procedure - " & raw_name)
            rstwrite!Function_name = raw_name
            rstwrite.Update
        
Done_all2:
    Loop
    'Close recordset
    Close #1
    ' Move to next path in the "File_Paths" table
    rstread.MoveNext
    'Forms!Progress.Refresh
    'OpenForms = DoEvents
Wend



End Function

 

[ClulessChris]

You say that the last line throws the error

Open rstread.Function_name For Input As #1

try changeing this to

Open "C:/temp/test.txt" For Input As #1

do you still get the error?

Everybody is somebodys Nutter.

 

[Remou]

What is the content of Function_name ?

 

[randy700]

Dim [COLOR=red yellow]rst_read[/color] As Recordset
Dim [COLOR=red yellow]rst_write[/color] As Recordset
Dim dbs As Database

Set dbs = CurrentDb
Set [COLOR=red yellow]rstread[/color] = dbs.OpenRecordset("Stored_Proc")
Set [COLOR=red yellow]rstwrite[/color] = dbs.OpenRecordset("Filter_Proc")

Compare the highlighted fields.



Randy

 

[PHV]

Replace this (Tip: use the Option Explicit instruction):
Dim rst_read As Recordset
Dim rst_write As Recordset
with this:
Dim rstread As DAO.Recordset
Dim rstwrite As DAO.Recordset

Replace this:
Open rstread.Function_name For Input As #1
with this:
Open rstread!Function_name For Input As #1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WyldeCarde]

Chris:

No, although obviously it errors soon after.

Remou:

It's just a text field, very dull

Randy:

That is an exceptionally good point, unfortunately correcting it made no difference at all :S and bizarrely it's spelt wrong in the function which does work too.

PHV:

I did as suggested, it still errors in the same place with the same error. the only difference is if I hover my cursor over rstread!Function_name I can see that the first line has been read into it (the first peice of datd in that field being "AircraftCount").

Why would you expect declaring and setting them in this way would make a difference? Or is it just faster/better practice?

 

[PHV]

Do you really have a file named AircraftCount in the actual cuurent directory ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

As far as I recall, you need a suitable extension for the file name, unless you have tampered with the registry.

 

[WyldeCarde]

no? It's the first entry in the "function_name" field in the table "Stored_Proc

 

[WyldeCarde]

hmm OMG, I've made a total pigs ear of this hahaha I think I've had too much coffee, I'm gonna go boil my head now.

Errm yeah I was reading in the file strings I want to search for in the files as the PATH I want to search. sorry to waste your time, I may come back to you when I've made a complete mess of this in a more logical fashion

*cough*

sorry

 

[ClulessChris]

Why would you expect declaring and setting them in this way would make a difference? Or is it just faster/better practice?

Yes, Yes, Yes always use Option Explicit

Everybody is somebodys Nutter.