Strange behavior

[NauticalGent]

Good evening AWF,

I was looking for a solution to remove duplicate records from a temp table prior to appending/updating the master table.

I found this solution here:

http://mobile.databasejournal.com/f...lete-Duplicate-Records-From-Access-Tables.htm

To test this, I loaded the table with some known duplicates and called the sub from the Immediate Window. It worked like a charm and I was pleased as punch.

I made the sub public and placed the "call code" within the forms module. I did not get the results I was hoping for and started trouble-shooting. Couldn't see anything obvious so I did what I should have done to begin with and removed the On Error Resume Next and put in an error handler, complete with a Msgbox with the error description. Complied and ran the code again...

I got a Msg box with no error, clicked "ok" and then checked the results...which were correct. In other words, the code deleted the dup records in spite of triggering the error handler. Even the Debug.Print was empty. Strange...

I loaded the table again with the control data, commented out the Msgbox portion of the error handler and ran the code again...and got the results I was looking for.

I am using Access 2010, windows 7. I guess I should just be happy that the code is working, but I am curious as to why this is behaving the way it is.

Anyone have any similar experiences like this, or an idea why it is throwing blank errors?

Thanks in advance!

 

[MarkK]

I'm not sure if this applies to the problem you are describing, but VBA does not--by default--break for errors in a class module, and a form is a class module.

In a code window goto MainMenu->Tools->Options->General Tab->Error Trapping Section, and notice the radio button labelled "Break In Class Module." By default, VBA does not break for errors in a class module, and this can be confusing. VBA will break on the line that calls the class module code, so if FormA calls a method on FormB and an error occurs in the FormB method, VBA will break at the line in FormA that calls the FormB subroutine.

If you want to debug class modules, make sure the "Break In Class Module" radio button is set, but, like I say, I'm not sure that this is what you are running into here, but thought it was worth a mention.

 

[NauticalGent]

Thanks MarkK, it will have to wait until I get to work, but it will be the first thing I do after I get my first cup of coffee... I will let you know!

 

[pbaldy]

Another possibility is that your code is structured in such a way as to cause the problem. Can you post the code here?

 

[NauticalGent]

Another possibility is that your code is structured in such a way as to cause the problem. Can you post the code here?

I pretty much used the code in the link verbatim, with the exception of the added error handler. I am on my IPad now, when I get into work, I will post the exact code.

 

[NauticalGent]

Ok, café consumed, here is what I am done:

MarkK, the "Break in Class Module" was NOT checked; the "Break on Unhandled Errors" was. I changed it to Break in Class Module, uncommented the "MsgBox Err.Description" bit of code, saved and compiled. Ran the code and got the same empty/blank MsgBox.

Paul, here is the code:

Public Sub DeleteDupImports()
On Error GoTo err_handler
[INDENT]Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strDupName As String
Dim strSaveName As String
 
Set db = Current()
Set rst = db.OpenRecordset("qryFindImportDuplicates")
 
If rst.BOF And rst.EOF Then 'interesting 
     Exit Sub
Else
     rst.MoveFirst
     Do Until rst.EOF
          strDupName = rst.Fields(0) & rst.Fields(1)
          If strDupName = strSaveName then
               rst.Delete
          Else
               strSaveName = rst.Fields(0) & rst.Fields(1)
          End If
          rst.MoveNext
     Loop
     
     Set rst = Nothing
     Set db = Nothing
End If
[/INDENT]err_handler:
     Debug.Print Err.Description
     MsgBox Err.Description
 
End Sub

As I have said earlier, if I comment out the MsgBox part, it runs perfectly and all is well. I only ask because this is intriguing.

One observation I made while I was retyping this...there is no Update before the loop. Any issues there?

 

[The_Doc_Man]

Some stray thoughts come to mind.

#1 - It is possible to explicitly signal an error by number. Does ANY of your code involve the Signal method? In which case it is signaling 0, usually meaning "no error." An explicit call to the signaller will generate a trap that would be intercepted by an ON ERROR handler because technically it ISN'T an error handler; it is a trap handler.

#2 - It is possible for a function written in C or C++ (for a UNIX environment) to return an error code of 0 as a success status. Do you have a non-standard .DLL reference? I.e. something other than the usual references made from the Windows /system/ or /system32/ folder or the /officenn/ folder?

 

[pbaldy]

As I suspected, the code is structured so that it will always hit the error handler. You need the exit handler:

http://www.baldyweb.com/ErrorTrap.htm

 

[The_Doc_Man]

Good catch, Paul.

Gent ... add an Exit Sub before the label that begins your trap code.

 

[NauticalGent]

As I suspected, the code is structured so that it will always hit the error handler. You need the exit handler:

Thanks Paul and Doc I will put this in as soon as I get back to work tomorrow.

So if I am understanding this correctly, the code isn't necessarily flawed, it is just incomplete.

Thanks to all who weighed in here, I learned a couple of things including when to tell access to break on code, what a class module is and to ALWAYS include an Exit handler with your Error handler.

One would like to think I would know this at this point in time. As a side note, the code has a unique method (to me anyways) to check for an empty record set. I normally use a query to get the recordcount. Interesting...

Excellent work guys; this is precisely why I am a Gold Supporter. I can't afford for this site to cease to exist!

 

[pbaldy]

Yes, the code was fine, you just needed to include a way to exit if there wasn't an error. I use the exit handler and put the cleanup code in there so it always runs.

 

[The_Doc_Man]

Yes... if the .EOF (End of File) and .BOF (Beginning of file) marker are set simultaneously, it is a sign of an empty recordset (because if it were a 1-record set, .EOF wouldn't be set until the first .MoveNext).

Reminds me of some southern Louisiana towns with "Entering LittleBurg" and "Leaving LittleBurg" on opposite sides of the same signpost.

 

[pbaldy]

I don't think I've ever tested for both to see if a recordset didn't return any records. I'd have

If rs.EOF Then

 

[The_Doc_Man]

You know where I found out about that, Paul? This forum - about six or seven years ago, at least.