UtterAccess Discussion Forums > Synchronized Subforms

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

doctor9

Sep 15 2011, 04:15 PM

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

theDBguy

Sep 15 2011, 04:27 PM

Hi Dennis,

Are you saying that you want to scroll once and both subforms move vertically?

Just curious...

doctor9

Sep 15 2011, 04:49 PM

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

theDBguy

Sep 15 2011, 05:11 PM

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

Gustav

Sep 16 2011, 03:15 AM

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

doctor9

Sep 16 2011, 08:18 AM

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

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.