473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

8
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
Attached Files
File Type: txt CurrentNotFiring.txt (356.0 KB, 943 views)
Aug 21 '09 #1
13 8844
Megalog
378 Expert 256MB
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.
Aug 21 '09 #2
geosmy
8
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.
Aug 21 '09 #3
Megalog
378 Expert 256MB
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!
Aug 21 '09 #4
Megalog
378 Expert 256MB
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.
Aug 21 '09 #5
geosmy
8
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...
Aug 22 '09 #6
Megalog
378 Expert 256MB
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.
Aug 24 '09 #7
geosmy
8
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.
Aug 24 '09 #8
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.
Sep 17 '10 #9
geosmy
8
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!
Attached Files
File Type: zip CurrentFiring.zip (97.7 KB, 301 views)
Sep 18 '10 #10
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.
Sep 19 '10 #11
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.
Sep 20 '10 #12
geosmy
8
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.
Sep 20 '10 #13
jforbes
1,107 Expert 1GB
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:

.
Expand|Select|Wrap|Line Numbers
  1. Public Sub callFormCurrent()
  2.     Call Form_Current
  3. End Sub
  4.  
  5. Private Sub Form_Current()
  6.  
  7.     If Me.CurrentView = 2 Then
  8.         Forms(Me.Name).callFormCurrent
  9.     Else
  10.         ' Do all the things you want to accomplish here  
  11.     End If
  12.  
  13. 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.
Aug 19 '14 #14

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Chris | last post by:
I need to pass a form say 'webform1' in a class file myClass (.vb) and access its web controls say 'txtdisp', ..... My code snippet is : Imports System.Web.UI public class myClass public...
1
by: dddsssdddsss | last post by:
A comment and a question To anyone who is using conditional formatting, beware that in Access 2007 the color pallette is not the same as the color pallette in Access 2003. So if you have a...
4
by: Peter | last post by:
Access 2007 optionally opens forms as tabs. There is an Access Option that controls this - Access Options Current Database Document Window Options Tabbed Documents. But how, from VBA, can I...
3
by: dstork | last post by:
Has anyone else experienced Access 2007 crashing when users right click on the subform (with the intention to use the copy feature in the shortcut menu). I went through Allen Browne's...
0
by: News Lantic moenie del | last post by:
Hi How can I disable the close button in access 2007. Reason I want to force a backup on exit and now I can't. The code for Access 2003 to disable close button does not work in Access 2007 Thanks...
1
by: gman | last post by:
Is there a way in VB.NET to build a Form/Control as easily as Access 2007 does? I built a small name and address application in Access 2007 and I was surprised at what I was able to do without any...
2
by: puT3 | last post by:
Hi, anyone can help me? My problem is data from Form of Asp does not appear in access database when i open the database. Why does this happen? My table name:Cuba ID PK Nama Umur
4
by: simulationguy | last post by:
I inherited an Access 2003 database runs fine in 2003 but in 2007 I get an error on updating a form. Record Not available on the last statement !ItemNumber.SetFocus Does anyone know why...
0
by: GARZAAX | last post by:
Hello; I am in serious need of help. I have a form with subform that I am developing to fill in multiple data on customer orders. the need has come to duplicate up to 10 times the information on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.