?
Solved

Filtered form prompts to save on close

Posted on 2014-11-12
13
Medium Priority
?
212 Views
Last Modified: 2014-12-03
We have a database which uses the navigation subform tabs in MS Access 2010.  The front screen is a search facility where the user can search for an employee name and it will filter out the correct person.

However, when they click on another tab to open a different navigation subform this filtered form prompts to save the changes.  I have searched for ways to prevent this and tried removing the filter on unload, lost focus and close but those actions take place before the filter removes so it isn't doing the job.

The only solutions I have found require a close button which isn't appropriate where the user is in the Navigation Subforms as they just click from tab to tab.

Does anyone have any idea how I can prevent the save prompt.  I'd be most grateful!

Thank you in  advance
0
Comment
Question by:PerksP
[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
  • 6
  • 4
  • 3
13 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40437014
You can try to run this command, for example when you open the form:

    DoCmd.SetWarnings False

However, it is global, so you may wish to turn it on again, say, when closing the form:

    DoCmd.SetWarnings True

/gustav
0
 

Author Comment

by:PerksP
ID: 40437138
Thanks for you quick replay Gustav

I just tried that but unfortunately it doesn't seem to apply to the save command which is still coming up when I switch to a different navigation tab
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40437541
You are getting the save prompt because you have modified a form property.  I recommend that you change your process to not modify the form properties since that prevents you from distributing as an .accde or from using the Access runtime engine.

To get past the save prompt, close the form this way:

DoCmd.Close acForm, Me, acSaveNo
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:PerksP
ID: 40437911
Thanks Pat

Unfortunately I can't use a button with code as they are using the Navigation Forms available in MS Access 2010.  This means that they switch between forms by clicking the tab rather than by clicking a close button
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40438027
Then the best option is to stop modifying form properties.  What are you doing to change the form?
0
 

Author Comment

by:PerksP
ID: 40438100
I have a field where they select an employee and it filters the dataset to show just that employee.  They then double click to go to the full record.

If they click away from this form to a different navigation form that is when they get the save prompt as it closes the form.

It is that which is causing the save prompt although I have used these fields in mdb files and never had a problem with a save prompt coming up.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40438220
Perhaps you can use the OnChange event (changing tabs) of the form and run this:

DoCmd.Save acForm, Me.Name

But I would consider a different method for filtering and navigating.

/gustav
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40438315
Personally, I don't use filters since they require loading the entire recordset and since I use mostly SQL Server, I really only want to retrieve the minimum set of records.  Therefore, I use criteria in the query to "filter" the form.  The criteria points to the controls where the selections are made.  Then I requery the form/subform.

Where somefield = Forms!yourform!somefield
0
 

Author Comment

by:PerksP
ID: 40438372
I do use queries to filter a form but this was supposed to be easier to let them choose to filter by different fields each time e.g. surname, job title, department and also to easily remove the filter to show all records again.

Thanks for you help and advice.  I think I am going to have to think again as this is an unforeseen disadvantage to the navigation tabs.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40440416
Access has always had problems filtering subforms and the navigation form puts your "main" forms on the navigation form so they become subforms.
0
 

Accepted Solution

by:
PerksP earned 0 total points
ID: 40470549
Sadly it appears that there is no real solution to this so will have to redesign in order to stop the save prompt
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40470570
One workaround is to forcely save the form before closing and/or to call

   DoCmd.SetWarnings False

when opening the form.
Then, when loading, reset by code the form to the default settings.

I once built a system for this for a client. It was a major task as he wanted the option for the user to save the current setting (filtering, freezing, columnwidths etc. etc.) without losing the option to reset to the default values. Of course, each user ran their own frontend, but all the settings were persisted in the backend to allow for updates of the frontend. The default values were persisted too, so the developers could work with the forms and save them without bothering for the default appearance; this was maintained by an application administrator.
As if this wasn't enough, the system automatically recorded new (and deleted) forms and fields and their formats.

So "There isn't a solution" is not quite right but it may be beyond the resources you wish to allocate.

/gustav
0
 

Author Closing Comment

by:PerksP
ID: 40478146
There isn't a solution.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 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