Close VBA editor without saving (1 Viewer)

[tomasm]

Sometimes I like to make changes to a VBA module for testing then want to discard those changes. The only way I have found to do this is to close Access and select "no" when prompted to save changes. The problem with this method is if I make changes to Access objects while I'm editing a VBA module, I am unable to save the object changes without also saving the VBA changes. It's either all or nothing.

So, how do I close the Microsoft Visual Basic for Applications window without saving changes?

 

[Gasman]

I get presented with a list of what has been modified and choose what I want to save.

 

[tomasm]

Yes I get that too (never mind what I said about "all or nothing"). What I want to do is discard VBA changes without having to close Access.

Also, sometimes I want to click the save icon to save changes to an object. But this will save VBA changes as well, even if the VBA editor window is closed. So the save icon is all or nothing.

Edit: tested this just now - I made a code change in the VBA editor and increased the width of a field on a table. Closed Access. When prompted to save, I de-selected the Module so that only the Table was highlighted blue. Clicked "yes" to save changes. Re-opened Access. Changes to BOTH the table AND module were saved, even though I de-selected the module.

Second test - I made a code change in the VBA editor and didn't make any changes to any Access objects. Closed Access. When prompted to save changes to the module I selected "no". Re-opened Access. Changes to the module were NOT saved, as expected.

Edit 2: I see now that the save icon in the VBA editor is not identical to the save icon in the Access window, but I still am not seeing a way to discard VBA code changes while keeping the database open.

 

[Josef P.]

Problem workaround:
1. SaveAsText
2. change code
3. test
4. LoadFromText

If the CodeModules are available as a file, you could use them for a version control system.
Or you could use an Access add-in that does the export/import for you.

 

[sonic8]

Problem workaround:
1. SaveAsText

Unfortunately not. The output from SaveAsText also includes the form definition.

 

[Josef P.]

I thought about modules (standard + classes) - and not the CodeModule of a form/report.

The problem with this method is if I make changes to Access objects while I'm editing a VBA module, I am unable to save the object changes without also saving the VBA changes. It's either all or nothing.

Does this mean changing a form layout and changing the form code + saving only the layout?
This is an unusual approach for me.

For form classes you could possibly still try:
VBComponent.Export + CodeModule.AddFromFile/AddFromString + Remove first lines.

The recommendation to think about version control system remains.

 

[tomasm]

I think I've figured out how the save system works. But it's so confusing I might be posting more incorrect information...

The save button in the VBA editor window generates a prompt to select which object and module you want to save if you have made changes to an object and module. The save button in the main Access window saves changes to objects but does not save changes to modules. There is no prompt when clicking this button. Closing Access will generate the same prompt as clicking the VBA editor save button.

The problem is that changes to the module will be saved even if it is de-selected in the save prompt (and the object remains selected). However, de-selecting the object in the save prompt while leaving the module selected saves changes to the module but not the object, as expected.

To save changes to an object but not a module, I need to click the Access window save button first, then close Access. This will trigger a prompt to save module changes and I can select "no". When I re-open Access the object changes are still there but the module changes are not.

Also, selecting "no to all" when prompted to save will not save changes to the object or module, as expected.

I keep testing this different ways and it seems like there's a bug to me or I'm still missing something. It would be better if closing a module window generated a save prompt for that module only, in the same way that closing an object generates a prompt.

 

[Pat Hartman]

I agree, this is confusing but think about it this way. Access is attempting to protect the novice from losing changes to objects. Non developers are known for accidentally forgetting to save. They also never make backups. So, Access is saving you from yourself. It means well