My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Synchronized Subforms, Office 2010
|
|
 Sep 15 2011, 04:15 PM
Post
#1
|
|
|
UtterAccess VIP Posts: 9,304 From: Wisconsin  |
I've got a main form with two wide subforms. The first subform holds the data entry controls. The second one holds a bunch of calculated controls. I'd rather not have a single, really wide subform, where the user needs to scroll both horizontally to see the data entry/calculated values, and vertically to see more records.
So, I want to attempt synchronized subforms. I've already tried downloading a demo from Roger's Access Library - but it displays a "lost synchronization" error as soon as the form opens, even if you close and re-open, as it asks you to do. Has anyone successfully done this? If not, here's my idea for proceeding: Bind both subforms to a "top X values that are greater than Y" sort of query. So, if X is 5, I could display records 1-5 or 7-12, for example. Then, I'd have to create custom "scrolling" controls - maybe a set of "Goto first/goto prev/goto next/goto last" command buttons. Each button would alter the Y value that drives the query. But before proceeding, I'll see if anyone has done this with VBA that somehow reads the scroll position of subform A, and sets a matching scroll position on subform B. Dennis |
 |
 
|
|
Sep 15 2011, 04:27 PM
Post
#2
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
Hi Dennis,
Are you saying that you want to scroll once and both subforms move vertically? Just curious... |
|
|
|
|
Sep 15 2011, 04:49 PM
Post
#3
|
|
|
UtterAccess VIP Posts: 9,304 From: Wisconsin |
Yeah, if the user scrolls the data entry subform down three records, I want the calculated values subform to scroll down three records as well, so the user can see the corresponding calculations for the data entry that's currently visible.
To simplify things, I would only want a scrollbar on the data entry subform. The calculated values would be "view only", with no user interaction. Dennis |
|
|
|
|
Sep 15 2011, 05:11 PM
Post
#4
|
|
|
Access Wiki and Forums Moderator Posts: 48,113 From: SoCal, USA |
Hi Dennis,
I see. I've seen somebody try that before but I don't remember what happened in the end. Have a look at this previous discussion. Just my 2 cents... |
|
|
|
|
Sep 16 2011, 03:15 AM
Post
#5
|
|
|
UtterAccess VIP Posts: 1,830 |
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: CODE 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 Of course, adjust the names of the subform controls and the IDs to those of yours. /gustav |
|
|
|
|
Sep 16 2011, 08:18 AM
Post
#6
|
|
|
UtterAccess VIP Posts: 9,304 From: Wisconsin |
DBGuy,
The final download in your linked discussion looks like it may be just what I'm looking for. I'll start with that one, but I'll keep this discussion bookmarked in case I want to try something else... Gustav, Thanks for the VBA - it's great to have options! Dennis |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 25th May 2013 - 02:21 PM |