open a form in microsoft access 2013 and go to a specific record on the child form

how in microsoft access 2013 can i open a form  with vba and go to a child form and set focus on a specific record (that the id is 134)?


something like this

docmd.openform "parentform"
forms!parentform!childform.form!childformid=134 set focus
bill201Asked:
Who is Participating?
 
BitsqueezerConnect With a Mentor Commented:
Hi,

depending on where you want to do that it would be:

forms!parentform!childformcontainer.form.Recordset.FindFirst "childformid=134"

Open in new window


or

Me.childformcontainer.form.Recordset.FindFirst "childformid=134"

Open in new window


(if you want to do that in the main form).

You do not need to use the bookmark property, you can use the recordset of the form itself and as all subforms are loaded before the mainform you can be sure that the subform has contents when the mainform starts with it's code. But as the loading is asynchronous this will sometimes not work because the subform is still downloading records while the mainform starts searching and so it will not find that. To avoid that, use "MoveLast" and "MoveFirst" on the subform's recordset (same syntax as above only without the FindFirst at the end) so it will be forced to first download all records.

The RecordsetClone is a copy of the form's recordset which is always available so you read often the method to use that, find the record there, use the bookmark property and set the main recordset's bookmark to the same value. That's OK but unnecessary as you can do it directly with the form's recordset itself. The reason why RecordsetClone is used is to avoid the pointer to move if nothing was found because your form recordset will then be at the last record. As you would need MoveLast and MoveFirst anyway there is no big difference to use MoveFirst again if nothing was found (that can be tested using the "NoMatch" property, see Access help for further details under "FindFirst" on DAO recordsets). You can also use "Application.Echo False/True" to avoid flickering - or use the bookmark method, it's up to you.

This methods are only needed if you want to position the pointer to the wanted record but also all other records should be displayed. If you only want to see the specific record you can instead link child and master values of the subform container or manipulate the SQL string of the RecordSource property of your subform to only see this specific record - in this case you would not need to use FindFirst but of course you would only see this one record in the subform.

Cheers,

Christian
0
 
PatHartmanCommented:
I don't have any code handy but you'll need to use  a recordset based on the recordset clone of the subform and the bookmark property to move the record pointer.
0
 
bill201Author Commented:
Thanks a lot for your answer, but I don't know how to write exact the code for a book mark, I will try to learn from googl (the best  teacher that exist today ), I hope I will manage.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
thenelsonConnect With a Mentor Commented:
You have the open form with:
docmd.openform "parentform"

To move to a specific record, you have several methods:

1. If you have a control with a unique identifier for the record, you can set the focus to that control and use DoCmd.FindRecord:

    Me.Childform.SetFocus
    Me.Childform.form.ctrlRecordNumber.SetFocus
    DoCmd.FindRecord RecordNumber

2. You can use the subform's filter event:
    Me.Childform.Filter = "RecordNumber=1234"
    Me.Childform.FilterOn = True

3. You can open the Access built in find dialogue:
    RunCommand acCmdFind

4. You can use the Recordset.FindFirst technique:
     Me.Childform.form.Recordset.FindFirst "RecordNumber=1234"

5. You can set the subform's RecordSource to a query with a Where clause:
     Me.Childform.form.RecordSource = "SELECT * FROM tablename WHERE RecordNumber=1234"
0
 
Nick67Connect With a Mentor Commented:
set focus on a specific record (that the id is 134)?
It strikes me that I don't yet know enough to answer your question.

"open a form  with vba"
As noted, in the VBA function or procedure (a button is  being clicked? or an AfterUpdate event?) you have
DoCmd.OpenForm "SomeFormName",acNormal
The command takes more optional arguments -- but your plan of attack depends very much upon what you intend to do with the resulting form.
After acNormal(the view argument, which I assume you want to open the form just normally) comes FilterName.  If used, it has to be the name of a valid query in your app: "SomeQuery"  I've never used it though
Next comes WhereCondition, which is a valid SQL WHERE clause, without the word WHERE.  If used, this filters the form down to the resulting set -- MS, when you build a button that 'opens a form to a specific record,' uses this option.  The problem is that you ARE filtered, so if you want to navigate the form through the whole set afterwards, you are hooped.  
The last parameter is OpenArgs.  This is particularly powerful.  It allows you to pass in a string value of your choice to the new form.  You can build delimited strings in the old form's VBA (somevalue;anothervalue;athirdvalue) and in the new form use Split(Me.OpenArgs, ";") to separate them out and use the resulting values in any way you choose.

Now "set the focus on a particular record" leaves a lot to be desired.
Normally, the focus is set to a control
Is your subform a continuous form?
Then what you are looking to do is open the form to a particular master record, and make a particular record in the child form Current, and then set the focus to a control in the subform.

Or maybe you want something else altogether :)
A sample db is the surest way to getting a good Answer
0
 
bill201Author Commented:
Many thanks to all responders, the reason that I don't responded immediately was because i was extremely busy,  but today I  looked deeply into everything you've written, and from the answers I got the answer (with a small different from Bitsqueezer code).
0
 
thenelsonCommented:
Glad I could help.
Nelson
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.