deleteobject acform problem (1 Viewer)

Hello All

This is my first post here! I have been working with Access since Version 2, and have stuck with DAO and version 2000 format databases.

I have CRM customer management database, which includes an action diary attached to the contact table.
I can set an action for a specific time in the day.
When the time comes, CAT (my system) copies the standard AlarmPop form to Alarmpop856 (856 being calculated form the exact time)
I make the call or whatever, then close the form.
When the form closes, the form should be deleted.
but it wont....

The code is like this.

On click of BtnClose on the form

Private Sub BtnClose_Click()
‘ grab the form name and set it as a
gAlertForm = Me.Name
DoCmd.OpenForm "AlarmPopDel", acNormal, , , , acHidden
End sub

On open AlarmPopDel
I seem to need this hidden form to make the form close

Private Sub Form_Open(Cancel As Integer)
Dim iForm As String
iForm = gAlertForm
DoCmd.Close acForm, iForm
fncAlarmClose
End Sub


So far so good – the AlarmPop856 closes

Function fncAlarmClose()
Dim iForm As String
iForm = gAlertForm
DoCmd.DeleteObject acForm, iForm
gAlertForm = ""
DoCmd.Close acForm, "AlarmPopDel"
End Function

It stops at the DeleteObject
With error 2008, can’t delete AlarmPop856 while it is OPEN.

Really weird, because the form is definitely closed

Any ideas as to how to force the form to delete?

If you debug this code you'll see that when you try to execute this line ...
...the subroutine 'Private Sub BtnClose_Click()' is still running. Yes, you have called a close command on that form, and it will close, but code is still running in its module.

A couple of observations I have...
1) You can find a way to solve this problem without programmatically creating a specifically named form and programmatically deleting it. Just set the value of a hidden textbox on the form and have your consumers read that value. You can have multiple instances of a form too if that is necessary. Search for info on 'non-default form instance.'
2) Code on FormA can start a timer on FormB, FormA can close, and the FormB timer can delete FormA.


This is a nice little dodge to run an 'asynchronous' process.
Cheers,
Mark

Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first Command to be completed before starting the second one, and this can cause timing problems. It's possible, and seems probable, given the error message, that the Form is not, in fact, completely closed before the DeleteObject Command is run.

Frequently the answer to halting the code, in this type of situation, until one piece of code is completed, is to use DoEvents.

DoEvents returns control to Windows, allowing the first piece of code to complete before starting the next piece of code. DoEvents is an easy, safe bet when encountering what seems to be timing issues.
Linq ;0)>

Thank you for your thoughts.

The multi-form looks like a work around I may need to try

I have tried Ling's DoEvents, which would have been the neatest way to make work what I have. However it didn't work. tried putting it in twice. as suggested, then at the beginning of the function. But it still came up with error 2008, can't delete while it is open!

Any other ideas, before I start re-writing using multi-forms.

cheers

Peter

Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first Command to be completed before starting the second one

Click to expand...

This is not so. Step through the code after calling DoCmd.OpenForm. If the form you opened has a Sub Form_Open() event handler that entire routine completes before control is returned to the next line of calling code. That is not asynchronous behaviour.

Test this...
Am I missing something?
Cheers,

Hi thaks again. In the end it was LAGBOLT who provided the solution with multi-form.
The Search for info on 'non-default form instance.' pointed me to a great bit of code by Allen Browne in Australia. It works a dream. The only tweak that was needed to make the btnClose sub work, was to put 'option explcit' in the top lines of the code behind form.
Brilliant - I now have pop up reminders that link back to the contact record, and you can close one and the others stay open as long as you want.

the final code looked something like this ...

Option Explicit
Public clnClient As New Collection 'Instances of Alarmpop.
Function fncAlarmpop()
'Purpose: Open an independent instance of form AlarmPop frmClient.
On Error GoTo err_alarmpop
Dim frm As Form
Dim MyTime As Double
Dim iChk As Long

MyTime = Time()
iChk = Nz(DLookup("ContactID", "AlarmPopQry"))

If iChk > 0 Then
gContactid = iChk
'Open a new instance, show it, and set a caption.
Set frm = New Form_AlarmPop
frm.Visible = True
frm.Caption = DLookup("Captn", "AlarmPopform") 'frm.hwnd & ", opened " & Now()

'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.hwnd)
Set frm = Nothing

End If
Exit Function
'If erro = 3008 Then

'End If
err_alarmpop: errlog "in Timer Alarmpop"
Resume Next
End Function

******************

Private Sub BtnClose_Click()

On Error GoTo err_btnclose
SaveRec
'Purpose: Remove this instance from clnClient collection.
Dim obj As Object 'Object in clnClient

Dim blnRemove As Boolean 'Flag to remove it.

'Check if this instance is in the collection.
For Each obj In clnClient
If obj.hwnd = Me.hwnd Then
blnRemove = True
Exit For
End If
Next

'Deassign the object and remove from collection.
Set obj = Nothing
If blnRemove Then
clnClient.Remove CStr(Me.hwnd)
End If
Exit Sub
err_btnclose: errlog "in btnclose"
End Sub


The clever bit with collections is all Allen Browne - what a star!

The above code refers to queries which pick up the next action record.
the caption includes the company/contact name so when you minimize the alrm form, you can see whose it is.
Alarmpopquery sql
SELECT TOP 1 Calls.ContactID
FROM Calls
WHERE (((Calls.CallDate)=Date()) AND ((Calls.CallTime)<Time()) AND ((Calls.DiaryOffYN)=False) AND ((Calls.AlertYN)=Yes));

AlarmPopform query sql
SELECT Calls.AlertName, IIf([CompanyName] Is Not Null,[CompanyName],[lastname]) AS Captn, Contacts.FirstName, Calls.[Call Type], Calls.CallDate, Calls.CallTime, Contacts.Dear, Contacts.WorkPhone, Contacts.MobilePhone, Contacts.WorkExtension, Calls.DiaryOffYN, Calls.AlertYN, Calls.Subject, Calls.ContactID
FROM Contacts RIGHT JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Calls.CallDate)=Date()) AND ((Calls.CallTime)<=Time()) AND ((Calls.AlertYN)=Yes) AND ((Calls.ContactID)=fnccontactid()));

The queries look a bit of a mess. but it gives you an idea.
cheers Peter

Nicely done. To make sure Option Explicit appears at the top of every module automatically you can, in a code window, go to Tools->Options->Editor Tab->Code Settings Section and check 'Require Variable Declaration'. Then new modules will require variable declaration by default.
Cheers,
Mark