Solved

How to goto a record on an Access Navigation form?

Posted on 2014-01-08
10
1,673 Views
Last Modified: 2014-02-04
Issue: I do not know how to use a macro/vba to find/goto a record in a list of records on an access “Navigation” form.

Assumptions: 1) I have a main navigation subform called: FrmAlpha
2) I have a navigation-subform (a tab on the navigation form) called: FrmBravo
3) I have a datasheet (a subform on FrmBravo) that is called: FrmCharlie
4) I have a form I just closed that I used to edited the record I now wish to search for in the FrmCharlie.  I shall call this closed form: FrmClosed

I know I will need to use a unique field from the FrmClosed form to find the same record from the list of records on the navigation sub-sub-form (FrmCharlie).  I have the underlying table’s primary key [ID] that I figure I can use.  Also, I presume I have to affect the focus of the database to look at the navigation sub-sub-form, but I am clueless how to do that.  I have seen posts mentioning GoToControl: NavigationSubform, but I am clearly not using that correctly.  Help!
0
Comment
Question by:BD
[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
  • 5
  • 4
10 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39767075
Upload a sample database. It is difficult to follow when you change default control names .

If you can, reproduce the issue using default names given by access, then specify what to look for.
0
 
LVL 84
ID: 39767491
So you have a sub-sub-subform? If so, you refer to it like this:

Forms("YourMainForm").frmAlpha.Form.frmBravo.Form.frmCharlie.Form.SomeControlOnfrmCharlie

See this page for a good tutorial on referring to subforms: http://access.mvps.org/access/forms/frm0031.htm
0
 

Author Comment

by:BD
ID: 39769299
Attached is an example database.

In the datasheet, if any of the records is selected, an Edit Form will open.  When the “Done” button is selected, the edit form closes, the database requeries and goes to the first record in the datasheet.  I would like to have the database goto the record that was edited so my users do not need to search for it.  It should happen automatically in the sequence of actions after the “Done” button is pushed.
Example-DB.accdb
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 30

Expert Comment

by:hnasr
ID: 39770044
In the macro, remove Requery.
Example-DB-2.accdb
0
 

Author Comment

by:BD
ID: 39770808
Understood that the requery causes the database to re-query and go to the first record, and if all I was doing was editing the record, removing that action would be fine.  However, if I add a new record I must requery the database to show that new record on the FrmCharlie form.  Thus the problem still remains.  I want to be able to have the form automatically goto that last record that was edited OR added.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39770849
Your sample does not show how to add.
Modify and upload.
0
 

Author Comment

by:BD
ID: 39771036
Please find attached DB v2.  On the FrmCharlie form, I have placed an "Add" button.  It opens the FrmEdit form in the ADD data mode.  Still the same edit form, but it only allows the addition of new records.  The "Done" button has the same macro actions.  Thanks for the help.
Example-DB-v2.accdb
0
 
LVL 30

Accepted Solution

by:
hnasr earned 200 total points
ID: 39772166
I am a bit confused with these forms. I guess the design can be simplified.

Try this database. I inserted a stop in the code, to show you the steps. You may remark the stop statements.
Run frmAlpha.
Example-DB-v2-1.accdb
0
 

Author Comment

by:BD
ID: 39788548
Sorry, I am away on a business trip but will take a look at the solution next week when I get back.  Thanks
0
 

Author Closing Comment

by:BD
ID: 39833611
The solution works, however I am not proficient enough in code to successfully apply it for my database.  It is apparent I need to take some time to try to understand VBA better.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

752 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