Strange Error OnClick

[MDW]

OK, I'm working on code that creates form controls on the
fly, and then deletes them once the form is closed
(because the next time that form is opened, you get
a "Control named xyz already exists"). I can get it to
create the controls OK, but for some reason I'm getting a
very odd error when I click the button to close the form.

Here's the error:

"The expression On Click you entered as the event property
setting produced the following errer:

- The expression may not be the name of a macro, user
defined function, or [Event Name]

- There was an error evaluating a macro, user defined
function, or [Event]

Here's the code behind the event button. It's sitting on a
form named "ControlAccess" -

Private Sub PRMcmdClose_Click()

Call removeBoxes

End Sub

The sub removeBoxes sits in a Global Module. Here's the
code -

Public Sub removeBoxes()

Dim objCtl As Control

DoCmd.Close acForm, "ControlAccess", acSaveNo
DoCmd.OpenForm "ControlAccess", acDesign, , , , acHidden

For Each objCtl In Application.Forms
("ControlAccess").Controls

If Left(objCtl.Name, 4) <> "PRMc" Then

DeleteControl "ControlAccess", objCtl.Name

End If

Next

' Save the form with the items deleted
DoCmd.Close acForm, "ControlAccess", acSaveYes

What's frustrating is that I HAVE gotten this to work. It
seems to bomb out after I delete a command button from a
third form. The sub that opens the form "ControlAccess"
uses the CreateControl method to create a checkbox
on "ControlAccess" for each command button on this third
form. I have it set up this way because the number of
command buttons could change in the future, and I want the
form "ControlAccess" to reflect the changes on this third
form (when I won't be around to program the thing).

 

[JeffK]

I would not recommend adding controls to a form as part of
a production database. Access' ability to modify objects
programatically like this is useful if you are building
wizards that make other developers' jobs easier and to
enforce design conventions, not to present users with the
appropriate options.

If you ever need to distribute your db as an mde, your
code will not work because objects cannot be openeded in
design view.
If you ever try to use user-level security, the code will
not work because most people will not have design
permissions to database objects.
If you use Access 2000+ the code will not work because you
must have exclusive access to the database to make design
changes.

You may not anticipate these things now but you are trying
to accommodate future changes in the first place . . .
this is also only a handfule of the problems you will
encounter adding controls to a form on the fly in a
production environment.

There are many ways to handle what you are trying to do
without creating controls on the fly. When it is possible
that a list of options will change, you should store the
list in a table and present it in a listbox or combo box
that reads from the table. For example, if you have 5
products in your database and a command button for each
one of them, store the names and IDs of the products in a
table and use a listbox to do what your command buttons
would do. Or you can use code similar to what is created
in the switchboard wizard.

HTH
Jeff

-----Original Message-----
OK, I'm working on code that creates form controls on the
fly, and then deletes them once the form is closed
(because the next time that form is opened, you get
a "Control named xyz already exists"). I can get it to
create the controls OK, but for some reason I'm getting a
very odd error when I click the button to close the form.

Here's the error:

"The expression On Click you entered as the event property
setting produced the following errer:

- The expression may not be the name of a macro, user
defined function, or [Event Name]

- There was an error evaluating a macro, user defined
function, or [Event]

Here's the code behind the event button. It's sitting on a
form named "ControlAccess" -

Private Sub PRMcmdClose_Click()

Call removeBoxes

End Sub

The sub removeBoxes sits in a Global Module. Here's the
code -

Public Sub removeBoxes()

Dim objCtl As Control

DoCmd.Close acForm, "ControlAccess", acSaveNo
DoCmd.OpenForm "ControlAccess", acDesign, , , , acHidden

For Each objCtl In Application.Forms
("ControlAccess").Controls

If Left(objCtl.Name, 4) <> "PRMc" Then

DeleteControl "ControlAccess", objCtl.Name

End If

Next

' Save the form with the items deleted
DoCmd.Close acForm, "ControlAccess", acSaveYes

What's frustrating is that I HAVE gotten this to work. It
seems to bomb out after I delete a command button from a
third form. The sub that opens the form "ControlAccess"
uses the CreateControl method to create a checkbox
on "ControlAccess" for each command button on this third
form. I have it set up this way because the number of
command buttons could change in the future, and I want the
form "ControlAccess" to reflect the changes on this third
form (when I won't be around to program the thing).
.

 

[MDW]

Thx for the advice. I'll think of a way to re-design that
form.

Maybe I'll just hard-code everything. That way if they
want to change something in the future, they'll need me to
do it. Job security!

-----Original Message-----
I would not recommend adding controls to a form as part of
a production database. Access' ability to modify objects
programatically like this is useful if you are building
wizards that make other developers' jobs easier and to
enforce design conventions, not to present users with the
appropriate options.

If you ever need to distribute your db as an mde, your
code will not work because objects cannot be openeded in
design view.
If you ever try to use user-level security, the code will
not work because most people will not have design
permissions to database objects.
If you use Access 2000+ the code will not work because you
must have exclusive access to the database to make design
changes.

You may not anticipate these things now but you are trying
to accommodate future changes in the first place . . .
this is also only a handfule of the problems you will
encounter adding controls to a form on the fly in a
production environment.

There are many ways to handle what you are trying to do
without creating controls on the fly. When it is possible
that a list of options will change, you should store the
list in a table and present it in a listbox or combo box
that reads from the table. For example, if you have 5
products in your database and a command button for each
one of them, store the names and IDs of the products in a
table and use a listbox to do what your command buttons
would do. Or you can use code similar to what is created
in the switchboard wizard.

HTH
Jeff

-----Original Message-----
OK, I'm working on code that creates form controls on the
fly, and then deletes them once the form is closed
(because the next time that form is opened, you get
a "Control named xyz already exists"). I can get it to
create the controls OK, but for some reason I'm getting a
very odd error when I click the button to close the form.

Here's the error:

"The expression On Click you entered as the event property
setting produced the following errer:

- The expression may not be the name of a macro, user
defined function, or [Event Name]

- There was an error evaluating a macro, user defined
function, or [Event]

Here's the code behind the event button. It's sitting on a
form named "ControlAccess" -

Private Sub PRMcmdClose_Click()

Call removeBoxes

End Sub

The sub removeBoxes sits in a Global Module. Here's the
code -

Public Sub removeBoxes()

Dim objCtl As Control

DoCmd.Close acForm, "ControlAccess", acSaveNo
DoCmd.OpenForm "ControlAccess", acDesign, , , , acHidden

For Each objCtl In Application.Forms
("ControlAccess").Controls

If Left(objCtl.Name, 4) <> "PRMc" Then

DeleteControl "ControlAccess", objCtl.Name

End If

Next

' Save the form with the items deleted
DoCmd.Close acForm, "ControlAccess", acSaveYes

What's frustrating is that I HAVE gotten this to work. It
seems to bomb out after I delete a command button from a
third form. The sub that opens the form "ControlAccess"
uses the CreateControl method to create a checkbox
on "ControlAccess" for each command button on this third
form. I have it set up this way because the number of
command buttons could change in the future, and I want the
form "ControlAccess" to reflect the changes on this third
form (when I won't be around to program the thing).
.

.