Closing and deleting a form from within a macro (1 Viewer)

Bickbadger · Jul 21, 2007

I'm using a form button to call a macro, the first task of which is to close the form and second is to delete it. Subsequent macro actions then replace the form with another (the ultimate goal being to load a modified form that prevents a particular action being repeated).

Although the macro works fine as a stand-alone macro, whenever it is run from the form button it fails to implement the delete task - always reporting that it cannot delete a form that is open (even though the form is, in fact, closed).

Is there something within Access that prevents deletion of a form that is closed from within the macro that deletes it - or am I missing something more fundamental?

pbaldy · Jul 21, 2007

Setting aside the immediate problem for a moment, why are you deleting the form? You will run the risk of hitting object limits by continually creating and deleting forms. It may make more sense to modify the form appropriately at runtime. Hiding/locking/disabling controls for example, to prevent the action you speak of.

Bickbadger · Jul 22, 2007

Paul,

The form I want to close (or, picking up on your suggestion, modify) is part of a very complex database that has been developed by me (over a period of 8 years) to run our annual village horticultural competition. I had no VBA experience when I started the project, so all the automatics are performed by macros. Ultimately, I will re-develop the database using VBA (instead of macros) - but that is a year away.

To give you some context, I have a Tools form that contains a number of buttons, one of which is Competition Close. This button sets in train a whole series of actions controlled by a (Competition Close) macro and I need to disable this button after it has been actioned, to ensure that the Competition cannot be closed more than once (but is re-instated if the Competition is, subsequently, re-opened).

I had perceived that this could be achieved from within the Competition Close macro, by closing and then deleting the original form and replacing it by renaming a "template" form in which the Competition Close button is disabled.
It works perfectly if I simply run the macro, but it fails when I call the macro from the Close Competition button.

I'd be grateful for any suggestion you have that would enable me to get it working.

Thanks,

David

pbaldy · Jul 23, 2007

If you just want it disabled for the current session, you can simply disable the button. For a longer term solution, I'd probably have a Yes/No field in the table that signified whether the competition was open or closed, and use that flag in the current event of the form to enable/disable the button appropriately. This would also be better if there were multiple competitions, so as you changed records (competitions) the button would be correct.

boblarson · Jul 23, 2007

Just a quick expansion on Paul's statement about object limits, so you are aware. There is a finite maximum of objects and controls that can be created over the lifetime of a database. If you are constantly creating and deleting them, you will get to that maximum a lot faster than normal and, at that point, your database will cease to function the way you are wanting.

So, it is better to follow Paul's discussion about flags in order to maintain the actual status of the competition.

Rabbie · Jul 23, 2007

boblarson said:
There is a finite maximum of objects and controls that can be created over the lifetime of a database. If you are constantly creating and deleting them, you will get to that maximum a lot faster than normal and, at that point, your database will cease to function the way you are wanting.

Does anyone know what this limit is. Will it cause problems that I have a temporary table that is regularly created and deleted.

I will be very grateful for the answers to this.

pbaldy · Jul 23, 2007

Look in Help for "specifications" for the limits relevant to your version of Access.

Rabbie · Jul 23, 2007

pbaldy said:
Look in Help for "specifications" for the limits relevant to your version of Access.

No mention of creations - just limits on the number of objects. Sorry if I have missed something.

pbaldy · Jul 23, 2007

Well, forms and reports have language like:

Number of controls and sections you can add over the lifetime of the form or report:

Now that I look, it doesn't have that language for database objects, so perhaps we're wrong about that limit. In any case, I don't believe the creation/deletion of forms is the best solution to the OP's problem. In your situation, I typically empty/fill temporary tables rather than delete/create them. Feels more efficient to me.

boblarson · Jul 23, 2007

pbaldy said:
Now that I look, it doesn't have that language for database objects, so perhaps we're wrong about that limit. In any case, I don't believe the creation/deletion of forms is the best solution to the OP's problem. In your situation, I typically empty/fill temporary tables rather than delete/create them. Feels more efficient to me.

There may not be an "official" number involved, but it may be possible that doing so many deletes of tables can cause a greater propensity for corruption. Then again, maybe not. But, my view is pretty much the same as Paul's. Why make tables and delete them when you can just empty and fill them? If the table structure was going to change each time then, maybe (and that would be a very weak maybe) I would create/delete each time. But, if the structure is the same and all you need is new data, then I would just use a delete query to clear the table and then import the new data into the existing table.

Rabbie · Jul 23, 2007

Thanks for your replies. I am looking at redesigning this part of my database so it seems like a good time to look at all options. Also looking to see if I can use a query for this data.

Bickbadger · Jul 24, 2007

Paul's suggestion of using a flag would be fine if my form was tied to a table. Unfortunately it isn't - it is simply a "Tools" form with several buttons on it, one of which is "Competition Close".

I want to achieve disabling of the "Competition Close" button immediately after it has been actioned (for both current and future sessions). Only if a "Competition Re-open" routine has been run should the "Competition Close" button be re-instated.

For the moment, I simply run the Close & Open macros directly from themselves. Obviously it would be tidier if they could be run from buttons on the "Tools" form - but that is where my problem arises.

boblarson · Jul 24, 2007

Bickbadger said:
Paul's suggestion of using a flag would be fine if my form was tied to a table. Unfortunately it isn't - it is simply a "Tools" form with several buttons on it, one of which is "Competition Close".

Oh, come on - let's use a little of the gray matter up there. In order to disable the button for a competition from this point on and whenever you open the database, you would need to store the fact that is is closed somewhere. So, create a field in your competitions table that is something like "closed" and then use an update query to set it's value to yes when the button is clicked. It will then store the value and you can use that value to determine whether or not to disable the button.

Bickbadger · Jul 24, 2007

Bob,

I think you are assuming that there is a single "competition" table that could be flagged in this way. Were it that simple, my limited (apparently) gray matter could have coped with it! (the database already contains a tad under a thousand tables, queries, forms and macros)

The reality is that the competition contains many sub-competitions within it. Each sub-competition relates to different combinations of Classes and Sections and the "Close Competition" action is performed only after all competitors details have been entered. Its primary purpose is to prepare the database for scoring and it is vital that this action is not repeated - unless the competition has first been re-opened. Currently, I achieve this by discipline, but I want to make it automatic (and foolproof).

Each competitor may enter as many (or few) classes as he/she chooses and there is no single table that could be flagged to disable the "Close Competition" button on the form.

pbaldy · Jul 24, 2007

That many objects hints at a normalization problem, but setting that aside for the moment, how about a new table/field whose sole purpose is to hold this value? When you close the competition, set it to True, if you reopen the competition set it to False. In the load or current event of this form, examine that value with a DLookup or recordset and use the result to set the enabled or visible property of this button.

Bickbadger · Jul 24, 2007

Paul,

It looks as though my objective cannot be achieved with just a macro. As I said at the outset, I intended to re-develop the database using VBA, probably next year. However, I'll follow your suggestion and solve my problem now by creating some VBA.

Thank you for your guidance.

David

Closing and deleting a form from within a macro (1 Viewer)

Bickbadger

Bickbadger

pbaldy

Wino Moderator

Bickbadger

Bickbadger

pbaldy

Wino Moderator

boblarson

Smeghead

Rabbie

Super Moderator

pbaldy

Wino Moderator

Rabbie

Super Moderator

pbaldy

Wino Moderator

boblarson

Smeghead

Rabbie

Super Moderator

Bickbadger

Bickbadger

boblarson

Smeghead

Bickbadger

Bickbadger

pbaldy

Wino Moderator

Bickbadger

Bickbadger

Similar threads

Users who are viewing this thread