Solved

Access VBA Opening Form to display specific record without filter

Posted on 2004-09-08
4
1,325 Views
Last Modified: 2007-12-19
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.
0
Comment
Question by:dr_dudd
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 12005097
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12005189
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 125 total points
ID: 12005853
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
 
LVL 2

Author Comment

by:dr_dudd
ID: 12007147
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now