Solved

How to goto a record on an Access Navigation form?

Posted on 2014-01-08
10
1,578 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:iamsickofthisidshtuff
  • 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:iamsickofthisidshtuff
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 30

Expert Comment

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

Author Comment

by:iamsickofthisidshtuff
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:iamsickofthisidshtuff
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:iamsickofthisidshtuff
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:iamsickofthisidshtuff
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

809 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