exit sub not working

[Ant]

Hi

I have the following code with an error handling in one part of the code.
Once the For...Next loop has finished running a macro is called and then
after it should stop (by going into the Exit_Adhoc_Click and then exiting).
However it seems to ignore the Exit_Adhoc_Click and then going into the
Err_Monthly_Click.

I can't seem to work out why this is happening. (I have missed out some of
the code that would be overkill, I don't think it is relevant to the problem

Thanks in advance
Private Sub Monthly_Click()
Dim NumCarr As Integer
Let NumCarr = DCount("*", "Qry_5_1_1_Carrier_Numbered")
Dim Month As Date
Dim monthnum As String
Let Forms![Fm_2_Import_Rates]![Entry_Indicator] = "M"
monthnum = Forms![Fm_2_Import_Rates]![Month_Select]
If monthnum = 0 Then
randvar = MsgBox("Please select a month from the drop down box")
Exit Sub
End If
Month = Format("01/" & Right(monthnum, 2) & "/" & Left(monthnum, 4),
"dd/mm/yy")
For N = 1 To NumCarr
Dim qd As DAO.Querydef
Set qd = currentdb.CreateQuerydef(strName, strSQL)
On Error GoTo Err_Monthly_Click
DoCmd.OpenQuery ("QueryX")
DoCmd.DeleteObject acQuery, "QueryX"
DoCmd.DeleteObject acQuery, "QueryY"
DoCmd.SetWarnings True
Next N
DoCmd.RunMacro "Mc_2_Import_Rates.Duplicates"
DoCmd.SetWarnings True

Exit_Monthly_Click:
Exit Sub

Err_Monthly_Click:
randvar = MsgBox("You have a carrier " & Carr & " in the database and
not in the import file. Either add to the import file or delete from the
database", vbOKOnly)
randvar = MsgBox("No rates were imported")
DoCmd.DeleteObject acQuery, "QueryX"
DoCmd.DeleteObject acQuery, "QueryY"
DoCmd.OpenQuery ("Qry_1_1_1_Delete_Import_Table")
DoCmd.SetWarnings True
Resume Exit_Monthly_Click

End Sub

 

[Dave Allan via AccessMonster.com]

Hi Ant,

I don't know your level of coding ability so i'll just say what i'd do in
this situation, you might already have done this.

It's skipping the Exit_Monthly_Click because it's catching an error. When
you do this, no matter what the error, access will go to the error handling
section and continue running the code. What you wanna do is either comment
out the "On Error Goto" bit and run it again, to see what error it gives
you, or add in the error handling bit "Msgbox(err.number & " " &
err.description)"

This should let you know what the error is, then you can be a step closer
to fixing it.

Hope this helps!

Cheers

Dave

 

[Ant]

Hi Dave

Thanks for your time

I put in the suggested message box and the error I got was "2001 You
cancelled the previous operation".

What I think is happening is that at the end of the macro that I am calling
is a Stopallmacros command. The code is thinking this is the user cancelling
the code and so runs the error handling.

To get round this (as I only want the error handling on for part of the
code) is there a way to turn the on error command off so to speak?

Apologies for my language, fairly new to all this and so I'm probably
causing programmers pain with my solutions!

Cheers

Anthony

 

[Ant]

Dave

Have fixed the problem through the following code although I accept it's a
bit messy, do you know of a neater way?

Thanks

Anthony
Private Sub Monthly_Click()
Dim NumCarr As Integer
Let NumCarr = DCount("*", "Qry_5_1_1_Carrier_Numbered")
Dim Month As Date
Dim monthnum As String
Let Forms![Fm_2_Import_Rates]![Entry_Indicator] = "M"
monthnum = Forms![Fm_2_Import_Rates]![Month_Select]
If monthnum = 0 Then
randvar = MsgBox("Please select a month from the drop down box")
Exit Sub
End If
Month = Format("01/" & Right(monthnum, 2) & "/" & Left(monthnum, 4),
"dd/mm/yy")
For N = 1 To NumCarr
Dim qd As DAO.Querydef
Set qd = currentdb.CreateQuerydef(strName, strSQL)
On Error GoTo Err_Monthly_Click
DoCmd.OpenQuery ("QueryX")
DoCmd.DeleteObject acQuery, "QueryX"
DoCmd.DeleteObject acQuery, "QueryY"
'DoCmd.SetWarnings True
Next N
Carr = "Exit"
DoCmd.RunMacro "Mc_2_Import_Rates.Duplicates"
DoCmd.SetWarnings True

Exit_Monthly_Click:
Exit Sub

Err_Monthly_Click:
If Carr = "exit" Then GoTo Exit_Monthly_Click
randvar = MsgBox("You have a carrier " & Carr & " in the database and
not in the import file. Either add to the import file or delete from the
database", vbOKOnly)
randvar = MsgBox("No rates were imported")
DoCmd.DeleteObject acQuery, "QueryX"
DoCmd.DeleteObject acQuery, "QueryY"
DoCmd.OpenQuery ("Qry_1_1_1_Delete_Import_Table")
DoCmd.SetWarnings True
'End If
Resume Exit_Monthly_Click

End Sub

 

[Dave Allan via AccessMonster.com]

hey Ant,

Sos about the wait. I see what you're doing and yes, very very messy, heh.
What you could do is specify the error number in your On.Error to match the
exact error you are explaining how to fix in:

MsgBox("You have a carrier " & Carr & " in the database and
not in the import file. Either add to the import file or delete from the
database", vbOKOnly)

as in if err.number = 2001 or whatever number is is, then msgbox(blah no
import file) etc

btw - you don't need "randvar = msgbox", just use msgbox on it's own.

In fact, the simplest thing to do is replace:

If Carr = "exit" Then GoTo Exit_Monthly_Click

with something along the lines of

if isnull(err) then exit sub

and totally loose the Exit_Monthly_Click:.

Rule of thumb, you really wanna avoid GoTo at any cost.

Why can't you fix this at source and take out the "stop all macros" thing
in the macro you are calling?

 

[Dave Allan via AccessMonster.com]

actually, your current code wouldn't work surely since the error catch will
ALWAYS have carr = "Exit". It wont go further and give you the helpful
message box about the import files.

 

[Ant]

Hi Dave

Thanks for all your help here, will take it on board for next time.

Only reason I used randvar was I got an error message when trying to use
just msgbox, may be it was linked to something else.

I'm using criteria in my macro so sometimes it has to stop, sometimes it
doesn't. But yes I think the err.number is the best thing to use, thanks.

Also the error catch was for the openquery commands above where I set the
value to exit so would go into to the error handling part only when carrying
out those commands.

Cheers for everything

Ant