I am working on an Access 'Data-Entry' Form which has the following: a tab control which has 3 pages- page1, page2, page3; each page has a subform: page1 -> subfrm1, page2 ->subfrm2, page3 -> subfrm3 and each subform has a text box: subfrm1 -> txt1, subfrm2-> txt2 , subfrm3 ->txt3. txt1,txt2 and txt3 are bound to the same ControlSource. Now, here is what I do: I click on page1, enter a value in txt1, click on page2. Now, when I click on page2 I want subfrm2.txt2 show the value of subfrm1.txt1 and if I click on page3 I want subfrm3.txt3 show the value of subfrm1.txt1 and of course I don't want to open the subfrm. Can anybody please help me with this?
Thank you very much for the reply, gavinfm.
But that does not work because oncurrent event triggers only when the form is independent. Once it becomes subform, the only events available for a subform control on a main form are onenter and onexit events. And one more thing: main form and subform have one-to-many relationship. If I go back to the previous record of the mainform through navigation buttons and come back to the just entered record it shows up on both the pages because its bound to same control source. I hope I am clear about the problem.
Be clear about the difference between the control on your main form that 'holds' the sub form and the sub form itself. The control only supports the OnEnter and OnExit events whereas the sub form and all its controls support all the spectrum of events.
You have a situation where a set of page controls are contained in a tab control. On each page there is a control that 'holds' a sub form. Then there are the sub forms themselves.
Now unfortunately the only remotely useful event for pages in a tab control is OnClick. But OnClick will not detect a page change brought about by using [Tab] or any other non mouse means. You need to devise some way of detecting that a page change has (or is about to) occur; perhaps you can use some event or property on each of your sub forms. Try the AfterUpdate property of the TextBox you are changing. (I suggest the After- event rather than the Before- event since you want the change to be propagated through to the underlying result set. This code is written in the sub forms module.
Now a slight variance from gavinfm's advice: When I need to force a sub form to display the latest up-to-date information, I tend to requery the control that 'holds' the sub form rather than the sub form itself or any specific control on the sub form. I can't remember whether the Me object for the sub form knows about the control on the main form at run time; I rather doubt it. So you will need to specify the full object model path to your main form and then requery each control holding a sub form in turn. (At least I think you will have to do it for all of them. Try doing it only for one and see whether the requery propagates to all sub forms based on the same underlying result set.)
Be aware that the choice of OnChange as the triggering event is inefficient as this event is raised each time an item of data is changed, that is each time you insert a character overwrite a character, delete a character, etc. So it is possible you are requering the underlying result set many times for what the user thinks of a one change. If you don't notice any degredation in performance then don't worry about it.
An alternative is to use the Before or AfterUpdate events for the control with one proviso: these events only seem to be raised when the user exits the control in question AND MOVES TO ANOTHER CONTROL ON THE SUB FORM. I have cases where after changing a sub form value and with the cursor still in the changed control the user then clicks on the main form or another form altogether. The update events are never raised; the changed data however is captured. I'm sure this behaviour is related to the SetFocus problem discussed on another recent thread to this board.
What's a way round this? Well you could in addition to local traps interrogate the OnDirty property for the sub form using the OnExit event for the control holding the sub form. If dirty then requery the sub form.