Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-03-11
7
Medium Priority
?
9,680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 39

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 668 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 668 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 664 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

660 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