Error Number 94

[exaccess]

I am close to the end of a module and this error number 94 starts appearing. It always highlights recordset closing. Here is sample code:

Public Sub ImportSheet()
    On Error GoTo Error_Handler
    Dim rr As Recordset, RT As Recordset, ch As Recordset
    Dim db As Database
    Set db = CurrentDb
    Set rr = db.OpenRecordset("OriginalTbl")
    Set RT = db.OpenRecordset("TargetTbl")
    Set ch = db.OpenRecordset("SearchingTbl")
    ch.MoveFirst
     ......
   Some code
    .......
    ch.close
    set ch = Nothing
     rr.MoveFirst  
    Do While Not rr.EOF
     rt.AddNew
    .............
   Some code
   ..............
    rt.Update
    rr.MoveNext
Loop
    rr.Close
    rt.Close
    Set rr = Nothing
    Set RT = Nothing

I am hitting this error message when I reach rr.Close. I commented this line out. This time it started complaining at rt.Close. Help is highly appreciated. Thanks.

 

[pbaldy]

Any chance the recordsets were closed previously? In my experience the Set = Nothing lines are all that are needed, so you can try dropping those lines.

 

[exaccess]

Any chance the recordsets were closed previously? In my experience the Set = Nothing lines are all that are needed, so you can try dropping those lines.

Only one of the recordsets namely ch is used in another sub but after it is opened and closed here. I have commented the rr and rt.close out, but this time it stops at the nothing statement and gives the same error. In fact after the error just the finishing message is issued and program is completed. But this message has to be handled somehow.

 

[pbaldy]

This is certainly an new one on me. Something odd is going on. I suppose one way to handle it is put those in the exit handler of a normal error handling routine, and precede with

On Error Resume Next

but I'm curious what's causing the error. I don't suppose you can post the db, or a sample that has the same error?

 

[exaccess]

This is certainly an new one on me. Something odd is going on. I suppose one way to handle it is put those in the exit handler of a normal error handling routine, and precede with

On Error Resume Next

but I'm curious what's causing the error. I don't suppose you can post the db, or a sample that has the same error?

I would like to post the program and database. But both are huge and contains a lot of private info. Now I will try to figure out a different algorithm to use. I'll give feedback. Cheers.

 

[exaccess]

I would like to post the program and database. But both are huge and contains a lot of private info. Now I will try to figure out a different algorithm to use. I'll give feedback. Cheers.

I have done as you said and used "On error resume next". Result the sub terminated as foreseen with good results. I could not find the culprit. By the way are there any plugins or addons on the market that help the developer to spot the erroneous statement with its number? Something like that would be quite helpful. Cheers.

 

[pbaldy]

Not sure exactly what you're looking for, but MZ Tools lets you add line numbers to procedures, and pretty sure its error handler can be customized to return the offending line number.

 

[exaccess]

Not sure exactly what you're looking for, but MZ Tools lets you add line numbers to procedures, and pretty sure its error handler can be customized to return the offending line number.

OK Finally I found the culprit. I inserted a test message nearly at every two statements and here it is:

fld = Nz(.Fields(findex(3)).Value, "0,00")

This is a text field that contains a number of the format 00,00. It is always there. But in this record it is null. Well I did not check for null. That was it. After fixing that as above it worked perfectly.