Compile error: Label not defined Access

Question

Hi All,

I am getting the above error message on the following code - I have highlighted the line it goes to. Can anyone advise how this is incorrect?

Private Sub New_Report_Click()

DoCmd.Close

On Error GoTo Err_CmdImportExcel_Click

DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="Report Temp", FileName:="~~FileNameRemoved~~xlsx", HasFieldNames:=True

Exit_CmdImportExcel_Click:

Exit Sub

Err_New_Report_Click:

MsgBox Err.Description

Resume Exit_New_Report_Click

DoCmd.RunMacro "Report"

DoCmd.OpenForm "Report"

Thanks in advance.

P.S. I have removed the filepath for confidentiality reasons but I have checked this and it is correct.

Answer 1

Move the lines

DoCmd.RunMacro "Report"

DoCmd.OpenForm "Report"

to immediately above the line

Exit_New_Report_Click:

Answer 2

The line

On Error GoTo Err_CmdImportExcel_Click

refers to a label Err_CmdImportExcel_Click, but the label in your code is named Err_New_Report_Click. So the above line should be

On Error GoTo Err_New_Report_Click

Also, the line

Exit_CmdImportExcel_Click:

should be

Exit_New_Report_Click:

Please note that the lines

DoCmd.RunMacro "Report"

DoCmd.OpenForm "Report"

will never be executed.

Answer 3

Hi,

Thank you for the help - This has fixed that problem. How do I need to rephrase/change this so that the last two actions are completed?

Thanks,

Briony

Answer 4

No line was highlighted, but I think I see the problem. Your On Error command defines Err_CmdImportExcel_Click as the label to go to when an error occurs. However, the label in you code is Err_New_Report_Click:

So Err_CmdImportExcel_Click is not defined anyplace.