My method for moving to a different entry in a form also filters the form. Is there a better way?

[cherold]

I have subform (SubformB) within a form (FormA). I wanted to be able to double click on a field in SubformB and have it take me to the appropriate entry in FormA. Repurposing some old code (that was used to open a different form from the original form), I did:

Private Sub OriginalName_DblClick(Cancel As Integer)    
    DoCmd.OpenForm "FormA", , , "DiscID = " & disc_id & " AND CompositionID = " & CompositionID, acFormEdit
End Sub

This works, but when it moves to the proper entry (or perhaps just re-opens the form in the proper place) it also filters FormA to that single entry, which is, for me, an unintended side effect. Is there a way to move to the proper entry rather than, as it appears, filtering for the appropriate entry?

[Micron]

Filtering is not the problem, I think, but not sure what your forms are/are not doing. Opening a form causes the records to reload and move to the first record. If it's filtered, then the filter is also applied. Don't know if you've got a continuous form on either the main or sub, so I'm not sure what your best solution would be. Perhaps you only need to create and apply the filter and don't need a Refresh.

[June7]

The form is already open. Instead, use code that navigates to record or applies parameters to Filter property. Review http://www.allenbrowne.com/ser-62.html.

Using subform to provide parameters for sorting main form is odd and usually doesn't work nice.

[cherold]

I don't know what you meant that filtering is not the problem. After double-clicking and having the right entry, the "Filtered" option is highlighted at the bottom of the form and it says that Record 1 of 1 is selected. I have to click "Filtered" to remove the filter. Why is that not a filtering problem?

[Micron]

I meant that based on my understanding of your situation and not knowing if you have continuous forms on either the main or sub, I figured your issue was due to re-opening the form, not that you applied a filter. Now it seems you are saying you don't want a filter and you have to remove it, yet your code constructs and applies one?

[cherold]

Apparently that's what my code does. I really don't know what I'm doing. I had tried googling for a solution and didn't find anything, so I used some old code that did something similar and hoped it might work for what I wanted now. Which it does, just now quite the way I want it to.

I'm hoping there's a way to move to the right entry without filtering the form. On my own, I would probably see if there was a command to turn off the filter I'm accidentally turning on after going to the right entry, but before I start googling for that I'll see if anyone here has a better idea.

[Micron]

Sorry, I thought you knew that. Suggest you explore June7's link and consider what was said about this might not be the right approach. Also Google "Docmd.Openform" and you will see what I mean about what your code does. In the meantime, I'll reiterate that your best solution depends on whether or not you're dealing with a continuous form in either the sub or main and how it was opened in the first place (see property sheet for the form in design view and look at the tabs for the key word 'Allow'). Usually, the subform is related to a single visible or single selected record on the main, so there's no need to navigate to a main record. If a multi record main, selecting a different main record is supposed to alter the subform record displayed. It seems like your subform does not have this child/parent relationship, hence the comment about using a subform to provide parameters.

Perhaps if you outlined what your process is and what the desired outcome is, we can make other recommendations.

[cherold]

Okay, I'll explain what I'm doing. I probably should have done that before.

I have a database devoted to my classical record collection. There's a a composition table that lists each piece I have (i.e. Beethoven's 5th), a disk table with an entry for each physical LP, and a disk_contents table that ties these two together.

My form displays the composition and what disk it's on, with a subform that shows all the compositions on that disk. It looks something like:

--- composition ---
Composer: Beethoven
Composition: Fifth Symphony
--- disk ---
Title: Beethoven's Fifth
Condition: VG+

And at the bottom a subform, which in this case would look like:
Composer Composition
Beethoven 5th Symphony
Beethoven 2nd Symphony


My intent is that if I double click on 2nd symphony, the form will move to the entry for Beethoven's 2nd. That is, it will move to the first entry it finds in that has the composition id for the 2nd symphony and the disk id for the current disk displayed.

OpenForm does that, but apparently it does that by filtering. I looked at June7's link and probably what I want is in there somewhere, since basically what I want is simply to search on two fields in the form and go to that entry, but I'm having trouble finding the part where she does that amidst all the other code creating and working with that search box. What is the part of that code that says, search for this and go there?

[June7]

Allen Browne's code builds a string of filter parameters and applies them to the form's Filter property. Did you click on the 'view the code in a separate window' link?

How would you navigate to another composer on the main form?

I suspect the main and subform are bound to same dataset. Perhaps you should look at using Split form.

If you want to provide db for analysis, follow instructions at bottom of my post.

Might find this of interest https://www.accessforums.net/access/...rts-56655.html

[cherold]

I navigate to a composer by searching on the composer field.

[June7]

Is that with VBA or the intrinsic filter/search tools? Use of the tools will possibly conflict with Allen Browne code.

[cherold]

In terms of the link, I don't want to use an existing program to catalog my records, because my own database contains hundreds of albums and there would be no way to import them into something else. Even if I found something that supported importing, I would have to create my own export report, which would probably be more work than finding a good way to search for two fields in a form and go there.

If I were starting fresh, I certainly would use something existing. But this database is an evolution of a flat database I created in Rapidfile in the 1990s, and it's not practical at this point to break away from it.

I search for compositions using the intrinsic search tool.

[June7]

An alternative to Allen Browne filter code is VBA to search and move to record. One approach involves Bookmark property. Example:

Private Sub tbxLabNum_AfterUpdate()
With Me.RecordsetClone
.FindFirst "LabNum='" & Me.tbxLABNUM & "'"
If Not .NoMatch Then
    Me.Bookmark = .Bookmark
End If
End With

Here is method I've not tried https://msdn.microsoft.com/en-us/lib...or=-2147217396

[cherold]

Bookmark is probably what I want. But if I just replace my code with that code though, it's working on the subform, SubformB. How would I cause this code to instead work on the main form, FormA?

[June7]

Not something I've ever done but maybe:

With Me.Parent.RecordsetClone
.FindFirst "somefield='" & Me.somecontrol & "'"
If Not .NoMatch Then
Me.Parent.Bookmark = .Bookmark
End If

Did you see my suggestion in earlier post for using a Split form?