I open a form with this code:
stDocName = "frmItemMaint"
stLinkCriteria = "[ItemUniq]=" & Me![lstItemSrch].Column(1)
DoCmd.OpenForm stDocName, , , stLinkCriteria
the record source for the form is set to a query on tblItem
I have a command button to quit the form without saving & it executes
DoCmd.Close , , acSaveNo
but any changes are still saved. What is going on here ?
Just want to clarify. What changes are being saved?
There are two types of saves that can occur with Access forms and the data that the form represents:
The first is when any data that the user adds, edits or deletes on the form is saved to the underlying table data. An example would be if you wanted to edit the street address of a contact record.
The second type of save is when a user saves the form's layout. Examples of form layouts are: filters, column widths, and sort orders.
SAVING UNDERLYING TABLE DATA
If you are trying to prevent a user from saving data changes to the underlying table (for example, you want the user to be able to cancel changes by pressing a cancel button), you have lots of options to accomplish this:
One way is to not allow the user to edit in the live table. Then you can evaluate the data and if accepted, write this data to the actual table using an action query or some other posting method.
You can also call the Undo method, which reverses changes made to a form and essentially puts the data back to its original condition.
If you are using a transaction process (like to process a credit card order) you can roll back the transaction.
Another way would be to use the form's cancel method will prevent the save from going through and force the user back to the previous operation prior to any form updates.
Remember that when you use an Access form, any movement off of the current record (such as the user scrolling to another record using the navigation buttons) will automatically save the underlying data if any of that data has been changed. (A user may also save changes using a keyboard shortcut or by pressing a menu option.)
SAVING FORM LAYOUTS
If you want to preserve the form layout when the user exits the form by closing it, you must prevent the user from doing anything that causes this layout to change.
The DoCmd.Close example you provided should lose any layout changes when the form is closed (unless the user already saved changes). You may want to examine the properties of the underlying query. You may be inheriting a filter or sort order from the underlying query, especially if you created the form from a wizard.
Personal Tip: Always set the form properties the way you want them - don't leave this to wizards which may give you form layouts you didn't want.
Here is some additional information from Microsoft Access:
When you create a form or report based on a table or query that has a filter or a sort order specified in it, Microsoft Access includes the filter and sort order with the form or report. The sort order is automatically applied. Whether or not the filter is automatically applied depends on how you create the form or report.
If you create the form or report directly from filtered data while the data is displayed in an open table or query, the filter is applied automatically each time you view the report, but it is applied only the first time you view the form after creating it. (The next time you open the form, you'll apply the filter yourself.)
If you create the form or report based on a closed table or query, the filter won't be applied in the new form or report. You apply it when you need it.