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!

[Nej Sanerkin]

Thanks ,
Very clearly illustrated with your demo, the Me form object returns different references dependent upon whether you are in the Main form or the Datasheet, although they both return the same name. Once the form has opened it is possible to capture both the Me objects to named objects, and then use [named object].Control(“...”) .setfocus or whatever to access either the Main or Datasheet methods and properties. However, to capture the datasheet Me reference the cursor must first be moved to the datasheet and an event fired to capture its Me object.
Referencing using Forms(name) always returns a reference to the Main form instance.
I need to spend some more time with the event management so thanks for the pointers in the demo.
However, my current objective of being able to open a splitform and re-direct the cursor to the datasheet in the first instance without recourse to the hideous SendKeys “{F6}{F6}” remains.

[Nej Sanerkin]

It would seem that the easiest solution to accessing the split forms module variables is to Declare them as public, and access then using Forms(me.name). VariableName as this will reference the Main instance of the form whether called from the Main or DataSheet instances of the split form.

[geosmy]

Hi Nej,

I haven't understood exactly the nature of your problem but you are probably right. The easiest solution would be a global variable but you would need to be cautious of how to handle it in the case you would want to use mupltiple instances of your form (either now or in the future).

I think the best principle to follow (once you have understood the behaviour of split forms - which by the way is not a good behaviour at all) is to treat the split form as two instances of the same form and try (if possible) to deal with both instances of your variable in a consistent manner. I know it sounds vague but it all depends on what exactly you are trying to do and how your variable needs to behave.

For example with my initial problem all I needed to do was to find a way to initialise my custom class for the datasheet part of the form. So all I did was to put an extra check to see whether my variable had been initialised and if not I initialised it. This covered both instances and solved the problem (not perfectly as the first time the Datasheet gets activated this don't fire...)

If on the other hand you need your variable value to be synced between the two instances then you might need to work something out (eg recalculate your variable every time or do anything that your code's logic might dictate). It could be tricky and might not even work at all in which case the global variable would be your solution.

Another point that might help is that we know for sure that On Open and On Load the Main instance is active. So if you reference a variable (eg objMain) to Me during these events you would have a kind of flag that could let you know which instance you are on at any time by simlpy checking whether objMain Is Nothing or not. This way maybe you could have two versions of your variable(s) (eg MyMainVar and MyDatasheetVar) which you could manipulate independently depending on which instance you are.

If you manage to do this consistently then you would have solved your problem and be able to use multiple instances of your form (with New Form_MyForm syntax). Note that your code in this case would also work even if you changed the default view of your form to Single Form etc.

[jforbes]

I know this thread is old, but I thought I would post a partial solution for those that are still running into this problem and come across this page in their search for a solution (like myself).

According to Geosmy access creates two instances of the form when creating a split form. I thought to inspect the CurrentView property of the form and it is a 1 for a Form View and 2 for a Datasheet View.

So to make the two instances to call the same form, I added the following code for my onCurrent event:

.

Public Sub callFormCurrent()
    Call Form_Current
End Sub

Private Sub Form_Current()

    If Me.CurrentView = 2 Then
        Forms(Me.Name).callFormCurrent
    Else
        ' Do all the things you want to accomplish here  
    End If
       
End Sub

.

This will need some additional tweaking to get it to work for multiple instances of a form, but it works for single forms pretty well.