Synchronize tabbed subform

Posted on 2011-09-16
Last Modified: 2012-05-12
Dear Experts:

Attached please find a simple sample database.

I got a main form (frm_customer_project) with one (1)  tabbed subform (3  tab pages: dividing the subform into different sections/set of fields ).

Now here come my questions:

1. how can I SYNCHRONIZE the 3 tab pages ?, i.e. if I move to the second record of the first tab page (of the subform) and then move on to the second tab page (of the same subform), the corresponding record, (ie. record no. 2 on the second tab page) is not shown. The first record is still shown.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

P.S. I have attached a simple sample data base for your convenience.
Question by:AndreasHermle
  • 5
  • 2
  • 2
  • +1
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 36552039
You can use the RecordsetClone method to do this. To do that, you'd have to run code like this from the AfterUpdate event of each of your Subforms:

Dim rst As DAO.Recordset
Set rst = Me.Parent.NameOfYourOtherSubformCONTROL.Form.RecordsetClone

rst.FindFirst "YourIDValue=" & Me.YourIdValue

If Not rst.NoMatch Then
  Me.Parent.NameOfYourOtherSubformCONTROL.Form.Bookmark = rst.Bookmark
End If

Note the NameOfYourOtherSubformCONTROL. This is the name of the Subform Control on your main form, and may or may not be named the same as the form you're using as a Subform. Be careful with this, since it's quite tricky ...
LVL 31

Assisted Solution

Helen_Feddema earned 166 total points
ID: 36552111
It would be much simpler to just link all the subforms on Customer_ID (for both Master and Child fields).  You have ID for the master field, which won't work for linking.
LVL 31

Expert Comment

ID: 36552121
Then select your new record on the main form, and all subforms will reflect that choice.

Author Comment

ID: 36552279
Dear both,

first of all thank you VERY MUCH for your quick and professional support. I really, really appreciate it, but ...
... As a matter of fact, I am at loss somewhat.

I am a newbie at Access and do not know how to put your answers to use ...

LSM Consulting:
1. I am afraid to tell you that I will not be able to implement your code into the VBA editor. I know how to call it up but no more.
2. Your answer: .... "Note the NameOfYourOtherSubformCONTROL. This is the name of the Subform Control on your main form, and may or may not be named the same as the form you're using as a Subform. Be careful with this, since it's quite tricky ... "

My answer: ... Are you referring to the names of the source objects of the tab pages? ie. sfrm_main_1; sfrm_main_2 and sfrm_main_3.
I may need only one subform since they all refer to the table 'tblproject'.

Helen: It would be great if you could apply your suggestions to my database. I am not quite sure whether I would do the right thing.

Again, your valuable help is much appreciated.

Thank you very much in advance.

Due to the time difference, I will go to bed now and have a look at the forum only tomorrow morning.

LVL 84
ID: 36553659
The concept of EE is that we help YOU - not that we do your work for you. I appreciate that it can be difficult to learn this, but when you do the work yourself, you arm yourself with the knowledge to resolve issues like this in the future.

Helen's suggestion would be quite a bit simpler to implement, if it will work. To add additional fields to the Master/Child linking fields, open the form in Design view, and select the Subform control. In the Properties dialog, find the Master and Child link properties, select one of them, then click the Build button (the button to the right of the box). This opens a dialog where you can select one or more fields in the Parent and Child containers which will be used to "link" those two items together.

In response to this:

" Are you referring to the names of the source objects of the tab pages? ie. sfrm_main_1; sfrm_main_2 and sfrm_main_3. "

Tab pages don't have a Source Object. Subform Controls do.

The Source Object of a Suform control is the Form that you're using as a Subform. The Subform CONTROL is just a container control on the form, and does nothing more than provide a "host" for your subform. It contains only a few events, and just a handful of unique properties - one of which is the Source Object.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Comment

ID: 36553716
Hi lsm:

thank you very much for your help. I will go thru your statements and may hire you for a fee if I do not get any further. (of course only if you want / and or have time) . I ran quickly thru your profile and saw that you also can be hired.

Regards and have a nice weekend,

LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 167 total points
ID: 36554634
Create a textbox on the main form.
Set its ControlSource =SyncSubs([sub1]![ID],[sub2]![ID])
where sub1 and sub2 are the names of the subform controls (not the subformes themselves) and ID is the primary key of each subform.

Then under menu:
Tools, References
check that a reference to DAO exists.

Now, create this function in the code module of the main form:

Private Function SyncSubs( _
  ByVal varID1 As Variant, _
  ByVal varID2 As Variant) _
  As Variant

  Dim rst         As DAO.Recordset
  Static varIDc1  As Variant
  Static varIDc2  As Variant
  Dim bmk         As Variant
  Dim varID       As Variant
  If IsNull(varID1) Or IsNull(varID2) Then
    ' New record. Don't sync.
  ElseIf varID1 = varID2 Then
    ' Initial setting.
    varIDc1 = varID1
    varIDc2 = varID2
    If varID1 <> varIDc1 Then
      ' sub1 has moved. Sync sub2.
      Set rst = Me!sub2.Form.RecordsetClone
      rst.FindFirst "ID = " & Me!sub1.Form!ID
      If Not rst.NoMatch Then
        bmk = rst.Bookmark
        Me!sub2.Form.Bookmark = bmk
        varID = varID1
      End If
    End If
    If varID2 <> varIDc2 Then
      ' sub2 has moved. Sync sub1.
      Set rst = Me!sub1.Form.RecordsetClone
      rst.FindFirst "ID = " & Me!sub2.Form!ID
      If Not rst.NoMatch Then
        bmk = rst.Bookmark
        Me!sub1.Form.Bookmark = bmk
        varID = varID2
      End If
    End If
  End If
  If Not IsEmpty(varID) Then
    varIDc1 = varID
    varIDc2 = varID
  End If
  Set rst = Nothing
  SyncSubs = varID

End Function

Open in new window

Of course, adjust the names of the subform controls and the IDs to those of yours.


Author Comment

ID: 36557416
Hi gustav:

thank you very much for your great support. I will test it tomorrow and let you know.

Regards, Andreas

Author Comment

ID: 36563132
Dear all,

I require some more time to sort things out. I let you know as soon as possible.

Again, thank you very much for your great help.

Regards, Andreas

Author Closing Comment

ID: 36707888
Dear all,

thank you very much for your great and professional help. I finally could put your solutions to use and after tweaking they all worked the way I want. Great jobs! Since all of your answers led me to the 'right' way I suggest splitting the points. Again, thank you very much for your valuable help.

Regards, Andreas

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Run Time Error 3075 15 47
Run Time Error 3071 26 41
Access 2010 Query Syntax 5 26
ensure than when a form opens it always starts with a NEW record 2 20
In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now