Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25

    Synchronise Two Related Subforms that are both displayed as data sheets.

    Here is what my form looks like.


    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	111.7 KB 
ID:	32208

    I want the record on the Proposed lighting subform to have "focus" and come into view, when any field in a record from the first subform is selected. The Main Form is bound to tblProperties and the two subforms' Link Master Fields are both set to txtProperties. Currently, when I use the combo box to select a property, the two subforms correctly show records from the selected property. The problem is that when I go back and forth between Existing Lighting and Proposed lighting subforms, it is very easy to enter data into the wrong field. Ideally, the synchronization would work in both directions, keeping both tables in synch at all times.



    Does anyone have any suggestions?

    Thank you,

    Jonathan

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Mabjro,

    Here is what I'm doing in one of my older applications (this goes into the upper subform):

    Private Sub Field1_Enter()
    On Error Resume Next
    Me.Parent.Form.sfrmBottomSubform.Form.Controls("Fi eld1").SetFocus
    Me.Field1.SetFocus
    End Sub
    Private Sub Field2_Enter()
    On Error Resume Next
    Me.Parent.Form.sfrmlBCS_Profiles_Import.Form.Contr ols("Field2").SetFocus
    Me.Field2.SetFocus
    End Sub
    Private Sub Field3_Enter()
    On Error Resume Next
    Me.Parent.Form.sfrmBottomSubform.Form.Controls("Fi eld3").SetFocus
    Me.Field3.SetFocus
    End Sub
    Private Sub Field4_Enter()
    On Error Resume Next
    Me.Parent.Form.sfrmBottomSubform.Form.Controls("Fi eld4").SetFocus
    Me.Field4.SetFocus
    End Sub
    Private Sub Field5_Enter()
    On Error Resume Next
    Me.Parent.Form.sfrmBottomSubform.Form.Controls("Fi eld5").SetFocus
    Me.Field5.SetFocus
    End Sub

    Hope it helps!

    Cheers,
    Vlad

  3. #3
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Vlad,

    Thank you so much! I will give it a try and post back the results.

    Jonathan

  4. #4
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Vlad,

    I have to admit that the code is just slightly above my experience level. However, I think I know enough to ask the right questions. For the sake of discussion, here are my variables.
    Main Form Name = frmProperties
    Upper sfrm = sfrmExistingLighting
    Lower sfrm = sfrmNewLighting
    The first field on the upper sfrm = txtBuilding
    The first field on the lower sfrm = txtBuilding

    I broke out your first event sub and adjusted it as follows.

    Private Sub txtBuilding_Enter()
    On Error Resume Next
    Me.Parent.Form.sfrmNewLighting.Form.Controls("txtB uilding").SetFocus
    Me.txtBuilding.SetFocus
    End Sub

    I am sure it is all wrong as I just started dabbling in Access again after years of not using it. Admittedly, I was no expert back then either.

    Thank you,
    Jonathan

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Looks good Jonathan, no space between txtB and uilding (Me.Parent.Form.sfrmNewLighting.Form.Controls("txtBuilding").SetFocus) I know my post ended up with those spaces there when I paste it in. The idea is to add similar code to all the controls in the upper form to set the focus to the corresponding control in the lower form (even if they don't have the same name). You can add similar code but reversed in the lower subform to sync the upper one if you navigate the lower one.

    Cheers,
    Vlad

  6. #6
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Thank you, I will let you know.

    Jonathan

  7. #7
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Vlad,
    Thank you for pointing me in the right direction. I can not get it to work, but I think that is because the main form has a control source and the subforms are already filtered on the value of the property from the property dropdown box on the main form. Its hard to describe because I am not exactly sure where the problem is. However, you did point me in the right direction and I am looking forward to learning much more about the procedures / methods that you have shared with me. The thing that is really cool about Access is that if you have a clear idea of what you want to achieve, you can learn a ton of stuff trying to get there. Unfortunately, I am too old to aspire to be a developer some day.
    Thank you for your help!

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Jonathan,
    The fact that the subforms are filtered should not impact their navigation. Can you upload a stripped version of your db here and I can have a look if you want. On my website (forestbyte.com on the MS Access Utilities page) I have a free utility that empties the contents of select tables (or all tables) in a db to help you get a "demo" version quickly (make sure you run it on a copy ).
    Cheers,
    Vlad

  9. #9
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Vlad,

    Thank you for such a kind offer. I just created the database a week or two ago, so the data is minimal and not of a proprietary nature. Its only 9092 kb. Please see the attached. Now all I have to do is figure out how to upload here.

  10. #10
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25

    Upload

    Vlad,

    The database stripper gave me a version error. Please see the attached compressed database.

    Thank you,
    Jonathan
    Attached Files Attached Files

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Jonathan,

    Here is your updated database, give it a try and let us know. Please note that I have not added code for all the fields, you can follow the pattern. If you need to navigate in the bottom form you need to add similar code but "reversed" (reference the upper subform). I also replaced a few text box control names in both forms to make it easier to understand the code (some were called Text33 or Text37 which is not useful at all when trying to write repetitive code like this.

    Cheers,
    Vlad
    Attached Files Attached Files

  12. #12
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Vlad,
    Thank you. I am on the road today, but I will try it first thing Monday morning as I will not be back into the office or be near access before then. Thanks again and i will let you know.

  13. #13
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Vlad,

    I made it back to the office. Great work! I really appreciate it. It is an excellent start. The focus is synchronized from the field on one subform, to the field on the other subform. The only problem is that the records do not synch. Currently if I click the first field in record 3, it will synch to the first field in record 1. I imagine that I can synch the records by using goto record or something similar... I will work at it.

    Thank you again!
    Jonathan

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Jonathan,

    I updated the code to sync to the same record as well (please note that I had to add the primary key - ID- to both forms and hide it)
    Attached Files Attached Files

  15. #15
    mabjro is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    25
    Solved... Thank you so much Vlad! My true expertise is in AutoCAD. If you should ever find yourself needing a quick drawing, sent me a private message and I would be happy to do it for you. Thanks again!
    Jonathan

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-29-2015, 01:40 AM
  2. Multiple Subforms with Related Data
    By funkymuppet in forum Forms
    Replies: 1
    Last Post: 04-01-2014, 08:40 PM
  3. Reports from sub-data sheets
    By sdc1234 in forum Reports
    Replies: 1
    Last Post: 06-11-2013, 09:42 PM
  4. Architects Room Data Sheets
    By ThatSkepTicGuy in forum Database Design
    Replies: 3
    Last Post: 09-10-2012, 10:37 AM
  5. Replies: 0
    Last Post: 03-14-2009, 12:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums