how to open a record With A specific id number on a form - on ms access 2016 - without switching the mode to filtered

i have a code that shen i click on some field on the form it's open a specific form with the Name "CategoryF".

this is my code  
DoCmd.OpenForm "CategoryF", , , "CategoryID=" & Me.CategoryID

Open in new window

'. is there some way to open the form and move to a specific  cateogryid with out swiching the form to filtered mode
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
>>>>>'. is there some way to open the form and move to a specific  cateogryid with out swiching the form to filtered mode <<<<<

Yes, one way is to use a drop down box on your form that includes all category id's.  The use code on the after update event of the drop down box to move the recordset pointer to the selected id.

Let me know if you want to explore this method ... I can provide specifics if need be.

Also, a sample db with your form would be helpful and probably generate a faster solution.

bill201Author Commented:
thanks alot, what is the code for the drop down box?
Rey Obrero (Capricorn1)Commented:
open the form using the "OpenArgs" option

DoCmd.OpenForm "CategoryF", openargs:=me.categoryID

now in the load event of form "categoryF", place thisa codes

private sub form_load()
if me.openargs & ""<>"" then
    with me.recordsetclone
          .findfirst "[categoryID]=" & me.openargs
          if not .nomatch then
                msgbox "Record not found"
          end if
    end with
end if

end sub
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the OpenArgs method. To do that, change your OpenForm to this:

DoCmd.OpenForm "CategoryF", , , , , ,Me.CategoryID

Now in your form, in the Open event, do this:

If Nz(Me.OpenArgs,"") <> "" Then
  Dim rst As DAO.Recordset
  Set rst = Me.RecordsetClone
  rst.FindFirst "CategoryID=" & Me.OpenArgs
  If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
  End If
End If
Eric ShermanAccountant/DeveloperCommented:
>>>thanks alot, what is the code for the drop down box? >>>>

Ok, the OpenArgs method will work better for you.  I misread your statement <<<<is there some way to open the form and move to a specific  cateogryid with out swiching the form to filtered mode >>>> .

I was thinking you had the form already open and you wanted to navigate to another category id.

bill201Author Commented:
the codes aren't working and it's Probably because I forgot to add a very important detail, it's a strange database. the form is based on a table that i named "category" and this table has two kind of records, parent records and child records.

this is how i builded this database
the table name is category
the table fields are:

so every record that has in the "parentcategoryid" field a id num is a child of the record that has this id number.

i have a form with the name "Category"
that has sub form with the child of this record (based on the same table).
i need a code that when i press on a record on the sub form it's will open the parent form on the same child record with a unfiltered mode.
i'm attaching the database, for clarifysample.mdb
Rey Obrero (Capricorn1)Commented:
Rey Obrero (Capricorn1)Commented:
lets start with this revised db..
post your comment
bill201Author Commented:
your database Built in a different way and it's don't give the  possibility make sub-sub-categories, but in the  database that i create  i can make easily sub categories without any limitation.
i add more records on the database to show you my point

look in the record drinks that have sub and sub-sub categories.sample--2-.mdb
Rey Obrero (Capricorn1)Commented:
<your database Built> that is your database not mine.. i did not build any.

so, lets get back to your problem.
your explanation above is clear as mud.

both your main form and subform are open, and these are the only forms you are working on right?
bill201Author Commented:
yes. now i want that when i dbl click on some record on the sub category form it's will show me this record on  the main category form, but on a unfiltered mode.
Rey Obrero (Capricorn1)Commented:
<...  it's will show me this record on  the main category form, but on a unfiltered mode. >

perhaps it may be clearer, if you could explain this using your Form/subform image, honestly right now, i don't really understand what you want to achieve
remember your Main/Subform  is just showing a one to one record
I can't look at the database at the moment so I can't see what you are talking about but having a subform control a main form goes against the nature of the form/subform relationship.  To have a subform control a main form, you would need to remove any master/child connections that Access or you might have added.  Then in the current event of the subform, you would force the main form to requery itself to move to the record selected in the subform.  One way to do that would be to have the RecordSource query of the main form reference the linking field in the subform.  Then the code in the current event of the subform would be:

And the criteria in the RecordSource query of the main form would be:
Where somefield = Forms!mainform!subform.Form!linkingfield

It goes without saying that since you have no relationship between these forms, it is incumbent on you to ensure that the foreign keys are correctly populated when new records are added.
Eric ShermanAccountant/DeveloperCommented:
Like some of the other experts ... I really don't quite understand what it is you are trying to accomplish by looking at your sample db.  Looks like you table design is not setup correctly.

Can you look at the db application that I am attaching.  I converted it to Access 2k and it is a grocery list app that I created for a client.  It uses a category table along with an an associated items table.  The main form and sub-form interacts with the tables accordingly.

Just trying to get a better idea of the objective so meaningful solutions can be posted.
bill201Author Commented:
First of all, I apologize for my English, I keep trying to improve in English but it seems that I have still a lot of work, i use also google translate to fix some mistakes but it's not enough like you can see.

my target is to create a database for tracking all my expenses and to know exact where every dollar gone,  and i want to enable sub and sub sub categories And so on (most time i don't need sub-sub categories but there is some expenses that i want to monitor very closely.

 so i design the category table on a way that don't have limitation for sub category, every category can have endless Necklace of Descendants,(so i didn't  made a table for cateogries and another table for sub categories).

now let return to my database:
on picture1 the arrow indicate where is the dbl click event and this is the code that i use: DoCmd.OpenForm "Category", , , "CategoryID=" & Me.CategoryID).

the result of this code is like in picture2 but like you can see on the image ms access is opening the form on a filtered mode to show me the record that have the same categoryid, so i don't can to navigate to other record, before i  unfiltered back.

 so i am looking for a code  that the result will be like on picture3 that open "CategoryF" on a unfiltered mode..picture1picture2picture3
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So essentially when you doubleclick on the "Bomboora" line, you want to know if that CategoryID is the "Parent" of other records?

If so, use the Bookmark method on the Parent. In the DoubleClick event (on the subform):

Dim rst As DAO.Recordset
Set rst = Me.Parent.Recordset
rst.FindFirst "ParentCategory="& Me.CategoryID
If Not rst.NoMatch Then
  Me.Parent.Bookmark = rst.Bookmark
  Msgbox "Parent Category Not Found"
End If
Rey Obrero (Capricorn1)Commented:
based on my post at http:#a41066949 

see this revised sample

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bill201Author Commented:
Thanks alot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.