Using event procedures

[Jim0845]

Is it possible to execute more than one action in an event procedure? On exit of a subform control on the main form, I use an event procedure to do a refresh on a different subform on the same main form.

(Forms!PPI_Main_Form!Calc_PPE_Stats_subform.Form.Requery)

I also want to run an update query against another table based on the input into the first subform, and I want to do it when returning to the main form, as soon as the records have been written to the table underlying the subform. I have a macro that runs the update query for me. The refresh and the update work when either of them is attached to the on exit event. I cannot figure out a way to make them run as a part of the same on exit. I have let Access convert the macro to a module, but I still can't find a way to use it. Surely there is a way to do this and I am overlooking it. I can tie the update macro to another control the user should touch but there is an element of uncertainity there, and it just doesn't feel right to do it that way.

One more question. I am returning to Access after about 10 years, and never did a lot with VBA when I was working with it. I was using Access97 at the time. When I started this project I had 2003 on my machine and converted some of my old work to refresh myself. It didn't take me long to install Access97 and go back to the old versions. I am more comfortable now, and will have to eventualy convert the project to 2003 when I turn it over to the users. The question is, is there enough (or any) increased functionality in 2003 for it to be worth getting out of my comfort zone with Access97?

This is a great forum and reading through your answers to the various posts has been a great help to me as I have been grinding through this.

 

[Remou]

The question is, is there enough (or any) increased functionality in 2003 for it to be worth getting out of my comfort zone with Access97?

Yes, there is, and if you can spare a little time to get to know 2003, you will thank yourself. Have a look at the Northwind sample dataabase for 2003.

You can run quite a lot of code in the one event, but you must use code. To do that, you set the event procedure line to [Event Procedure] and click the three little dots to the right of the line. You will get something like:

Private Sub Form_Close()
    Forms!PPI_Main_Form!Calc_PPE_Stats_subform.Form.Requery
    'Or Me.Calc_PPE_Stats_subform.Form.Requery
    DoCmd.OpenQuery "SomeQuery"
    MsgBox "All done. Bye"
End Sub

 

[Jim0845]

Thanks Remou. I will this a shot.

 

[Jim0845]

Thanks again Remou. I added a line to turn off the messages and it does exactly what I need. And I will dig into 2003.

 

[JoeAtWork]

There really isn't much difference in the foundamentals between Access97 and 2003. At least 95% of what I do in 2003 is the same as how I would have done it in 97.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Jim0845]

Thanks Joe. I guess I need to go ahead and make the move though.

 

[Remou]

Here is an article by Allen Browne on some of the changes/difficulties.

http://allenbrowne.com/ser-48.html

A lot of new functions were added in 2000 and also new collections and these continued into 2003. You will find that MSDN articles often do not include '97 in the "applies to" list.

 

[Jim0845]

Thanks for pointing me to the article Remou. After reading it though, I am wondering if I really want to leave Access97 behind. As the users for this particular project don't do anything else with Access I am thinking it may be easier to deal with the dual Office installations.

However, for myself going forward I suppose I need to take the plunge. Does anyone have any thoughts or suggestions on the 2007 version?

I have already installed and then removed the 2007 office suite from my system because I didn't have time to figure out all of the gadgetry and new looks, but if the Access problems in 2000-2003 have been improved it may be worth the effort.

I do recall seeing some postings on that topic and I will look for those, but any suggestions will be appreciated.