Open record in main form by clicking record ID in subform

[cosyg]

Hello,


I am new to Access and while I've been able to figure out how to build most of my intended functionality by searching forums, Google, YouTube, etc., I can't figure this one out.

I am creating an events database. In my main form, I have all the details of a single event (billing code, fees, customer, support personnel, etc.). Then, I have a subform (form view) that lists each of the other services which share an event ID (this ID comes from a separate, unrelated events management suite). In the subform, I display the most-commonly-edited fields so that they are editable without navigating to a different record.

However, there will be instances where it becomes necessary to navigate to one of the records listed in the subform. I would like to make this a quick process by just clicking on the primary key in the subform, and have the corresponding record appear in the main form.

In my searches I've found a common suggestion to use a DoCmd.OpenForm method where the form opens to that specific record -- in effect filtering by the primary key number. I don't prefer this method because it disables the ability to navigate to other records using the previous, next, find records, (etc.) buttons without closing and re-opening the form.

Is there a way to make the main form 'jump' to a specific record by clicking on a control in the subform, without limiting the ability to navigate in the main form?

Thank you.

[June7]

I am confused. This is a form/subform arrangement and both forms are bound to the same data? Ordinarily, subforms do not dictate to main form (children don't command parents).

Maybe you should look at the Split form. Sounds like you are trying to emulate a Split form and my experience with this indicates success is doubtful.

[cosyg]

Thank you for the response. Yes, I would like it to operate like a split form, but would also like to take advantage of form view for the subform, automatically displaying corresponding records by Event ID, enabling edits in the subform using drop-down lists and date pickers, lock certain controls in the subform, etc. I just created a test split form (I'm not very familiar) and at first glance it appears some of these functions may not be available.

[June7]

I use Bookmarks and RecordsetClone to navigate a form's records. Example code:

Private Sub cbxCommunities_AfterUpdate()
'Find the first record that matches the control.
Me.RecordsetClone.FindFirst "Community = '" & Me.cbxCommunities & "'"
If Not Me.RecordsetClone.EOF Then Me.Bookmark = Me.RecordsetClone.Bookmark
Me.cbxCommunities = Null
End Sub

[trevor40]

you may need to seperate the form/sub form and use two forms and link them using code to update the subform when the main form changes, and when you want to update the main form with the sub form reference again use code for this. Or you might be better off opening another form on top showing the related data. lots of code... or look outside the box and see if you can find a better way to use the default forms linking parent/child setup to do it all for you somehow.

[cosyg]

Hmm, interesting. I'll have to look more into Bookmarks.

I assume this code would go into the subform (not the main form)?

It looks like your code is based on making a selection in a combo box. In my case I am trying to do a Click event using a control ([ID1]) which is bound to the AutoNumber primary key. When I tried modifying your code to fit mine, I first got a data type error. I removed the single-quotes (') and then I got a different error on the Me.ID1 = Null line saying I can't alter that value ... seems logical, since you shouldn't be able to edit the primary key.

I'm just learning to understand VBA code so I'm not exactly sure what each step is doing, but this gives me some encouragement to keep digging around -- thanks for that.

[EDIT] Just wanted to mention that while the way I have my form configured may not be "ideal" it's really doing everything I want it to do except for this one 'hyperlink' function. Otherwise it's definitely meeting my goal of having very user-friendly and useful functionality by giving three specifically-tailored views of the same dataset using subforms (main form for single-event detail; subform 1 for related services within an Event ID; and subform 2 for all prior customer orders by customer name).

[cosyg]

I found some more information here: http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx

Based on the 2nd example, I have the following code in my subform 1:

Private Sub ID1_Click()

    'This is a test to verify the Click function is working
    MsgBox ("Success")

    Dim rst As Recordset
    Dim strSearchName As Integer
 
    Set rst = Me.RecordsetClone
    strSearchName = Me!ID1
    rst.FindFirst "ID1 = " & strSearchName
        
        'I chose a specific record ID for now to verify that the actual record I clicked on is found.  When I click a different record "Yes" does not return.
        If strSearchName = 11893 Then
            MsgBox "Yes"
        End If
        If rst.NoMatch Then
            MsgBox "Record not found"
        Else
            'I added a verification that the record was found in the dataset
            MsgBox "Record found"
            'Here is where I get stuck. I'm trying to figure out how to reference the main form's Bookmark parameter
            'Form.EventsLogF.Bookmark = rst.Bookmark
        End If
    rst.Close
End Sub

The code seems to work as expected in terms of finding the record in the record set, I'm just wondering if it's possible to use it to change the record shown in the main form?

[June7]

If the code is behind the subform, try:

Parent.RecordsetClone.FindFirst "ID = " & Me.ID
Parent.Bookmark = Parent.RecordsetClone.Bookmark

Code behind the main form:

Me.RecordsetClone.FindFirst "ID = " & Me.subformcontainername!ID
Me.Bookmark = Me.RecordsetClone.Bookmark


Setting the combobox to Null was because the combobox was UNBOUND and used only to enter filter criteria. That certainly doesn't apply in your situation.

[cosyg]

It worked! Awesome! Thank you!!

One last question - how to handle a Null entry? (i.e. the user clicks on '(New)' in the subform). I think the best thing would be either to end the function (do nothing) or mimic the Add Record function.

[EDIT] Turns out the code in the main form doesn't appear to be necessary. I was getting Null errors when triggering a Duplicate or New Record macro, so I commented those two lines out, and the functionality still works.

[June7]

If Me.NewRecord Then


or


If Me.subformcontainername.Form.NewRecord Then


Now I wonder why previous attempts by others failed.

[cosyg]

Alright, I think I'm to the point where I'm going to call it good for now. When the user clicks '(New)' the code captures that and simply pops a MsgBox giving instruction to use the provided buttons and then does nothing.

Here's my code for posterity:

Private Sub ID1_Click()
    
    Dim rst As Recordset
    Dim strSearchName As Integer
    
    Set rst = Me.RecordsetClone
        If Me.NewRecord Then
           MsgBox "Use the Duplicate or New Record buttons."
           Exit Sub
        End If
    strSearchName = Me!ID1
    rst.FindFirst "ID1 = " & strSearchName
        If rst.NoMatch Then
            MsgBox "Record not found"
        Else
            Parent.RecordsetClone.FindFirst "ID1 = " & Me.ID1
            Parent.Bookmark = Parent.RecordsetClone.Bookmark
        End If
    rst.Close
End Sub

Thanks a million!