Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Synchronize tabbed subform

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
Andreas Hermle
Asked:
Andreas Hermle
  • 5
  • 2
  • 2
  • +1
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Helen FeddemaCommented:
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
 
Helen FeddemaCommented:
Then select your new record on the main form, and all subforms will reflect that choice.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Andreas HermleTeam leaderAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Andreas HermleTeam leaderAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Andreas HermleTeam leaderAuthor Commented:
Hi gustav:

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

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
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
 
Andreas HermleTeam leaderAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now