Deleting a Form at the press of a Button

**LeonS** · 09-26-2021, 04:58 AM

Hello! I have a form (ThoughtsF) on my db that I wish to give the user the option of keeping it of deleting it. The form (ThoughtsF) opens with the There is a table (ThoughtsT) and a query (ThoughtsQ). I have tried with a macro and I have tried with the below code. As you can see, I have even tried including a delay between the form being closed and being deleted. To no avail. I believe there is only a need to delete the form as the table and query do not take up much space.
The "Delete" option is under a button on the form.
The form “ThoughtsF” is in the On Load Event procedure of another form. They both open at the same time.
If the user presses Yes, the form should be deleted, never to be seen again.
If they press No, the message box should disappear.

The code I have been working on/ with is below. Any advice will be welcome. Leon

Code:

Private Sub DeleteThought_Click()
    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Are you sure you want to throw this away?", vbYesNo, "Delete Thought of the Day")
    DoCmd.Close acForm, "ThoughtsF"
    DoCmd.Close acTable, "ThoughtsT"
    DoCmd.Close acQuery, "ThoughtsQ"
'    Me.TimerInterval = 2000
    DoCmd.DeleteObject "ThoughtsF"
DeleteThought_Click_Err:
End Sub

**isladogs** · 09-26-2021, 05:18 AM

1. You can't delete an object when it is in use
2. A user might want to delete a record (the thought for that day) BUT NOT the entire form, table & query

My 'thought' is that you are both overthinking this and misunderstanding how a database should be used

**Micron** · 09-26-2021, 10:11 AM

Aside from the fact you can't delete the form you're on, you forgot to specify the object type before its name. Not passing judgement, just an opinion that this is one of the strangest requests I've seen here yet.

If you really need it, you'll have to come up with another trigger. Perhaps set a flag somewhere (e.g. the yes answer sets a T/F field to true) and on db close a hidden form that is always open runs code in its Unload event to check that field value (or perhaps just a global variable). However, now what about an always hidden form that tries to delete an object that no longer exists? Obviously you have to trap for that. Seems like a much ado about nothing when you could simply not show that form again. Should it ever be needed again, it's still there.

**LeonS** · 09-26-2021, 07:05 PM

Micron - thanks for that. As I have said already, I know that I have to close the form before I can delete it. That is in the code - it does not work (with or without specifying the object type). And, I have tried many variations on the code - from advice, including that on MS Support. It does elsewhere. I want to close the form. I am trying to close the form. The error message reminds me that I cannot delete a form whilst it is open. That is obvious even to a simpleton like me. I do not understand why, having run the DoCmd.Close command, the form is still open.
I will try another route. That of having a check box on the form. Which will have the form deleted after the form has been closed and the user moves on to something else. My exact route is not decided yet. But clearly my current attempts to close the form are not working and no-one has suggested how I might achieve that.
As you know, I am not a fully trained or experienced programmer. I understand some of the concepts and most of my successes are based on my experience, trial and error and, very importantly, expert advice.
As far as the facility I am trying to introduce is concerned, it is an existing facility on a number of programs. It is the "Please do not show this again" - usually attached to some procedure about which the user does not necessarily need repeated reminders. There is one in my website building program, which offers "Helpful Tips" - and I have the option to turn it off.
The form I have produced is based on a table and query. It is a "Thought for the Day" pop up form which appears every time the db is opened. Via a RND query, a new Thought of the Day is shown each time (together with its "author"). I currently have over 40 quotes from which Access can choose. Being a marketing man, I thought it would be a nice touch. Clearly there are people out there (mainly men, I suspect) that would see such a "service" as a waste of time and energy. For them I want an option whereby they can prevent it from showing the next time.
I hope this explains my ideas and how I hope to move forward and solve the problem. Thank you for your help and contribution. Leon

**Micron** · 09-26-2021, 08:30 PM

I do not understand why, having run the DoCmd.Close command, the form is still open.

You can tell it to close the form but it is a delayed action if you have code after that. Only after the End Sub line will the form truly close and any code before that will be processed. Just because you can't see it doesn't mean it is closed, even if it's only for a micro-second.

attempts to close the form are not working and no-one has suggested how I might achieve that.

I'd say I did that in my first post but I'd also say you didn't ask how to close it, you asked how to delete it.

Suppressing some sort of reminder is not the same as deleting the form that presents the reminder IMO. Either your form should be bound to a table and the checkbox on a field in that table so that the check either allows or suppresses the message OR your unbound form and control requires code to set that table value. Easier to bind the form and control AFAIC. There is no way I'd delete an object to suppress a feature.

**LeonS** · 09-27-2021, 01:54 AM

Micron - please excuse my use of certain words which may mean different things in Access. Thank you for your message. I have decided to go via different route, and I would appreciate your comments.
I have the code for the message box. I plan to include Cancel as well as Yes and No.
I will add a CheckBox to the form and use a label to describe it. It will ask the user to tick if they do not want to see the form again.
I plan to use the CheckBox to eventually HIDE the form, not delete it. But to hide it permanently.
Like you, I do not like to delete in a database. For example with contact records, I have a Tick Box (Y/N/D) to indicate whether the person is active.
The only reason I was using delete for the form was that I saw no other way.
The code could be something like

Code:

The code from the Yes/No/Cancel box as before. Followed by:
If Check = True Then
Form ThoughtsF = False
End If

I am not sure yet how to make it permanent.
I plan to add the code that will make this happen to the Close Form button.
I should add that the ThoughtsF form is on a timer and is only visible for about 5/6 seconds. It does not change the "Thought" until the db is re-opened.
Does that sound like a reasonable plan and do you have any advice on that? Thanks, Leon

**isladogs** · 09-27-2021, 06:24 AM

OK now you have have clarified what you meant, I thought you might be interested in the example app attached.
This is a cut down version from one of my commercial apps for schools and displays a random set of tips about that app at startup
Users can choose to bypass that form if they choose

To help grab users' attention, the rest of the screen is automatically dimmed when the tips form is shown