Split Form does not hold Module Variables in Access 2007!!!!

[geosmy]

Hi everyone,

First post here!

I have been trying to upgrade to Acc2007 looking forward to using Split Forms.

To my horror I have just discovered that Split Forms do not hold variables at module level!!! Although code is working perfectly well when in Single Form view, when switching to Split Form view... problems. This must definitely be a bug unless there are limitations to Split Forms (doubt it).

Although there might be some workarounds when it comes to normal variables, would there be any workarounds for object variables? I am trying to subclass these forms and without module object variables I don't know how that can be possible!

Please let me know if anyone knows about this. I am too far with upgrading to go back now...

I am attaching a database that recreates this issue and shows how the Current event of the form is not fired through the class when in Split Form view. Change .txt extension to .accdb

[Megalog]

Funny, my first post here was about split forms too..

Access 2007: Split Forms with Subforms

I'm not sure if you'll get anything helpful out of that thread... I tried your sample database and verified the problem. It could be when you change records, the on current event isnt firing off properly because of the buggy navigation button behavior with split forms.

I'll try your sample db with my Access 2010 copy and see if anything has changed.

[geosmy]

I saw that post and that's how I found out that Split Forms do not hold module variables. It was very helpful in understanding why this problem occured (the Current event not firing - this is how this issue started) but unforunately I could not see any workarounds.

I tried referring to the Form as Forms!Form1 instead of Me but that didn't work.

If you test in Access 2010 please let me know. I am very interested.

[Megalog]

Geosmy,
Same behavior in A2010.. so no magical fix there..

I did notice that the class code does execute properly if you navigate records using the arrow keys. I think the entire problem is the record selector on the form is bound to the datasheet on the bottom, and not the actual form on top??! Or at least that's how it BEHAVES. It's causing the recordset to move in some manner that doesnt activate the form's events. Very very frustrating...

I have a co-worker who has custom functions that work very well on a datasheet.. he's had the same problem trying to get these functions to work on the datasheet side of a split form. So there's no winning with these!

[Megalog]

You might want to remove the built-in navigation buttons from the form, and put custom nav buttons in the header.. See if that works.

[geosmy]

Hi Megalog,

I tried disabling the nav buttons and navigate through the datasheet but no luck.

I experimented with the orientation of the form (datasheet top, bottom...) and I noticed that when the nav buttons are attached to the proper form they work properly but when they are attached to the datasheet the problem is still there.

I think that the core of the problem is navigation through the datasheet in any form (either through buttons attaced to the ds or through selecting records from the ds). I can work around the buttons issue by putting the ds on top but a user can at any time select records through the ds (this is the main purpose of the ds in the first place). In the latter case I don't see how custom buttons would work since they would not be attached to the ds but to the main form.

I will keep trying though. Thanks very much for the suggestions. Shocking that Acc2010 has not fixed this though...

[Megalog]

Hrm.... If you have any luck let me know. I was a bit dismayed when I saw they didnt fix any of the problems with the splitforms, or add any new functionality to them in a2010. Although, they have added something called Table Events, which is basically macros tied to the table itself. So, you could have a form that has no coding on it at all, and have the table itself handle the events when the form interacts with it. But I can think of many scenarios where we'd rather have the form do the work.

[geosmy]

Sounds interesting. Maybe there could be a workaround for my issue (current event not firing in subclassed form) through table events...

When I get hold of access 2010 I will check it out.

[Nej Sanerkin]

I have had the same difficulty in accessing form module variables in split forms. I have found that if the cursor/focus is in the detail section as opposed to the datasheet the variables are exposed. There seems to be no syntax for moving the focus that I have been able to find, except for F6. In my code I test for the variable and if empty I move the focus by sending - SendKeys "{F6}{F6}", this is grossly inelegent, but needs must.

[geosmy]

Hi Nej,

To my understanding when access loads a split form it loads two instances of the form. One instance corresponds to the main form and one to the datasheet part. The main form is the main instance (default) on which events such as "On Load" and "On Open" fire at startup.

When focus is on the main form the events that fire (like "OnCurrent" ) correspond to the instance of the main form. Likewise when the focus is on the datasheet the events firing correspond to the datasheet instance. As said earlier on startup (no focus yet) the events of the default instance (main form) fire up.

So when you have a module level variable defined in your split form this will be different for each of the instances (same as if you opened a new instance of SomeForm with New Form_SomeForm - all variables in the new instance will be independent of the original instance).

Now when it comes to the navigation buttons, these seem to belong to the instance of the part of the form on which they are attached to. So if they are attached to the main form the OnCurrent event fires from the main form and when they are attached to the datasheet the OnCurrent fires from there.

You don't even have to change the orientation of the datasheet to see this behaviour. You can play around with the tab key on either part of the form and try to change records from there. The tab key changes the records from the part that you are in at the time.

So what you basically have to do if you are running into problems with this behaviour, is to ensure that your code can deal with both instances (and both their module level variables) seperately and consistently. For instance in my initial example my module level variable was not initialised for the datasheet instance because the "On Load" event would only fire for the main form instance. So all I had to do was at some point to initialise the variable for the datasheet too (along with referring to the form as Me instead of Forms!Form1 to get the right instance - Forms!Form1 seems to make a reference only to the main part of the form!)

You can see all these with the new db I am attaching. Check Form2 and see how the Integer module variable loads and changes with the current event when you change records from the different part of the form (use the tab key).

Hope this helps!