<

How to synchronize forms using own events - for example "split form" with any Access version

Published on
23,694 Points
9,794 Views
9 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
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.Form" 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.

Events.

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

Open in new window


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) 

Open in new window


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) 
End Sub 

Open in new window


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 

Open in new window


"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 
End Sub 

Open in new window


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 
            Forms("frmMain").sfTest.SetFocus 
            DoCmd.GoToRecord , , acNewRec 
          Else 
            GotoID Me, lngID, "ID" 
        End If 
    End If 
End Sub

Open in new window


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" 
End Sub 

Open in new window


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...:-)

Christian

EventsEN.zip
9
Comment
Author:Bitsqueezer
  • 6
  • 4
  • 4
  • +1
15 Comments

Expert Comment

by:developingprogrammer
OH MY GOODNESS!!!!!!!!!!!!!!!!!!!! I HAVE BEEN TRYING SOOOO SOOOO LONG TO GET EVENTS WORKING IN ACCESS AND FINALLY I'VE GOT IT WORKING!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

oh my goodness!!!! the last frontier (other than regex hahaha) is finally broken!!!!!

this is such a fantastic article Christian!!!! once again, i'm enlightened by you!!! = ))
====================================================
one question though that came into my head before a long time ago, if i've got 5 classes that are event recivers, which one would fire first? what are the rules? of course we should try to ensure they  are not reliant on each other, but in any case for experimenting and pedagogy, what are the rules?

i will try this out myself and post here again, i'm off to read the other post you put up on my question = )

To Everyone Else!! --> once again, Christian has very, very generously spent his time helping a developingprogammer haha = ) here's the link to his post on my question about custom events. he has put in a lot of time answer me and giving me fantastic examples (as per all his replies to me always = )    ), so do take the time to check out his generous and excellent pedagogy!! = ))

Christian's Further Comments On Custom Events
=====================================================
0

Expert Comment

by:developingprogrammer
Christian, could you kindly consider changing / appending the title with "Custom Events VBA Access" so that when i / others trawl the internet for custom events for vba in access we can find this article? maybe my googling skills aren't that good, but this is the FIRST article that gives me exactly what i'm looking for. as always by you!! = ))
0

Expert Comment

by:developingprogrammer
dear all, for your easy reading reference, here is Christian's comment in my question, further elaborating how custom events can be used. it's absolutely fantastic (his sharings), so please do read on!! = ))

Hi,

events are really interesting if you know how to use them.
The cpearson article is interesting - but not very helpful in case of Access. Maybe this approach is a start point for you:

How to synchronize forms using own events - for example "split form" with any Access version

But this is only one possible usage of own events.

1. Events for multilanguage applications
2. Events to write own controls
3. Events for showing progress
4. Events to make objects independent

(this enumeration is of course not complete, the usage of events depends only on your fantasy.)

1. Events for multilanguage applications

