?
Solved

Synchronize tabbed subform

Posted on 2011-09-16
10
Medium Priority
?
417 Views
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.
Sample-Database-Synchronize-Subf.mdb
0
Comment
Question by:AndreasHermle
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 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 ...
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 664 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.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36552121
Then select your new record on the main form, and all subforms will reflect that choice.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:AndreasHermle
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.

0
 
LVL 85
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.
0
 

Author Comment

by:AndreasHermle
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,
Andreas

0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 668 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
  Else
    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
      rst.Close
    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
      rst.Close
    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.

/gustav
0
 

Author Comment

by:AndreasHermle
ID: 36557416
Hi gustav:

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

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
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
0
 

Author Closing Comment

by:AndreasHermle
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

621 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