VBA no prompt to save design changes (2 Viewers)

[John F]

In VBA if I start an Access.Application, open a database (with OpenCurrentDatabase) and open a report in design mode, then make changes manually and exit, there is no prompt to Save. But it does save the changes.

If I open the same database by double-clicking in File Explorer and change the report design, it does prompt to save.

It's the same for the design of a table.

It's nothing to do with setwarnings=true - tried with & without.

My simplified code is below.

Dim accobj As Access.Application
Set accobj = New Access.Application
accobj.Visible = True
accobj.OpenCurrentDatabase dbname, True
accobj.DoCmd.OpenReport repname, acViewDesign

I've also tried it with :
Set accobj = CreateObject(Access.Application)

Any help please. Access settings somewhere? Thanks

 

[arnelgp]

on the External db's on the Form you are changing the design, add this on it's close event:

Private Sub Form_Close()
    If Me.IsDirty Then
        If MsgBox("You have unsaved design changes. Close anyway?", _
                  vbExclamation + vbYesNo, _
                  "Unsaved Changes") = vbNo Then
            Cancel = True
        End If
    End If
End Sub

 

[sonic8]

In VBA if I start an Access.Application, open a database (with OpenCurrentDatabase) and open a report in design mode, then make changes manually and exit, there is no prompt to Save. But it does save the changes.

Just a wild guess: Try setting

accobj.UserControl = True

@arnelgp , the question was about changes to the design of objects.

 

[John F]

@sonic8 works a treat, many thanks. Something new every day.

@arnelgp not sure if you were half asleep - it was for Reports not Forms, Form_Close does not have a Cancel parameter, and the Form.IsDirty property should be Form.Dirty. But thanks for thinking for me

 

[arnelgp]

you are right it is report.
but Form.IsDirty is correct when you are editing the form in design view.
Form.Dirty when you make change/add Record on the form.

 

[isladogs]

@John F
I have a short article about the UserControl property which may be of interest

ApplicationUserControl

This shows how the UserControl property can be used to restrict how an application can be opened. One line of code is required in the Form_Load event of the startup form or an autoexec macro.

isladogs.co.uk

 

[sonic8]

@arnelgp , I cannot find the term IsDirty in the whole Access object library.

@isladogs, it would be nice to have some more information on the UserControl property, such as what we figured out here in this thread. The official documentation is woefully sparse.

 

[Gasman]

@sonic8 works a treat, many thanks. Something new every day.

@arnelgp not sure if you were half asleep - it was for Reports not Forms, Form_Close does not have a Cancel parameter, and the Form.IsDirty property should be Form.Dirty. But thanks for thinking for me

Way to go.
First response is to belittle the advice given by a member.
We are not perfect and @arnelgp supplies a lot of code here, 99.9% which works out of the box.

So take the mick for one of his few mistakes.

You might have cut off a good source of help with those comments. Serves you right if that is the case.

 

[isladogs]

@isladogs, it would be nice to have some more information on the UserControl property, such as what we figured out here in this thread. The official documentation is woefully sparse.

There really isn't anything of consequence to add to the official documentation at https://learn.microsoft.com/en-us/office/vba/api/access.application.usercontrol

As stated in my own brief article, I mainly use it to control whether or not an Access file can be opened using automation

 

[sonic8]

There really isn't anything of consequence to add to the official documentation

Excuse me?
Whether Access prompts to save changes to object designs or not is nothing of consequence? - I beg to differ!

 

[isladogs]

you are right it is report.
but Form.IsDirty is correct when you are editing the form in design view.
Form.Dirty when you make change/add Record on the form.

Perhaps you were thinking about the Form_Unload or Form_Dirty events, both of which are cancellable.
Either way, it won't compile using Form.IsDirty.

 

[isladogs]

Excuse me?
Whether Access prompts to save changes to object designs or not is nothing of consequence? - I beg to differ!

That's not what I meant!

UserControl = True when an app is opened directly.
UserControl = False when an app is opened using automation unless specified otherwise in code (as in post #3)

As clearly described earlier, users can still change object designs when UserControl is false
But users have no control over any subsequent Access actions such as deciding whether on not to save changes to form or report design.
So no warning message appears before closing after a design change.
That point had already been clearly covered in your earlier exchange.

However, I will qualify my previous comment by expanding upon the implications of the above.
Users will not get any warning dialogs before an Access action is completed.

So, for example, users can also add/edit/delete records or delete objects when UserControl = False
As users have no control over Access actions once begun, they will also not get warnings before deleting records (even if specified in Client Settings) or before deleting objects.

Also its worth stressing that users can change the UserControl property value from the immediate window.
This is why UserControl is only of limited value on its own as a security measure.

 

[sonic8]

So no warning message appears before closing after a design change.
That point had already been clearly covered in your earlier exchange.

But it is not covered on your web-page on UserControl. - That's the point I intended to make.

So, for example, users can also add/edit/delete records or delete objects when UserControl = False
As users have no control over Access actions once begun, they will also not get warnings before deleting records (even if specified in Client Settings) or before deleting objects.

Also not mentioned on your web page.

You write "This article shows how the UserControl property can be used to restrict how an application can be opened." - Sure, my above points are nor relevant in that regard. However, as the official documentation is lacking, it would IMO be a very sensible idea to mention these in your post to help people understand the effects of the UserControl property.

 

[isladogs]

Similarly my original response was directly related to this thread.

Anyway, I will indeed add additional detail in my web article in the next week or so. Thanks for the 'subtle' prompt!