Vb code looping when not expected

[hardyd44]

Can anyone help ?

I have this Code running when a form opens to import and update a table from excel - I will be the first to admit I am no expert but the bits run ok when not put in one script - but it keeps opening excel again and again when put together instead of just once. I "borrowed" bits from other Vb examples and cobbled them together.

I want it just to run once then open the message box
here is the code:

Option Compare Database
Option Explicit

Private Sub Form_Timer()
On Error GoTo Err_Form_Timer


Dim objExcel As Object

' Create a new instance of Excel.
Set objExcel = CreateObject("Excel.Application")

' Show the instance of Excel on the screen.
objExcel.Visible = True

' Open a file
objExcel.Workbooks.Open ("C:\DriverControl\Holiday2006-2007.xls")

DoCmd.RunMacro "delay5Seconds"

DoCmd.DeleteObject acTable, "Imported Holidays"

DoCmd.OpenQuery "Imported Holidays Builder"

' close workbook
objExcel.Quit
Set objExcel = Nothing



MsgBox "Holidays Updated"

DoCmd.OpenForm "MainForm"
DoCmd.Close acForm, Me.Name

Exit_Form_Timer:
' Exit Function

Err_Form_Timer:
' Exit Function


End Sub


Thanks

Dean

 

[seth_belgium]

Why did you put your code in the Timer-event? I'm pretty sure that's causing the loops...

Just place your code into the Form_Load() event of your form and replace the following lines:

"On Error GoTo Err_Form_Timer" with "On Error GoTo Err_Form_Load"
"Exit_Form_Timer:" with "Exit_Form_Load:"
"Err_Form_Timer:" with "Err_Form_Load:"

That way, the code will only run once, when the form opens.

Seth

 

[hardyd44]

Thanks

Thank you Seth

that worked - I copied the on timer from elsewhere and should have checked when added the extra bits


Thanks again

Dean