How to synchronise main and slave forms?

Topic

New Reply

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

Reply | Quote

Replies

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

[/LIST]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

Reply | Quote

Why not use a subform that is linked to the main form?

Reply | Quote

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

Reply | Quote

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/access-help/create-a-form-that-contains-a-subform-HA101872705.aspx

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Why do you wish to separate the memo field onto another form? Why not have it all on the one form?

Reply | Quote

Trevor,

You can use the Current event to run the sync process. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Trevor,

Here’s the code you need of course you’ll have to substitute your form names and ID field where appropriate.
31500-SyncTestForms
Note: you can set the ID field in the frmSyncTest2 to not visible if you don’t want the visual verification.:cheers:

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Trevor,

You’re welcome!

Your OP said:

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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 [noparse]iID = Me.ID[/noparse] will generate an error as Me.ID will be equal to Null you can test for this by using the line [noparse]If IsNull(Me.ID) Then Exit Sub[/noparse] before the assignment statement. You want to get out since there is no data to synchronize. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

I think the original post indicated there was only one table.

What the user wants to do is disply one record but not including a memo field in one form on the left hand screen, and the memo field in a second form on the right hand screen.

The simplest would be to put in the OnCurrent Event of the Main form
Forms!SlaveForm.Filter=chr(34) & “OwnerID=” & me.OwnerID & chr(34)

Reply | Quote

I know what he wants to do, i was asking WHY.

Reply | Quote

Hi Patt,

The reason is pretty simple, actually 2 main reasons:

1 The memo field has a lot of descriptive information in it that I wish to have visible, and
2 The Main_Form is already full with information such that there is no room left on my Main_Form for the memo field when viewed on my main monitor.

Putting the memo field in a separate form allows me to move it to a second monitor and have all (well most – some of the memo field data still needs to be scrolled) of the information related to the record on display.

There is also a less significant reason, the memo field does not need to be displayed every time a record is displayed, it only needs to be displayed when there is memo info that goes with the record that is added after certain external actions have taken place based on the information generated in and added to the Main_Form.

I trust that satisfies your curiosity.

Cheers

Trevor

Reply | Quote

Hi Cronk,

Thanks for your suggestion to use the OnCurrent Event of the Main_Form, which I understand will always display my Slave_Form. I don’t want the Slave_Form displayed every time I open a record. I only want the Slave_Form displayed when I wish added to, or review, the memo data field. Accordingly, opening my Slave_Form is triggered by a Button on my Main_Form.

In passing, what is the purpose of the “chr(34)” (twice) in the assignment?

Thanks again for your input

Cheers

Trevor

Reply | Quote

Why don’t you use a Tab control showing the main stuff on the 1st tab and the memo field on the 2nd tab? That way there is no need for code at all.

The chr(34) is the double quotes (“), they are used to surround a text field, they are not required for a numeric field.

Reply | Quote

Hi Patt,

As I’ve previously stated, and this is the whole point, I do not have enough screen space to display all the information I want displayed on one monitor at the same time. Using Tabs means I can look at either one lot of info or the other lot – I can’t look at it all at the same time, and this is a fundamental requirement. As it is, I already use Tabs for information that does not need to be looked at concurrently.

If you can provide a Tab solution that puts the Tab_2 information on a selectable alternate monitor (I have up to 4 connected to my computer) while Tab_1 is still fully displayed on my main monitor, I’d appreciate your posting a working code sample that does this.

End of discussion on why I wish to do this. Just accept that it is my requirement.

Trevor

Reply | Quote

I missed the point re multiple monitors, i was just interested, i will butt out

Reply | Quote