Using the method in the linked article which shows how to synchronize forms you can do the same thing to switch the application to another language. You would only need a class module object which has defined a "ChangeLanguage" event (you can name it like you want) which takes a parameter for the language (and the parameter could be a value from a public enumeration which lists all available languages, defined in the same class module, like I've shown you earlier).
Then you can define a "RaiseChangeLanguage" public sub in the same class module which also takes the same language parameter and which does nothing else than firing the event and forwarding the language parameter to the event.

Option Compare Database
Option Explicit

Public Enum EnmAvailableLanguages
    enmAvLang_DE
    enmAvLang_EN
End Enum

Public Event ChangeLanguage(intLanguage As EnmAvailableLanguages)

Public Sub RaiseChangeLanguage(intLanguage As EnmAvailableLanguages)
    RaiseEvent ChangeLanguage(intLanguage)
End Sub

Open in new window


To use it, you would instantiate this class module simply in a standard module using the "New" attribute:

Public objLanguage As New clsLanguage

Open in new window


Because a standard module always will be automatically instantiated the class object would also automatically be instantiated.

You could fire the event now:

objLanguage.RaiseChangeLanguage enmAvLang_DE

Open in new window


But of course nothing would happen because nobody is "listening" to the event. The same as with any form: The "Form_Load" event will always fire when you open a form, but as long as you don't write a "listener" - the Form_Load event sub - nothing would happen.

So what you need is a listener. Let's say you want to change the labels of your form when the event fires. All you need is an object variable which can receive event messages. That can not be done in standard modules so you can't use the "objLanguage" variable of the standard module. You need a class module - fortunately a form module IS a class module and so you can write on module level:

Option Compare Database
Option Explicit

Private WithEvents objChangeLanguage As clsLanguage

Open in new window


But that's not enough, because the variable is "Nothing". Using "New" here would be wrong as it would instantiate another language object so your form would be the only module which could fire and get the events from this new object. So we must use the object variable of the standard module:

Private Sub Form_Load()
    Set objChangeLanguage = objLanguage
End Sub

Open in new window


Now we have simply the same object, only a new reference, a "pointer" to the existing object. Now any object which fires the event would also inform our form that the event was fired. Fine. But nothing happens. You remember: Form Load but not Form Load Sub...

So we must do that now, fortunately the VBA editor helps us to create such sub. Look into the left dropdown field at the top of the VBA editor: It shows all available variables which can fire an event. As we defined our object variable with "WithEvents" it's also listed here. Choose it and you get:

Private Sub objChangeLanguage_ChangeLanguage(intLanguage As EnmAvailableLanguages)

End Sub

Open in new window


Same as with Form Load. This sub will now be called everytime the event is fired anywhere.

Now it must do something and that's where we can use the parameter:

Private Sub objChangeLanguage_ChangeLanguage(intLanguage As EnmAvailableLanguages)
    Select Case intLanguage
        Case enmAvLang_DE
            Me.lblTest.Caption = "Dies ist ein Test"
            Me.cmdTest.Caption = "Testschaltfläche"
        Case enmAvLang_EN
            Me.lblTest.Caption = "This is a test"
            Me.cmdTest.Caption = "Testbutton"
    End Select
End Sub

Open in new window


If you would have these two controls on your form (a label named "lblTest" and a commandbutton named "cmdTest") you would now see that every time the event is fired (for example using the immediate window of VBA) the language of these controls will change accordingly.

So you see, not really difficult to work with own events.

2. Events to write own controls

But of course we can do a lot more with events. In .NET and WPF it's simple to create own controls which will be assembled using other controls and simply apply them a new outfit to create something completely new.
Jealous? No need. We can do that with Access and VBA, too (and of course also in other VBA applications which has programmable controls like Excel).

The difference is that a control in .NET and WPF will be bound together using XAML so you can take that as one control.

All you need to assemble your own "control" in Access is a class module which holds some "WithEvents" event variables. All you need is a little bit fantasy. Let's say you want to make a new type of checkbox - why not use a textbox to do that?

You can create a variable in the class module which takes the events of a standard textbox control:

Private WithEvents prv_objTextbox As Access.TextBox

Open in new window


Now you need an Init sub which gets the reference to an existing textbox control and which assigns the events (because Access objects additionally needs the assignment of "[Event Procedure]" to be assigned to an event property to tell Access that there is a sub which should be used as listener).

Public Sub Init(ctl As Access.TextBox)
    If Not ctl Is Nothing Then
        If TypeOf ctl Is Access.TextBox Then
            Set prv_objTextbox = ctl
            prv_objTextbox.OnClick = "[Event Procedure]"
        End If
    End If
End Sub

Open in new window


You also need a sub which reacts on the click event:

Private prv_objTextbox_Click()
    Static bolValue As Boolean
    If bolValue = True Then
        bolValue = False
        prv_objTextbox.Value = ""
    Else
        bolValue = True
        prv_objTextbox.Value = "X"
End Sub

Open in new window


The only thing to do in the form would be to send the reference to this class module:

Option Compare Database
Option Explicit

Public prv_objCheckbox As clsCTRL_Checkbox

Private Sub Form_Load()
   'On Error GoTo Form_Load_Error

    Set prv_objCheckbox = New clsCTRL_Checkbox
    With prv_objCheckbox
        .Init Me.txtMyTextbox
    End With
End Sub

Open in new window


Now if you click on the textbox you can change the contents and it should react similar to a real checkbox (of course not exactly, there some issues with this new "control", but adding more and more functionality to the class module you can make a really better "checkbox").

The interesting thing here is that you can of course now add own events to your class module. So you could create an event which should fire when the value has changed. You could add properties and methods which could be used as "wrapper" to translate things to the textbox properties and methods so your class only offers the things the users of your class should work with. So you could, for example, add a "Value" property to the class module which fires the own event if the value is changed. On the other hand, your form could create the object variable with "WithEvents" and add an event procedure which will be informed if the value would be changed using the own "Value" property of the class.

You can also tie more than one control together with this method. You can, for example, use a commandbutton and a rectangle control and a label control to create your own slider by using their individual standard events (like MouseDown, MouseUp and so on) to inform the other controls how they should react on the user input. You can offer events to the class module user which handles the "value" of this slider control (although no of the three standard controls can work with values) and inform the using module about the changed value. You can also create completely new events which are not available in standard controls like maybe in the slider example an event which will be fired when the one or other end of the slider has reached or anything else.

I attached a slider demo which I made earlier which demonstrate what you can do with own controls.

3. Events for showing progress

Of course you can also use own events to inform other modules about the progress of a procedure. The way is the same as above. You would create a public event and you would raise the event each cycle of a loop or any 10 times or whatever. But beware that of course this will slow down the loop as any object which uses the same class module object with the loop will be called any time the event raises.

But the trick is that you could for example use a class module to write a lot of data for example from an Excel file to a table. Maybe you must do a lot of tests and this takes so long that you want to inform the user about the progress. So if you would do this by looping through recordsets (although that's of course not the recommended way of importing data from Excel to a table) you could fire an event each 10 rows for example.

What you get is a class module which does no graphical output, it only works with data and fires an event. The form which would use this class module would simply create an object variable with "WithEvents" and creates an event sub like in the examples above. Now the form will get informed through the event which for example has a percentage value as parameter which tells the sub the progress. The form can now use any graphical method to translate the percentage value in a graphical output - additionally it can change the caption of a percentage value label, additionally it could do anything else like "DoEvents" to give the user the possibility to click a stop button and stop the input or anything else like that.

4. Events to make objects independent

So in the end we got what makes events so powerful: Independance of objects. To achieve the same as explained above any of these class modules could use other methods. For example, you could create a public method in the form modules which gets the percentage value of the last example to do the same. The class module would get the form reference in an init procedure and calls the public method of the form - would also work.

But: The class module doesn't "know" if the form really HAS this procedure. Normally you send standard "Access.Form" references and IntelliSense would not show you the public declared sub. You must know that on your own - and you must type the name correctly, the compiler will not warn you as he uses the base class, the "Access.Form" class which doesn't have this method. Of course, you could get a reference using the specific "Form_YourFormName" class module type. But that means, the class module now knows the sub, would be listed in IntelliSense - but it would also be "hard coded" and bound to exactly this form. You could create 100 forms of this type, all these could use your class module - but the class module can't work anymore with any other form. Think of the "own control" approach: You could create this control for only exactly this one form type - or you must use the generic "Access.Form" and all form types must implement all the same public subs and so on (yes, you could use an Interface for this but that's another story).
The interesting thing in using events is that an own event in a class module calls into the world "hey, something happens, I only want to say that!!" - and nothing happens. The class module doesn't have a form reference so it does not execute anything actively. It executes things passively. Because the way is now vice-versa: The using class module (which can be a form, a report or another class module) inserts an object variable of this event class module, either instantiating an own one (like in the example of an own control) or using a standard instance which is of interest for the complete application (like with the language event). As the using module now has a "listener" it will be informed about the shouting class module and executes the event sub in the own environment independant of each other module. So a form can now change all it's own controls to the desired language, another class module getting this event could for example call the Invalidate command to let the ribbon get the new changed language entries from a standard module and so on. Anything is independant from each other and you don't need to check if the "other" module is loaded (like with the form mentioned at the beginning of this section which must be loaded if the class module should call the public methods. The form could be closed by the user while the class module still exists, calling the public method would create an error as the reference to the form now points to "Nothing").

In a real complete and object oriented programming model in VBA you should always try to make objects completely independant from each other. You will often see programmers which write a sub into a standard module using things like "Forms!MyForm!MyControl.BackColor = RGB(255,255,0)" or anything like that which is a completely wrong approach. At least you would use a control reference in the parameter list of this sub so the sub would change the back color of this control - this makes the sub independant from the caller. And the next step on this idea is the use of events: Making objects completely independant from each other by sending messages to each other without knowing if the message is received or used for anything. That's the way your operating system Windows works - and many others also.

Hope this little article helps you enjoying events and get the most out of it. They are really powerful however rarely used (but I think that's also the result of a really bad help file explaining nearly nothing about events in VBA).

Cheers,

Christian

**Comment edited by Netminder 27 July 2013 to fix formatting issues**
CCSlider.zip
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Expert Comment

by:developingprogrammer
Netminder thanks for editing the comments to fix the formatting issues!! = ))
0

