Solved

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

Posted on 2014-03-11
7
9,063 Views
Last Modified: 2014-04-02
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
0
Comment
Question by:bill201
7 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:bill201
Comment Utility
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
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 167 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 167 total points
Comment Utility
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 166 total points
Comment Utility
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
 

Author Comment

by:bill201
Comment Utility
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
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
Glad I could help.
Nelson
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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

11 Experts available now in Live!

Get 1:1 Help Now