By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,881 Members | 714 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,881 IT Pros & Developers. It's quick & easy.

events within child subforms from a parent master form

P: 8
I have a question concerning the need to trigger events within a "child" subform which is itself enbedded within a master "parent" form and which is accessible via a tab in the parent form. Becuase this is all very difficult to explain in words, please bear with me as I endevour to explain what it is I am trying to do. It would be helpful if I could attach a graphics file to this posting that would help explain what it is I'm trying to achieve in the form of a daigram but as this isn't possible, please bear were me. Before I continue, I should just say that I am a newcomer to MS Access and to VBA programming in general.

I am seeking to build an MS Access application that will hopefully be accesible via a single page. By page, I simply mean, single window. Because my application uses a large number of different forms both to input and display data, I have decided to create a single "page" and have multiple tabs within it. It is within each of these tabbed areas that I want to embed a different subform. Each subform does of course have it's own specific function. For the sake of clarification, I shall call the single displayed page within a single window, the "parent" form and each of the subforms within each of the respective tabbed areas, a "child" form. (I hope this all makes sense as I did warn you that explaining all of this in words was going to be complicated ... )

Now becuase it is the case that there are some "global functions" (events) that I would like to apply to all of my subforms, I have decided to divide the parent form into threee succient areas or "frames" as I shall call them: Top, main and bottom respectively. To be clear, within the "main" frame, the tabbed areas exist with their corresponding subforms. Within the "top" and "bottom" frames, button designed to invoke global functions exist.

What I want to do is to have some record navigation and form operation buttons appear within the top and bottom frames that, when clicked on, will trigger an event within the particular subform that appears at that time depending upon which tab within the main frame has first been selected.

As I said, the buttons I want to appear in the top and bottom frames are record navigation buttons (i.e., go straight to the next, previous first or last record) and form operation buttons - specifically: "print current record", "edit current record", and "delete current record"). What I'm trying to achieve is to have these functions operate irrespective of what form is currently being displayed in whatever tab has been selected.

The problem I am having is that although I can get all of my subforms to work perfectly when used in isolation, and although I can my main frame buttons to work perfectly on data displayed within the parent form as a whole, I cannot get the two to work together when combined in the manner described. What I therefor want to know is how to achieve this.

To help clarify, I should tell you that I have a total of 6 tabs within the parent form. Becuase I have 6 tabs, I also have 6 corresponding child subforms. You may wonder why I bothered to create these child subforms first and embed them within the parent form and the reason is that it was easiest to develop each of them in insolation and ensure that each of them work. My thinking was that having done so, I could then just drag and drop them into the parent form and hey presto, everything would work as planned.


1. Tab Control name: TabCtl1
2. Number of Tabs/child subforms: 6
3. Tab/child subform Captions: Page1, Page2, Page3, Page4, Page5, Page6
4. Tab/Child Form Names: pgeOne, pgeTwo, pgeThree, pgeFour, pgeFive, pgeSix
5. The record navigation button functionality has been achieved by means of a module called: FormNavigationButtons

Here follows the code I am using for the 'FormNavigationButtons' module:

Expand|Select|Wrap|Line Numbers
  1. Function GotoPrevious()
  3.     'if an error happens, we must be on the first record
  4.     On Error GoTo OnFirstRecord
  6.     'got to the first record
  7.     DoCmd.RunCommand acCmdRecordsGoToPrevious
  8.     Exit Function
  10. OnFirstRecord:
  12.     'if an error has occured, we get here
  14.     'display a message to say what the problem is ...
  15.     MsgBox "Already on the first record!"
  17.     '... and carry on immediately after the offending line
  18.     Resume Next
  20. End Function
  22. Function GoToNext()
  24.     'if an error occurs, logically we must be on the last record
  25.     On Error GoTo OnLastRecord
  27.     'go to the next record
  28.     DoCmd.RunCommand acCmdRecordsGoToNext
  29.     Exit Function
  31. OnLastRecord:
  33.     'if an error has happened, we get here
  35.     'display a message to say what the problem is ...
  36.     MsgBox "Already on last record!"
  38.     '... and carry on immediately after the offending line
  39.     Resume Next
  41. End Function
  43. Function GoToLast()
  45.     'Because we're not at either the first or last record, we can execute code directly
  47.     'go to the last record
  48.     DoCmd.RunCommand acCmdRecordsGoToLast
  49.     Exit Function
  51. End Function
  53. Function GotoFirst()
  55.     'go to the first record
  56.     DoCmd.RunCommand acCmdRecordsGoToFirst
  57.     Exit Function
  59. End Function
