Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

    How to synchronise main and slave forms?

    I have a single table with numerous fields and records.


    My Main_Form is used to input a lot of individual fields' data and I'd like to have a Slave_Form that only displays a large Memo field that is tied to, and always synchronised with, the record displayed by the Main_Form (and vice versa).


    The Slave_Form has the Popup property set to True and its Modal property set to False so that the focus can be on, and entries made on, either form whilst they are both open. (The forms will be on different screens.)


    The Slave_Form will only be opened from the Main_Form, but can be closed by either a Close_Button on the Slave_Form, or automatically with the closing of Main_Form.


    Moving from one record to another will only be initiated from the Main_Form. (I have set the Slave_Form.NavigationButtons and Slave_For.RecordSelectors properties to False so they cannot be used to move between records from the Slave_Form.)


    My issue is how to keep the record displayed on the Main_Form and Slave_Form synchronised at all times.

    In anticipation, many thanks for the kind assistance that will point me in the right direction to resolve this issue.

    Cheers

    Trevor

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,175
    Thanks
    200
    Thanked 781 Times in 715 Posts
    BAB,

    Here's some code I use to sync up an Owner form with independent forms for Docks and Storage lots {each owner can have more than one of each}.
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim lCurOwnerID As Long
        Dim lRecCnt     As Long
        Dim zCondition  As String
       
        DoCmd.MoveSize 0, 0
        lCurOwnerID = Me.OwnerID
        zCondition = "[OwnerID] = " & Format(lCurOwnerID)
        
         lRecCnt = DCount("[Lot]", "Lots", "[OwnerID] = " & lCurOwnerID)
         DoCmd.OpenForm "frmAssignLots", acNormal
        
         lRecCnt = DCount("[StorageLotNo]", "StorageLots", "[OwnerID] = " & lCurOwnerID)
         DoCmd.OpenForm "frmAssignStorage", acNormal '***, , "OwnerID = " & lCurOwnerID
         If lRecCnt = 0 Then
           Forms![frmAssignStorage].Visible = False
         End If
        
         lRecCnt = DCount("[Dock]", "Docks", "[OwnerID] = " & lCurOwnerID)
         DoCmd.OpenForm "frmAssignDocks", acNormal '*** , , "OwnerID = " & lCurOwnerID
         If lRecCnt = 0 Then
           Forms![frmAssignDocks].Visible = False
         End If
        
         Forms![Switchboard].Visible = False
        
         Call cmdSync_Click
        
     End Sub                   'Form_Open()
    Code:
    Private Sub cmdSync_Click()
    
        Dim iOwnerID  As Integer
        Dim rst       As DAO.Recordset
        Dim lRecCnt   As Long
        Dim lCntr     As Long
       
        iOwnerID = Me.OwnerID
       
     '   MsgBox "OwnerID = " & Format(iOwnerID) & vbCrLf & vbCrLf & _
     '          "Forms: " & Format(Forms.Count)
       
        With Forms.frmAssignLots
            .SetFocus
            .OwnerID.SetFocus
            Set rst = .Recordset
            rst.FindFirst "OwnerID = " & iOwnerID
            If rst.NoMatch Then
                MsgBox "No Lot Records found for Owner ID: " & Format(iOwnerID)
            Else
     '        MsgBox "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
     '               rst.Name
             DoCmd.GoToRecord acDataForm, "frmAssignLots", acGoTo, rst.AbsolutePosition + 1
            End If
        End With
       
        With Forms.frmAssignDocks
            .SetFocus
            .OwnerID.SetFocus
            Set rst = .Recordset
            rst.FindFirst "OwnerID = " & iOwnerID
            If rst.NoMatch Then
     '           MsgBox "No Dock Records found for Owner ID: " & Format(iOwnerID)
                .Visible = False
                Me.cmdShowDocks.Caption = "Show Docks"
            Else
     '        MsgBox "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
     '               rst.Name
             DoCmd.GoToRecord acDataForm, "frmAssignDocks", acGoTo, rst.AbsolutePosition + 1
             .Visible = True
              Me.cmdShowDocks.Caption = "Hide Docks"
            End If
        End With
       
        With Forms.frmAssignStorage
            .SetFocus
            .OwnerID.SetFocus
            Set rst = .Recordset
            rst.FindFirst "OwnerID = " & iOwnerID
            If rst.NoMatch Then
     '         MsgBox "No Storage Records found for Owner ID: " & Format(iOwnerID)
              .Visible = False
              Me.cmdShowStorageLots.Caption = "Show Storage Lots"
            Else
     '        MsgBox "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
     '               rst.Name
              DoCmd.GoToRecord acDataForm, "frmAssignStorage", acGoTo, rst.AbsolutePosition + 1
              .Visible = True
            Me.cmdShowStorageLots.Caption = "Hide Storage Lots"
            End If
        End With
       
     '   Forms.frmAssignDocks.SetFocus '*** Move docks in front of storage
        Forms.frmOwnerInput.SetFocus
       
     End Sub                      'cmdSync_Click()
    Of course you'll have to subistutite your table names and field names and tie the code to the appropriate record events to do the updates. If you have problems understanding the code please post back.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2012-07-15)

  5. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Why not use a subform that is linked to the main form?

  6. The Following User Says Thank You to patt For This Useful Post:

    BygAuldByrd (2012-07-15)

  7. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi Patt,

    Thanks for the suggestion, but how do I implement a "subform"? I'm a newbie at this and still learning Access VBA and have never used subforms.

    Plugging away at learning...

    Cheers

    Trevor

  8. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,272
    Thanks
    130
    Thanked 1,153 Times in 1,062 Posts
    A subform is just a form that you design with a view to use it within another form. Common situations are those represented by a one to many relationships - the main form displays the main record (the one side) and the subform displays the (many) records related to the main record.
    To design a subform, you design it more or less as you design any other form. What you need to ensure is that the recordsources for the main and subform have a common field, that Access then uses to automatically relate the data shown in both forms - as the main record changes, the related records shown in the subform change too, without user intervention.

    You can have a subform that only shows one record related to the main form, but usually the subform shows multiple records. This means that usually subforms display in Continuous Form view or in Datasheet view.

    So, a subform is just built as any other form, ensuring that its datasource has at least one field in common with the main form.

    This MS articles explains it a bit more and shows how you can create subforms: http://office.microsoft.com/en-us/ac...101872705.aspx
    Last edited by ruirib; 2012-07-18 at 18:15. Reason: spelling

  9. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi Retired Geek,

    Many thanks for your post. It provided me with a way to be able to open a new Slave_Form from my Main_Form but it did not resolve my fundamental issue of maintaining synchronization between the two Forms when I moved from one record to the next. The field on Slave_Form remained unchanged

    In the process of working though your code I've simplified it to the following so others may find it easier to work through.

    I note:
    • on Main_Form are Textboxes for ID, Action_A and Action_B
    • on Slave_Form is a Textbox for Action_C
    • this code is place in Main_Form,
    • Slave_Form is opens automatically when Main-Form is opened
    The only way I found to ensure that Main_Form and Slave_Form remained synchronized was to force Slave_Form to close when I navigated from the away from the record that Main_Form was originally opened at or to close both forms and reopen Main_Form as a new record.

    Code:
    Option Compare Database
    
    
    Private Sub Form_Open(Cancel As Integer)
    
    
    '   Opens form [fmActionsMain] using data from table [Actions]
    '   Table [Actions} has the following fields:
    '       Primary Key:    [ID]        type AutoNumber
    '                       [Action_A]    type Long Integer
    '                       [Action_B]    type Text
    '                       [Action_C]    type Memo
    
    
    Dim lActionID As Long
    Dim lRecordCount As Long
    
    
    DoCmd.MoveSize 0, 0
    lAction_ID = Me.ID
     'MsgBox ("Form_Open() [1]..." & vbCrLf & vbCrLf & _
    '        "Made it this far...")
    
    
    lRecordCount = DCount("[Action_A]", "Actions", "[ID] =" & lAction_ID)
    DoCmd.OpenForm "fmActionsMain", acNormal
    'MsgBox ("Form_Open() [2]..." & vbCrLf & vbCrLf & _
    '        "Made it this far...")
    
    
    lRecordCount = DCount("[Action_A]", "Actions", "[ID] = " & lAction_ID)
    DoCmd.OpenForm "fmActionsSlave", acNormal
    If lRecordCount = 0 Then
        Forms![Actions (Slave Form)].Visible = False
    End If
    'MsgBox ("Form_Open() [3]..." & vbCrLf & vbCrLf & _
    '        "Made it this far...")
    
    
    Call cmdSyncSlaveForm_Click
     
    End Sub     '   ****END Private Sub Form_Open()
    
    
    Private Sub cmdSyncSlaveForm_Click()
    
    
    Dim iActionID As Integer
    Dim recordset As DAO.recordset
    Dim lRecordCount As Long
    Dim Counter As Long
    
    
    iActionID = Me.ID
    MsgBox ("Private Sub cmdSyncSlaveForm_Click() [1]..." & vbCrLf & vbCrLf & _
            "ID = " & Format(iActionID) & vbCrLf & vbCrLf & _
            "Forms: " & Format(Forms.Count))
            
    With Forms.fmActionsSlave
        .SetFocus
        .Action_ID.SetFocus
        Set rst = .recordset
        rst.FindFirst "ID =" & iActionID
        If rst.NoMatch Then
            MsgBox ("Private Sub cmdSyncSlaveForm_Click() [2]..." & vbCrLf & vbCrLf & _
                    "No ?? Records found for IS: " & Format(iActionID))
        Else
            MsgBox ("Private Sub cmdSyncSlaveForm_Click() [3]..." & vbCrLf & vbCrLf & _
                    "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & rst.Name)
            DoCmd.GoToRecord acDataForm, "fmActionsSlave", acGoTo, rst.AbsolutePosition + 1
        End If
    End With
           
    Forms.fmActionsMain.SetFocus
    
    
    End Sub     '   ****END Private Sub cmdSyncSlaveForm_Click()


    To resolve my fundamental issues of navigation synchronization and record updating I resorted to the following:

    On Main_Form:
    • add a Button to open Slave_Form using embedded macros that:
      • saved the content on Main_Form BEFORE opening Slave_Form (required when a new record was being created), THEN
      • opens Slave_Form
    • add Record navigation buttons using embedded macros that:
      • force to closing of Slave_Form before moving to a new record, THEN
      • moves to the next record
    • set the properties "Form.Record Selectors" and "Form.Navigation Buttons" to "No" (to prevent the use of the navigation buttons on the bottom of the Main_Form - all inter-record navigation to be done using the navigation buttons on Main_Form).
    • add a Button to close Main_Form using embedded macros that:
      • closes Slave_Form, THEN
      • closes Main_Form
    On Slave_Form:
    • add a Button to close Slave_Form using embedded macros
    • set the properties "Form.Record Selectors" and "Form.Navigation Buttons" to "No" (to prevent the use of the navigation buttons on the bottom of the Slave_Form - all inter-record navigation to be done using the navigation buttons on Main_Form).
    • add the following code to display the correct field from the record selected on Main_Form (Private Sub Form_Load()) and to save any changes made to the field directly to the underlying Table whilst Slave_Form is open (Private Sub Form_Close()):
    Code:
    Option Compare Database
    
    
    Public iMainFormRecordID As Integer
    
    
    Private Sub Form_Close()
    
    
    Dim dbDatabase As Object
    
    
    Dim rstActions As DAO.Recordset
    
    
    Set dbDatabase = CurrentDb()
    
    
    Set rstActions = dbDatabase.OpenRecordset("Actions", dbOpenDynaset)
    
    
    
    
    rstActions.FindFirst "ID = " & iMainFormRecordID
        If rstActions.NoMatch Then
            MsgBox ("fmActionsSlave Form_Close() [1]... " & vbCrLf & vbCrLf & _
                    "No record found for iMainRecordFormID: " & Format(iMainFormRecordID))
                    
        Else
            'MsgBox ("fmActionsSlave Form_Close() [2]... " & vbCrLf & vbCrLf & _
            '        "Record found for iMainRecordFormID: " & Format(iMainFormRecordID) & vbCrLf & _
            '        "Record Number: " & Format(rstActions.AbsolutePosition) & vbCrLf & _
            '        "Table: " & rstActions.Name)
            rstActions.Edit
            rstActions("Action_C").Value = Me.Tbx_Action_Memo
            rstActions.Update
            
        End If
    
    
    
    
    If CurrentProject.AllForms("fmActionsMain").IsLoaded Then
        Forms![fmActionsMain].Refresh
    End If
    
    
    End Sub     '   ****End Private Sub Form_Close()
    
    
    Private Sub Form_Load()
    
    
    If CurrentProject.AllForms("fmActionsMain").IsLoaded Then
        iMainFormRecordID = Forms![fmActionsMain].[Action_ID]
    Else
        MsgBox ("Slave form MUST be opened from the Main Form")
        Exit Sub
        
    End If
        
    
    
    Me.Tbx_ActionID = iMainFormRecordID
    
    
    Dim rRecordset As DAO.Recordset
    
    
    With Forms.fmActionsSlave
        .SetFocus
        .Tbx_ActionID.SetFocus
        Set rst = .Recordset
        rst.FindFirst "ID = " & iMainFormRecordID
        If rst.NoMatch Then
            MsgBox ("fmActionsSlave Form_Open() [1]... " & vbCrLf & vbCrLf & _
                    "No record found for iMainRecordFormID: " & Format(iMainFormRecordID))
        Else
            'MsgBox ("fmActionsSlave Form_Open() [2]... " & vbCrLf & vbCrLf & _
            '        "Record found for iMainRecordFormID: " & Format(iMainFormRecordID) & vbCrLf & _
            '        "Record Number: " & Format(rst.AbsolutePosition) & vbCrLf & _
            '        "Table: " & rst.Name)
            Me.Tbx_Action_Memo = [Action_C]
        End If
    End With
                    
    End Sub     '   ****END Private Sub Form_Load()
    The major failing of this solution is that the Slave_Form has to be re-opened whenever a move from one record to another is made.

    If anyone has any suggestions for improvement I welcome them with a very open mind.

    Cheers

    Trevor

  10. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I have been away for a few days travelling from Melbourne to Alice Springs here in Australia.
    What i suggested is by far the simplest way to do what you want, and Ruirib has explained it well.
    Why don't you put together in a database the 2 or more tables concerned and send it to us to show you.
    Let us know what the main form table is and what the subform table is.
    Don't forget to zip that database prior to sending it.

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,175
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    You can use the Current event to run the sync process.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. #9
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi Patt,

    Trust you enjoyed you Melbourne to Alice Springs trip. I did a similar trip earlier in the year and had trouble finding the "Red Centre", it was all green

    I looked at your suggestion to use a Subform when you first made it. I'm pretty much a Newbie at Access VBA so did some googling on the topic, some experimentation, and from what I could work out a Subform is a Form within a Form and is displayed in the same window as the MainForm. My experiments with this approach couldn't work out a way to separate the Subform from my MainForm so I could put the Subform on a different monitor, which is one of my specific requirements. Thus I didn't pursue it further. I also note that all related data to be displayed on the MainForm and SlaveForm is contained in a record within a single table, not multiple tables. Also the Field from the Record I want on the SlaveForm is a Memo field, and that produced further problems with Record Locking, hence the approach I've taken.

    If you can show me how to separate a Subform form its parent Form with a bit of sample code I'd like to look at this option further.

    Thanks for your input and advice.

    Trevor

  13. #10
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Your suggestion to use "Current Event" looks interesting. I looked at the link you included and while it suggests the approach, I haven't been able to work out the appropriate way to apply it. Can you post a simple code sample based on a single table and 2 forms that remain synchronized to the same record as record navigation moves between records?

    Your assistance is much appreciated by this follicly challenged white head.

    Cheers

    Trevor

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,175
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    Here's the code you need of course you'll have to substitute your form names and ID field where appropriate.
    SyncTestForms.JPG
    Note: you can set the ID field in the frmSyncTest2 to not visible if you don't want the visual verification.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Current()
    
        Dim iID       As Integer
        Dim rst       As DAO.Recordset
        
        iID = Me.ID
        
        With Forms.frmsynctest2
            .SetFocus
            .ID.SetFocus
            Set rst = .Recordset
            rst.FindFirst "ID = " & iID
            .Width = 3#
        End With
        
        Forms.frmSyncTest1.SetFocus 'Return focus to main form
        
    End Sub
    
    Private Sub Form_Load()
    
        'Open the Memo form when main form loads to prevent
        'Error when Form_Current code runs.
        
        DoCmd.OpenForm "frmSyncTest2", acNormal
            
    End Sub
    Note 2: You need to set references in the VBA project for this to work.
    VBAReferences.JPG
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-07-19 at 12:27.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2012-07-19)

  16. #12
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Thanks heaps that solution has greatly simplified by coding and it appears to have solve another issue I had with the length of the content in my Memo which had been throwing an error and required the closing of the MainForm before saving any changes made in the Memo field.

    I also found it necessary to repeat the "Private Sub Form_Current()" in the SlaveForm to ensure that if the record navigation controls in the SLaveForm were used the MainForm was kept in synch with it. Now it works a treat. I've attached a copy of my modified version of your SuchTestDB for the benefit of other working on this problem.

    Many thanks again for excellent assistance.

    Cheers

    Trevor
    Attached Files Attached Files

  17. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,175
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    You're welcome!

    Your OP said:
    Quote Originally Posted by BygAuldByrd View Post
    Moving from one record to another will only be initiated from the Main_Form. (I have set the Slave_Form.NavigationButtons and Slave_For.RecordSelectors properties to False so they cannot be used to move between records from the Slave_Form.)
    That's why I didn't include the code in the Memo form. But I'm glad you got it sorted out.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  18. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,175
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    I just discovered a problem with the code above as I was trying to integrate the concept into the code I had originally posted to get rid of a Sync button on my main form. If you try to add a record the line iID = Me.ID will generate an error as Me.ID will be equal to Null you can test for this by using the line If IsNull(Me.ID) Then Exit Sub before the assignment statement. You want to get out since there is no data to synchronize.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  19. #15
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Yes, I found that problem too when I tried to navigate beyond the first or last record. Accordingly I have already inserted the IsNull() trap as you've suggested.

    Thanks for picking it up too.

    Cheers

    Trevor

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •