Hi,

I am getting increasingly irritated by Access 2007.

My background is in Excel VBA, so I'm not terribly familiar with Access, but in the short time I've been using it, it's clear there are fundamental differences in behaviour of forms. Some of the things I'm experiencing are:

1. If I change a form dynamically during runtime (e.g. hide/reposition controls), these changes propagate through to the designer once execution has finished and the form has *seemingly* unloaded from memory. What's that all about!?

2. If I open a form in code, which has been set to modal, pop up = yes and acDialog passed to DoCmd.OpenForm, I would expect code execution to halt at the line which called DoCmd.Open form. This is NOT happening.

Can anyone help??

Kind regards,

Keith

I am not sure how you are changing controls in runtime in a way that should only be available to the designer. Can you explain what that means?

As to item 2, when you run a sub on an event, the entire sub runs, not down to any particular line in the sub, so you have to parse out your code based on form events. Don't expect execution to halt during a sub unless there is an error. This is normal behavior to Access developers. VBA is not like VB.NET in that regard.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

For Item 1, are you saying that the form changes during execution and then you want it to go back to a default state after it closes and is reopened?

Well, I would make sure that form changes are done at two places: After_Update & On_Current events. The After_Update will alter the form based on your new control value and the On_Current will move stuff around based on their stored values as you scroll. When you leave and come back, the On_Current will once again move things to where they should be for that stored value.

Thanks for the replies!

I think my problems boil down to simply not knowing Access. Access forms and controls are clearly in a different league and work differently to forms in Excel. In Access, I guess forms are just another way for users to interact directly with the data in the database, and so they are geared for that purpose. In Excel, UserForms can have a multitude of purposes.

The situation that led to both my problems is a technique that I've used successfully in Excel, whereby I create a public method withinthe form's module. This method, which is called from outside code, 'sets up' the form relevant to the purpose it is to be used (dictated by the parameters passed into it), before the form is actually shown. I know that such code could just as easily be placed in the On_Load event (Initialize event in Excel) of the form, but I have my reasons.

In Excel, when this method is called from outside code, the form will initialize into memory automatically. Any subsequent changes to the form then only apply to that form instance. This method also shows the form once it has been set up. Code execution breaks at this line if the form is model, and resumes when it is hidden or unloaded.

In Access (from what I can gather), the form does not load into memory when this public method is called from outside. It seems you need to explicitly load the form using DoCmd.Open...etc. Changes made to the form's controls were thus actually changing the form in the design view, leading to my first observation in my original post.

In Excel, there needs to be an instance of a form in memory before it can be manipulated, otherwise you get a runtime error. With Access, this doesn't seem to be the case with forms, as I can seemingly alter the form in code, while it's in design view, or so it seems. Can anyone explain and put me right on this?

With regard to code execution not halting at the line which opens a modal form, I've since discovered this does happen in Access. The fact I was calling DoCmd.Open from within the form in this public method was probably something to do with it.

This has probably made no sense at all, but it's a bit clearer in my head why I was getting those *issues*, even if I don't fully understand why. If anyone can give me any further insight into the differences between Excel (what I'm used to) and Access forms, I'd be most grateful.

Thanks for the help!

The particular form in question was not bound to any table or query, and was not meant as a means of adding, viewing or altering data in the database. Therefore, I think the events you mention would not have been relevant. It was simply a form telling the user *something* had happened and giving him/her a list of options. It's the *something* which dictated the layout of the form. Since a majority of the form was relevant no matter what that *something* was, it made sense to re-use it and tailor parts of it relevant to the purpose, hence why I created this public Setup method in the form's module.