Now I know how to give focus to the tabs but don't know how to apply the following code to that that I have already:

Expand|Select|Wrap|Line Numbers
  1.    Method 1:
  2.    Me!TabCtl1.Pages(0).SetFocus - set Focus to the 1st Tab
  4.     Method 2:
  5.     Me!TabCtl1.Pages!pgeThree.Setfocus - set Focus to 3rd Tab
  7.     Method 3:
  8.     Me!TabCtl1.Pages("pgeFour").Setfocus - set Focus to 4th Tab
  10.    Method 4:
  11.    Me!TabCtl1.Value = 4 - makes the 5th Tab active
  13.    Method 5:
  14.    SendKeys "%a" - makes the 6th Tab active (a is a 'Hot Key' in the Caption of this Tab.
Please can someone tell me how I would need to alter my module code so that it resulted in what I'm looking to achieve.
Dec 7 '07 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 2,653
Hi, Harlequin.

Try the following code. Hope it is quite self-explaining.

Expand|Select|Wrap|Line Numbers
  2. Private Sub btnNext_Click()
  4.     With Me.TabCtl0 'get reference to tab control
  5.         ' get reference to an active page subform control
  6.         ' supposed it is the first one or even the only one
  7.         With .Pages(.Value).Controls(0)
  8.             .SetFocus
  9.             'trap an error when tries to go to a record after the last one
  10.             On Error GoTo EndOfRecords
  11.             DoCmd.GoToRecord , , acNext
  12.         End With
  13.     End With
  15.     Exit Sub
  17. EndOfRecords:
  19. End Sub
  23. Private Sub btnPrev_Click()
  25.     With Me.TabCtl0
  26.         With .Pages(.Value).Controls(0)
  27.             If .Form.CurrentRecord = 1 Then Exit Sub
  28.             .SetFocus
  29.             On Error GoTo BeginOfRecords
  30.             DoCmd.GoToRecord , , acPrevious
  31.         End With
  32.     End With
  34.     Exit Sub
  36. BeginOfRecords:
  38. End Sub
Dec 7 '07 #2

P: 8
Hi Fish,

Thank you for very helpful posting. I'm glad my literal explanation was sufficiently clear that you understood what it is I'm trying to achieve.

I do however have one followup question for you. At the momement, I've put all the VBA code to work with buttons appearing within the top and bottom frames into modules - thinking that this would be the easiest way to reuse the code should I need to. You'll note also that my module code uses function procedures rather than sub proceduces. My thinking here was that since a function always performs and an action and returns a particular result, and that that is precisely what I wish to achieve with these buttons, that that was the best way to proceed.

I note however that your code uses procedures and I'm unsure as to (a) where to call them from and (b) where to associate them with my parent form.

Can you clarify this please?

Many thanks,

Dec 8 '07 #3

P: 8
Apologies for following up on my last post but I forgot to add that if at all possible, I'd like to adapt my existing module function code as this works very well but only with the parent form used in isolation of the child forms.

Basically, what i want is to adapt my existing code to work within the secenario I've described. It is of course about how to give focus to the child subforms within the parent form.
Dec 8 '07 #4

Expert 2.5K+
P: 2,653
Hi, Harlequin.

I have even better solution for your problem. What about using native Access Navigation Bar on the main form to browse records on a subform on an active Tab control?

Here is very simple code to dynamically connect main form to an active subform RecordSet.

In main form module
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     ConnectMainFormToActiveSubform
  3. End Sub
  5. Private Sub TabCtl0_Change()
  6.     ConnectMainFormToActiveSubform
  7. End Sub
  9. Private Sub ConnectMainFormToActiveSubform()
  11.     With Me.TabCtl0
  12.         Set Me.Recordset = .Pages(.Value).Controls(0).Form.Recordset
  13.     End With
  15. End Sub
Dec 8 '07 #5

Post your reply

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