?
Solved

Synchronize tabbed subform

Posted on 2011-09-16
10
Medium Priority
?
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 51

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

771 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