Expert Comment

by:developingprogrammer
guys gustav (cactus_data) shared a really great article with me that i would like to share with yall too on custom events. here it is! thanks gustav!! = )

Custom Events Tutorial (applied to forms)
0
 

Administrative Comment

by:Eric AKA Netminder
Congratulations, Christian; your article has been awarded Editors' Choice status.

ericpete
Page Editor
0

Expert Comment

by:developingprogrammer
Woo hoooo!!!! = )))
0
LVL 24

Author Comment

by:Bitsqueezer
Hi ericpete,

thanks for the award...:-)

Cheers,

Christian
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi thanks for the explanation.... i am using this a relative long time thanks to your clarification !

i am experiencing an issue which i wonder whether someone has notice it. on run-time if the code engages  "form_formName" it breaks the listener and will not fire anymore.

I would appreciate any help !
0
LVL 24

Author Comment

by:Bitsqueezer
Hi,

you need to explain your question a little bit more to answer anything meaningful.

Cheers,

Christian
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi
 
I have multiple related forms open and i wanted to use this technique in order to keep the forms independent without the need to call FormB from FormA...
and everything works fine besides if i uany part of executed code includes the  form' constant (EX. form_FormName.visible=true), from this point on the listener gets a nap (forever)
0
LVL 24

Author Comment

