After experimenting a while with the new split form in Access 2007 I found that it has too many problems to be usable, especially if you need subforms.
The idea is good but internally Access uses two different form objects to create a split form and that's often a big problem. I tried to use an object variable inside of a split form and after a lot of tests, wherein I asked myself "why am I not able to access the stored data inside of the datasheet object", I found out that two objects are instantiated when opening a new split form -- one for the single form and one for the datasheet form. Access handles the form in VBA "as one" so any access to the form uses only the single form. Only constructs like "Screen.ActiveDatasheet.Fo
rm" can be used to access the datasheet form.
Another annoying point is the automatic creation of subdatasheets if a subform is created on the single form part. Every line in the datasheet part gets a "[+]" sign and you can open the subform directly at the chosen line. That would be good if both forms would be synchronized -- but if you change something in one subform it is not shown in the other part. If you have more than one subform, only the first one in the tab order is bound to the datasheet part. No chance to change this. If you try to change the SourceObject property of the subform to change the subform to another form then Access crashes if you try to access the [+] in the datasheet part...
I found no way to switch off this feature.
So for all who have troubles with split forms or who don't have split forms like in earlier versions of Access here is another way to build your own split view which is stable and fast and easy to access.
It uses a feature of VBA that is rarely used and most people even don't know that VBA can be used on this way. The help of Access doesn't offer much information about that so it's no wonder.
Normally, events are used in the daily work with Access by anyone. If you have used a VBA procedure to do something during loading of the form you have already used a form event named "OnLoad".
But you don't need to use only the events Access offers out of the box. You can simply create your own events.
The help text in Access can only be found if you already know what you are searching for, with search words like "WithEvents" or "RaiseEvent". The example -- showing an object that is instantiated in a form to show something after a specified period of time -- doesn't really make much sense because every form already have a timer event, and the event is only available inside this form.
If you have a project consisting of several forms and subforms, you often have the need to refresh the contents of several forms that are currently open showing data similar to the current form: for example, a form that shows master data of a category table that can be edited by the user and is used inside a combobox of another already-opened form.
Normally you would use tricks like a modal popup form to make sure the user finishes editing in the master data form before going back to the combobox form so you can refresh the combobox if the popup is closed. Hmm, yes, but did you think of the three other currently opened forms which also used this combobox? OK, so you need to go through the Forms collection (which shows the opened forms) and requery each one's comboboxes.
But stop -- one form used a subform and here there is the combobox again. The subforms are not listed in the Forms collection so you must know that form "A" has a subform "B" and then go through the main form and requery the subform. It is not very efficient to search all the forms that must be requeried, and only this combobox needs a requery, not the whole form. What if you have three other comboboxes based on three other master data forms with each using another table?
You see, it can be very complicated to refresh a complex application without simply requerying a complete form (which would of course requery all contained comboboxes, too), because this would load all the data of the form, too, which can be a lot of unnecessary data loaded through a network. And not only by this user, but the other 200 wanting to do the same...
The solution is the simple event model.
The idea is, you have only one instance that fires some events and a receiver that checks the events and reacts if there is something to react to. Or: If one form changes a record it calls "to the world" and says "Hello all, I have changed something, so whoever is listening, please requery!"
The other opened forms (and of course only
the opened forms) listen, and if there is something related to them they refresh their contents. The form that calls "to the world" doesn't need to know if there is any other form loaded and it doesn't need to know if it really refreshes; it only shouts out its own
status and the other forms must react now.
The whole thing works with three commands: "Event", "WithEvents" and "RaiseEvent". But of course there are again some little restrictions like always in Access...
1. Events can only be defined in a class module.
2. RaiseEvent can only be used in the same class module where the Event is defined.
3. WithEvents of course must also be in the same class module.
Fortunately forms (and reports) are class modules so they can use "WithEvents" objects, so instead of creating a new event in the form load event procedure, like is often shown in examples, we use one single event object which does all the work.
Fortunately, too, there are also "normal" modules ("static" classes in higher languages) and these can be used to hold and instantiate a global class object by simply defining them as global and with the keyword "New". Starting the application will automatically instantiate the object.
So now we have all we need to create are a news sender and receiver.
1. The news sender
The "radio station" is simply created by one single line in a module like this:
Public e As New clsEvent
The application starts and a new class object based on the class "clsEvent" will be instantiated and can now be used from within the whole application.
Now we have the "building" of our "radio station", so now it's time to fill it with life. This is very simple, too: For every event that should be sent by our radio you will need one event declaration. Here the one for the form synchronization used in the example:
Public Event syncForm(lngID As Long, strName As String)
Unbelievable, but that's all it takes to create an event. You can define parameters like in any sub. These are the variables sent "through the air".
But of course, an event would not do anything if there is nobody who fires this event. Unfortunately the event can only be fired by the same class where the event is defined (see above). So to be able to fire the event from outside we must surround the call by a public method. Now we can start the event from anywhere -- life can be so simple, here's the example:
Public Sub RaiseEvtSync(lngID As Long, strName As String)
RaiseEvent syncForm(lngID, strName)
Now if anyone has to tell something "to the world" he can use this simple sub to start sending.
2. The news receiver
Sending news to which nobody listens makes no sense so now we need the "Audience". That's the point where the third part of events comes in, the "WithEvents".
A form that will be used as "audience" can now install its "radio", like this:
Private WithEvents evt As clsEvent
"evt" can be named anything you want; it must only match the name of the event procedure discussed later. The type is again the already known: "clsEvent" which contents you saw above (the lines to create an event and to access the RaiseEvent externally).
And again this object does nothing without a connection to the radio sender. If you install a cable radio in your home you must plug into a cable, or it would not react to any sender.
We can do that at the form's load event:
Private Sub Form_Load()
Set evt = e
It's important to point out that you don't use the keyword "New" here like "Set evt = New clsEvent". This would create not only your radio, but it would also create its own radio station -- but there would be no other radio to listen to it. You already have a radio sender, the global variable "e" in the module. We plugged in the cable -- or to say it in VBA style: We set a reference to the event object.
The cable radio is online, so now we can listen. But stop, who listens now? We have a radio, and it can get news, but we don't have a listener. That's what you do every day with Access: If there is no listener to "Form Mouse Down" nothing reacts when you click the mouse button. Yes, you need to define an event procedure but that's nothing new and as easy like above.
If you look at the upper left combobox of the VBA editor you will see a list of all available objects in the current form. And you see: Now there is an additional "evt" in the list! If you choose this then in the right combobox all defined events for this object can be chosen. This will automatically create the right syntax for the event listener like you know from the form event procedures, including any defined parameter.
For the synchronization example it looks like this:
Private Sub evt_syncForm(lngID As Long, strName As String)
If strName = "tblTest" Then
If lngID = 0 Then
DoCmd.GoToRecord , , acNewRec
GotoID Me, lngID, "ID"
This event listener now gets the parameter from the sender and can test the contents to do something with it. Here it is: If the ID is 0 then it should be a new record, and in any other case it is the ID where this form should be synchronized. A simple little sub named "GotoID" is used to do this job for all forms. Moreover it only reacts if the name that was sent is "tblTest". In any other case nothing happens here.
So if the radio sender sends to all listening radios "hey, all firemen from Los Angeles should come to put out the fire", the nurses from New York aren't interested in this message. Why should they react to put out a fire in Los Angeles?
Here it means: If a special name sounds from the radio, all forms should react and test this name. A string is of course only one method; you could use a complex object to be sent as the event message if you want -- nearly every datatype is allowed. But for now we want to keep it simple.
The ID now tells the current form to which ID it should be synchronized.
And here comes the third player: Someone must send some message.
3. The message creator
The best radio sender is of no use if there is nobody who sends something. For this purpose we have created the public methods in the event class module. They can create a new message and fire the event. It's a liberal radio sender -- anyone can create a message and send it. It will be sent immediately and guaranteed not censored...
In the example database this is used to synchronize two subforms. Both are message creator, radio and audience. The main form in the example does nothing; it is only the container for the subforms so it doesn't need to install a radio.
Both subforms use the same table as record source -- otherwise a synchronization wouldn't make sense. The left one uses a continuous form, the right one a datasheet form.
The best moment to send a message to tell about the change of a record is of course the form's current event. Whenever this happens, a message should be sent to any form:
Private Sub Form_Current()
e.RaiseEvtSync Nz(Me.ID, 0), "tblTest"
OK, not really all. Only the firemen of Los Angeles -- or in our case, any form listening to the word "tblTest". If you want, you can change this to "firemen" if there is a form listening to the word "firemen".
"Nz" can here be used to check if the current record is a new one or not (because the ID is an autonumber field and only in a new record the value would be NULL) , if it is, then a 0 is sent.
Now the message is sent and any form listening for the word "tblTest" will synchronize. Very easy. Not only another subform. Any form at any place -- because every form has its own radio plugged in to the "e" event object. Never again do you need to think about referencing a lot of difficult subforms in subforms in subforms in forms...
And because any form is both sender and receiver at the same time the synchronization works in both directions. It works even if one form is filtered and the other one is not -- because the bookmark is set with the recordset find method and if the record is not found (because it's filtered out) then nothing happens - until the other form jumps to a record that is also displayed in the filtered form.
In the example database a further event is defined to exchange record updates so that the updates are shown in any other form that shows the same data.
You can create as many events as you want, for example an event for a deletion of a row. With enough imagination you can do a lot more with this simple event model, and you can create a "split form" by using two subforms. These can be easily accessed like normal forms.
A better variation of this very simple model would of course check the sender of the message -- because the form that sends the message gets back the message, too. So it would not requery itself if it is the sender of the message. But for this little experiment this should be enough; I think you will find a lot of useful things to do with it.
Have joy in experimenting...:-)