Solved

Access 2013 - Opening the last form or table that was accessed on start-up

Posted on 2014-10-13
14
301 Views
Last Modified: 2014-10-28
I have an access 2013 database that is opening the last form or table that was accessed when it starts-up

I have a start-up form that runs a vba app to create a table on start-up then it closes the start-up form and opens a master form.

The start-up form is listed under the Display form in Current Database Options

code in the start-up form is


Private Sub Form_Close()
    DoCmd.OpenForm "0_MasterDataFrm", acNormal
End Sub

Private Sub Form_Open(Cancel As Integer)
    CreateTableFunction
    DoCmd.Close acForm, "0_STARTUP"

End Sub

Thanks
0
Comment
Question by:rogerdjr
  • 7
  • 7
14 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 40377679
I don't see a question.

If the question is "how do I open on startup the last FORM accessed before the app was closed last time?" then --

You will need to add a new table (preferably in the BE) to log each Form as it opens.  You will also need to capture the username as he logs in.  You can't do anything with tables since tables do not offer any events and users should NEVER, EVER access tables or queries directly anyway.  As each form opens, it should update the record of a particular user in the log table to record that this user opened this form.  The code needs to be able to add a log record if one doesn't already exist for the user.  You can call this logging code from the Open event of each form.  You would pass in the name of the form.

Call MyLogger(Me.Name)

The reason I prefer the table to be in the BE rather the FE is because when you replace the FE to distribute updates, the table with the last Form for this user will be wiped out and replaced with the copy from your test FE.

Then instead of
    DoCmd.OpenForm "0_MasterDataFrm", acNormal
you would use
    DoCmd.OpenForm  Nz(DLookup("FormName", "tblLogUsage", "UserName = '" & loggedinusername & "'"), "0_MasterDataFrm"), acNormal


Creating a table every time the app opens sounds like you think you are working with a spreadsheet rather than with a relational database.

Also, rather than hardcoding the form name in expressions such as
DoCmd.Close acForm, "0_STARTUP"
Use a reference to the name of the current form
DoCmd.Close acForm, Me.Name

This is safer because
a) if you copy and paste the code, you won't have to remember to change the form name.
b) if you rename the form, the code won't break.  It will simply pick up the new form name.
c) if you create dummy forms that you copy whenever you create a new form so you don't have to keep redefining the wheel, the code references don't have to change each time.  I have several standard forms with headers/footers, font, color, standard buttons, standard procedures (open, close, BeforeUpdate) that include standard code snippets.
0
 

Author Comment

by:rogerdjr
ID: 40378244
The issue actually is that the last table or form that was open when I closed the database re-opens when I start-up.

I don't want this to happen.

Seems to be unique to this particular application - other databases I'm working don't do this?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40379977
There is an option to open the last used database when Access starts (Access Options/Client Settings/Advanced) but none to open the last used object that I have ever seen.

On the Access Options/Current Database sheet, you have the option to specify a startup form.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:rogerdjr
ID: 40380336
It seems to be something strange about this database

Please don't judge the content too harshly - I'm an amateur and I only use this as a working tool to learn and deliver results (reports) for clients

Thanks
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40380463
Is there a macro running?  Look for something named AutoExec.  Did you ever create a macro or attempt to write code to perform this automatic opening?

Try opening the database while holding down the shift key.  This stops the startup processes from running.
0
 

Author Comment

by:rogerdjr
ID: 40387683
The only thing I can think is if the spell check process I use on some objects might be an issue a typical example would be

Private Sub IDandDescripofInspStructures_Exit(Cancel As Integer)
    'Private Sub GeneralLocation_Exit(Cancel As Integer)
        On Error Resume Next

        If Nz(IDandDescripofInspStructures.Text, "") = "" Then
            Exit Sub
        Else
            IDandDescripofInspStructures.SetFocus
            IDandDescripofInspStructures.SelStart = 0
            IDandDescripofInspStructures.SelLength = Len(IDandDescripofInspStructures.Text)
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdSpelling
            DoCmd.SetWarnings True
        End If
End Sub
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40390568
You may have to post the database.  Please compact it and zip it first.
0
 

Author Comment

by:rogerdjr
ID: 40400432
See attached zipped database

I had some computer hardware issues that delayed my response
J--Projects--Accessibility---CASp-Librar
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40405108
You seem to have disabled the shift-bypass key so I can't open the database and whatever code you have running is in a loop and so the database doesn't ever open.
0
 

Author Comment

by:rogerdjr
ID: 40405470
If I disabled the shift-bypass key it was not intentional - how would I re-enable it?
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40406398
Can you open the database directly into design view by holding down the shift key?  If you can then there may be something wrong with the file you uploaded.  Please try compacting and zipping and upload again.
0
 

Author Comment

by:rogerdjr
ID: 40406477
it Opened fine in the design mode with shift key pressed

I compacted and re-zipped - see attached file

The last time i opened it the CASp_ImplementationPlan_SummaryDataTbl opened and access tried to spell check it. That was the last table I had opened when I was working on the database and closed it.

Very strange - I have several databases that I use as working tools (regularly adding tables, forms, reports, etc.) and none of them have this going on????
J--Projects--Accessibility---CASp-Librar
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40407051
I can't actually get the form to load without errors because it references some files outside of the database. and since the files aren't found, I get errors.

There is a huge amount of code that runs when the form opens that makes opening the form extremely slow.  There are other ways to accomplish the filtering that don't require code.  For starters, you should be using the master/child links.  That keeps the subforms sync'd with the main form.  For the other queries, you can use criteria embedded in the query.  There is no need to rebuild the SQL string each time the form loads.

There is a lot of overhead associated with creating a new query that doesn't happen when you use querydefs.  Querydefs are "compiled" when they execute the first time and the execution plan that is calculated is saved for later use so subsequent executions of the query will use the saved plan rather than building a new one.  For your form, you are forcing Access to build quite a few execution plans before it can even open the form.

Also, when you replace a SQL string with a new one, Access automatically runs the query.  Your requeries are making the queries run twice which also adds to the slowness.

I never got to the point of trying to actually solve your problem and I don't really have any more time to look at this.  Sorry.
0
 

Author Closing Comment

by:rogerdjr
ID: 40408913
thanks
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro to import XML in Access 2013 2 37
How to use DLookup with IsNull Function 4 26
Batch Export Reports (with multiple parameters) As PDF 2 21
Dcount help 2 12
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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