by:Bitsqueezer
Hi,

depending on your code you should avoid to access a form using "Form_Nameoftheform" in general. The problem is, if that form is not open or could not be found, VBA creates a new instance of the form which don't need to be the instance you formerly used. If the object instance of the message event class module object in this new form is not the same as the object instance of the former form no message ever reaches the new form.
So normal way is: If you have opened a form before and only made that invisible you need to save the reference to this form anywhere to access this to make it visible again. That's the only way to make sure you get the right instance. If you have opened the form ten times with different reference variables then only on this way you can pick the right instance with the right object reference variable. "Form_Nameoftheform" could create a new one or Access could get the first with this name from the Forms collection which might not be the right one.

Anything else could only be said if you upload a demo database to show your issue.

Cheers,

Christian
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi

If you have opened the form ten times with different reference variables then only on this way you can pick the right instance with the right object reference variable.

how can i open a form ten times without using form_formName ? the forms collection  contains only opened forms !

Reagrds

Joseph
0
LVL 24

Author Comment

by:Bitsqueezer
Hi Joseph,

you must create an own collection and insert the references to the created forms into the collection:

Code in the form which should create the other forms:

In the header of the form's code:
Private colForms As Collection

Open in new window


Form_Unload Event:
Set colForms = Nothing ' This will automatically close all forms opened with the collection

Open in new window


Button_Click Event:
Dim objForm As Form_YourFormName ' <- here is the right place to use that
If colForms = Nothing Then Set colForms = New Collection
Set objForm = New Form_YourFormName ' <- here also, now the form is loaded but not visible
colForms.Add objForm, "Add a meaningful name here for each different reference (unique)"

Open in new window


Now you can make each reference to the form visible either with the objForm variable (inside the code above) or by accessing the collection:

colForms("TheMeaningfulName1").visible = True

Open in new window


Cheers,

Christian
0
LVL 1

Expert Comment

by:Joseph Krausz
Hi

I just test it and you're right, using the form_formname for form instance purposes (frm as form_formname) will not break the listener, in the mean time i've improved my code form_formName free.... Anyhow, it's working and really

Regards
Joseph
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month