How do you save a modified property setting in VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Ponzelli - 07 Apr 2006 22:03 GMT

I'm trying to use the code below to set the Modal property of all forms in
an Access 2002 database to "No."

The code compiles, and runs completely through both loops, and the
Debug.Print frm.Modal command confirms that as the code is running, the
Modal properties are being set to "No." But the line that tries to save the
form after the modal property has been set to "No",

DoCmd.Save acForm, strFormName

doesn't seem to have any effect, because when you open the form after the
procedure runs, the modal property is still set to "Yes" (if it had been set
to "Yes" before you run the procedure.

Here's that line in context:

For Each frmDoc In db.Containers("Forms").Documents
strFormName = frmDoc.Name
DoCmd.OpenForm strFormName
For Each frm In Forms
frm.Modal = False
Debug.Print frm.Name
Debug.Print frm.Modal
DoCmd.Save acForm, strFormName '<-- this line doesn't save
the new Modal property.
DoCmd.Close acForm, strFormName
Next frm
Next frmDoc

How can I modify that single line of code to save the new property setting
when it closes the forms?

Thanks in advance,

Paul

Reply to this Message

Marshall Barton - 07 Apr 2006 22:44 GMT

>I'm trying to use the code below to set the Modal property of all forms in
>an Access 2002 database to "No."
[quoted text clipped - 27 lines]
>How can I modify that single line of code to save the new property setting
>when it closes the forms?

I think your problem is that you are not opening the form in
design view.

I have always used the Close method to save the form
instead.

Since there is only supposed to be one form open while you
operate on it, there is a potential danger in using the For
Each loop.

Here's the way I would code this operation:

For Each frmDoc In db.Containers("Forms").Documents
strFormName = frmDoc.Name
DoCmd.OpenForm strFormName, acDesign
Forms(strFormName).Modal = False
Debug.Print frm.Name
Debug.Print frm.Modal
DoCmd.Close acForm, strFormName, acSaveYes
Next frmDoc

Signature

Marsh
MVP [MS Access]

Reply to this Message

Paul Ponzelli - 07 Apr 2006 23:20 GMT

It works great. Thanks, Marsh.

However, in order to get it to not crash on the

Debug.Print frm.Name

statement with an "Object variable or With block variable not set" error, I
had to add the

For Each frm in Forms
. . .
Next frm"

statements. With those back in, it didn't produce an error message, and it
saved the modified Modal property in all forms.

Is there a line of code I can use to set the frm object variable to the form
that's currently opened in Design View without running through the For Each
loop? I know the loop is overkill, but I don't know how to set the object
variable otherwise.

Thanks again in advance,

Paul

>>I'm trying to use the code below to set the Modal property of all forms in
>>an Access 2002 database to "No."
[quoted text clipped - 51 lines]
> DoCmd.Close acForm, strFormName, acSaveYes
> Next frmDoc

Reply to this Message

TC - 08 Apr 2006 02:09 GMT

dim frm as form
docmd.openform "blah", ... acDesign ...
set frm = forms("blah")

HTH,
TC (MVP Access)
http://tc2.atspace.com

Reply to this Message

Marshall Barton - 08 Apr 2006 04:23 GMT

Sorry, I wasn't paying any attention to the Debug lines.

You really do not want to loop through all open forms just
to provide an object reference. you can do what TC
suggested,

or use:
    With Forms(strFormName)
        .Modal = False
        Debug.Print .Name
        Debug.Print .Modal
    End With

or change the debug lines to:
    Debug.Print Forms(strFormName).Name
Debug.Print Forms(strFormName).Modal

Personally, I prefer usinf With.

Signature

Marsh
MVP [MS Access]

>It works great. Thanks, Marsh.
>
[quoted text clipped - 72 lines]
>> DoCmd.Close acForm, strFormName, acSaveYes
>> Next frmDoc

Reply to this Message

Paul Ponzelli - 08 Apr 2006 21:10 GMT

Thanks for the array of choices, gentlemen. The all work just fine.

I was confused about the right syntax for setting object variables, but your
examples shed some light on this for me.

Paul

Reply to this Message