Open a form and run a module at the same time

[mlai08]

I have a module with a public Sub routine to export data from a query to Excel. Owing to the codes requried to set up the resulting spreadsheet for printing, the exporting process takes about 15 seconds. I intend to run a pop up form while the process is running, alerting users to wait and the form to close when the process is completed. Message on the status bar is not obvious enough to our users, some of which are not very computer literate. Adding a alert message box will do but I see it as a secondary option.

When users click a button on the main menu, I want to open a pop up form and at the same time run the routine to export data to Excel. Since I have to pass a variable from the main menu to the Sub routine, I can either open the form but not able to trigger the Sub or start the Sub without opening the form, depending on how I manipulate the click button action.

1) Open a pop up form - Use docmd command to open the form and pass varible via OpenArgs. In the form load event of the pop up form, retreive the OpenArgs value and pass it to the calling Sub. But it only opens the pop up without running the Sub.

2) Call the Sub directly but include open form command in the Sub procedures. In this case, codes run to build the Excel spreadsheet without opening the pop up form.

I tried to search the forum but did not seem to find anything close to my need. I wonder if any expertise out there can help.

Thanks

 

[DCrake]

From what I gather you want a form to open that states something along the lines

Exporting data, please wait.....

Then when the export has been completed close the form.

If I am right then you need to apply a timer interval to the form, say 1000 (1 second). In the Form Timer() sub you need the following

Me.TimerInterval = 0
  Call YourExportRoutine
  DoEvents
  Msgbox "Export Completed. Click Ok to contrinue.",vbInformation,vbOKOnly,"Process Completed"
   Docmd.Close

This will allow the form to open for 1 second before running the export procedure. The DoEvents waits for it to be completed before issuing the msgbox

By setting the timer interval to zero in the first line will stop it from repeating itself every second.

David

 

[mlai08]

Hi DCrake,

Thank you for your suggestion. This is an alternative to my problem but actually I want the pop up form to stay open until the exporting routine is completed.

The challenge is when the form is opened, Access shifts focus to the form without running the routine. I tried to call the routine in form load or form current event. Both cases don't work.

 

[DCrake]

I fyou follow the suggestions I made the form will open THEN after one second it will call the export routine. The form will remain open suring this process and when it has finished the message box will appear.

Have you set your Form Timer event correctly?

David

 

[mlai08]

Where should I put the codes you suggested? I put them in the form load event and it does not work, got an invalid procedure call or argument.

 

[DCrake]

Go to the properties of the form and select the On Timer Event

Private Sub Form_Timer()
   Me.TimerInterval = 0
   Call [B]YourExportRoutine[/B]
   DoEvents
   MsgBox "Export Completed. Click Ok to continue.",vbInformation+vbOkOnky,"Completed"
   DoCmd.Close


End Sub

Then on tine Timer Interval event type in 1000

Do not have anything on the lo load or on current.

Remember to change the YourExportRoutine to match your name.

David

 

[mlai08]

The form does open and the procedure was run properly although I still got an error on invalid procedure call or argument at the Msgbox line. Any way, I don't really need that msg since user knows the process is completed when the Excel spreadsheet pop up.

Anyway, thanks a lot for your help!

I am a happier man now.