How to update main form while navigating in a subform

[WCStarks]

I have an Assets main form with two continuous sub-forms, Asset Tracking and Asset Groups. The Asset Tracking sub-form is in a standard n to 1 relationship. The Asset Groups form displays other Assets related to the current Asset in the main form via a common field called Group. The purpose of this feature is allow creating ad-hoc groups of Assets as needed to simplify accessing related Assets. These groupings can be temporary, or more permanent as needed. And they can be dissolved when no longer needed.

The relationship between the Assets form and the Assets Group sub-form is: Link Master Fields = [Group] and Link Child Fields = [Group]. In the screen print example, I show an Asset in the Assets form and a number of other Assets in the Asset Groups sub-form, which are all part of an Audio-Visual Radio Transmission Kit (ATV-1).

As I navigate between the Assets in the Asset Groups sub-form, I want the Assets form to automatically update with the current Asset in the sub-form. Not knowing any other way, I created a Double-Click event on the Sub-form Tag column, to bring the current Asset in the sub-form into the main form. This is OK, but it is somewhat cumbersome.

I've looked all around on the Internet to see how to deal with the Access navigation column, on the left of the form to accomplish this task automatically. I presume it can be done, but I have no clue how to do it. Any ideas?

FYI: the main form wraps around the right side of the two sub-forms.

[pbaldy]

If you already have code that works, you should be able to put it in the current event of the subform, which will fire when the form loads and when the user changes records.

[WCStarks]

Of course!!! I originally did this months ago, before I understood what On Current does. With my recent experience, I really should have made the connection. Oh well. One of these days, I hope I'll be able to see through the fog more clearly. Thank you.

[pbaldy]

No problem. There is a lot to digest sometimes.

[WCStarks]

Well, Paul, I guess it was too good to be true. Here is the code in both properties. It works in the double click, but not in the On Current. The error is 3021, no current record, on the .Bookmark = .... I don't understand what is different in this new context. With my later experience, I don't really think all this NavigationSubform syntax is even necessary. A simple Parent has usually worked.

Private Sub BarCode_DblClick(Cancel As Integer)
'With Forms![Manage SCATeam].NavigationSubform.Form
'   .RecordsetClone.FindFirst "ID=" & Me.ID
'    .Bookmark = .RecordsetClone.Bookmark
'End With
End Sub

Private Sub Form_Current()
With Forms![Manage SCATeam].NavigationSubform.Form
    .RecordsetClone.FindFirst "ID=" & Me.ID
    .Bookmark = .RecordsetClone.Bookmark
End With
End Sub

[pbaldy]

I think the subform loads before the main form, so on load the main form doesn't have a record to go to. Simplest might be to trap for that error number and ignore it. I suspect it works fine after it loads.

[WCStarks]

Actually, both forms load just fine. The error doesn't occur until I try to navigate in the sub-form.

[pbaldy]

Curious, I'd expect it to work if it worked in the double click event. Can you attach the db here?

[WCStarks]

Rather than sanitize another copy, I'll send the one I did in the last post.
Incidentally, I also changed the With statement to With Me.Parent.Form, which seems to do the same as the extensive NavigationSubform syntax.

Go to the Assets tab and you will recognize the forms from the image. This db, having been sanitized a few days ago doesn't have the code added to the On Current property yet. Just copy it from the sub-form's Tag, Double-Click property. The first asset/group you will see, are some asset records with tags without assets. Those assets were lost in the transfer from an old relational db and need to be re-identified, when I get around to it. Use the Search box to find an asset that has a Group value, such as "ATV-1", in the earlier screen print.
SCATeam.zip

The only irritation with this Group feature, is that each time you select a new asset to move to the main form, the Group form resets. So if the group list is longer than the window, and you are wanting to check consecutive assets in the group beyond the window, you have to keep going down and look where to continue looking. That isn't done all that much, so isn't as big a deal as it might be, if I had to do it a lot.

[WCStarks]

I ran the Form_Current code in debug mode. It is curious. It repeats several times until it errors out.
It executes the two lines .RecordsetClone and .Bookmark several times. Each time it assigns the Bookmark, it re-executes the Current() before it gets to End With. Finally it errors out. It appears that this code, as written, cannot run in the On Current property, as it recycles until it has no record to go to.

[pbaldy]

I saw the error but haven't had a chance to look into it. My pesky employer has me working hard today.

[WCStarks]

I tried the following code but I end up with the same error. I am thinking the problem might be related to how the Sub-form and Main form are related. As mentioned earlier, they are linked not by IDs, but by the Group field. Each time an Asset in the sub-form is moved to the main form, the sub-form must rebuild its group list to match the Group field in the Main form.

Private Sub Form_Current()
    Dim rs As Object
    Set rs = Me.Parent.Recordset.Clone
    rs.FindFirst "[ID] = " & Me![ID]
    If Not rs.EOF Then
        Me.Parent.Bookmark = rs.Bookmark
    End If
End Sub

[pbaldy]

I think your test would be

If Not rs.NoMatch Then

but it's still curious that it works in the double click event but not the current event. I must be having a brain cramp.

[WCStarks]

I am thinking it is because this code is in the Current property, which must get called again for each iteration while rebuilding the Group list. Using the Double-Click, there is no code in the Current property to run with each iteration.

[accesstos]

but it's still curious that it works in the double click event but not the current event. I must be having a brain cramp.

It must appear the [Event Procedure] in the On Current event of the form in the Property Sheet to run the event(!).
It's one of quirks of Access.

In my system this example works fine

Private Sub Form_Current()
    Me.Parent.Recordset.FindFirst "[Field_Name]=" & Me.Field_Name
End Sub

but only without link fields between the forms. Therefor, You have to reserve the process to link the forms by code.

Good luck with your project!