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,205 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 35

Expert Comment

by:PatHartman
ID: 39921740
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
ID: 39923070
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
ID: 39936159
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 167 total points
ID: 39936593
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
ID: 39937349
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
ID: 39973635
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
ID: 39973702
Glad I could help.
Nelson
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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

773 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