Access VBA Opening Form to display specific record without filter

I need to get a form to open another form and display a specific record. BUT I don't want to apply a filter because the user needs to select any record if they don't want the specific one that is displayed.

I have tried opening the form with a LinkCriteria that selects the records I want (and that works), but if I then remove the filter (forms!formname.filteron=false) the filter is removed but the form jumps to the first record in the database.

The form has an 'OrderBy' on it and I can't take that off because the form sequence would then make no sense to the user.

This is one of those 'this should be simple but I am damned if I can find the answer" questions.
LVL 2
dr_duddAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
I'd generally open the form and find the record that you're interested in. This also uses bookmarks:

DoCmd.OpenForm "MyForm"
Forms!MyForm.RecordsetClone.FindFirst "[ID]=111"
Forms!MyForm.Bookmark=Forms!MyForm.RecordsetClone.Bookmark
0
 
peter57rCommented:
Hello dr_dudd,

Not clear what the real problem is here.

If the user doesn't want the initially displayed record and removes the filter why does it matter which record is then displayed as a result of that action?  Presumably, the user will go on to find the record they actually want using the built-in tools or developer-provided search facility?
And I understand you're saying that you don't want the natural results to occur but you don't say what you DO want to happen.


Pete
0
 
will_scarlet7Commented:
If you put this code in the event that the user selects not to use the current record it should remove the filter and then return the user to the same record:

    Dim myBookmark As Variant

    myBookmark = Bookmark
    Me.FilterOn = False
    Bookmark = myBookmark
0
 
dr_duddAuthor Commented:
Thanks Shane spot on! I thought about bookmarking as Sam's idea but the issue was that it was not this form I wanted to bookmark, but the one that I opened. Creating a recordset clone and then using its boomark - genius!

Thanks for all replies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.