Solved

Synchronize tabbed subform

Posted on 2011-09-16
10
405 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 84

Accepted Solution

by:
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 ...
0
 
LVL 31

Assisted Solution

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

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 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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 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
  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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 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

21 Experts available now in Live!

Get 1:1 Help Now