DoCmd Close problem, reference instance of multiple form

[Garindan]

Hi all, I know this can be tricky from searching myself but I couldn't find an answer...

I have code for multiple instances of a form from Allen Browne http://allenbrowne.com/ser-35.html

It works fine, I just have a problem with closing the forms.

The form I use the code on has two buttons to close the form (with different actions). One just uses DoCmd.Close and works, i.e. it closes the instance of the form it is meant to. I am unsure if this is foolproof though?

The other however, loads another form first, so DoCmd.Close simply closes the other form which was opened. 'DoCmd.Close acForm, Me.Name' however will not work because it closes the first instance of the form which is open. On another click it will close the second instance of the form which is open, then the third, fourth, etc.

I am unsure how to reference the instance of the form to close the right one. I naively tried DoCmd.Close acForm, Me.hWnd and DoCmd.Close acForm, frm.hWnd but these did not work

Any ideas?? Many thanks in advance!

Here's the two buttons... as I said, the btnIgnoreAlarm_Click works, the btnOpenCustomerForm_Click doesn't...

Private Sub btnOpenCustomerForm_Click()
On Error GoTo Err_btnOpenCustomerForm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim lngArgs As Long

    stDocName = _
      "frmCustomerSearchDataEntry"
    stLinkCriteria = _
      "[CustomerNumber]=" & Me![CustomerNumber]
    lngArgs = _
      Me![CommentNumber]

    DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria, OpenArgs:=lngArgs
    
    Me![cbAlarmViewed] = -1
    
    ' Force any unsaved changes
    If Me.Dirty Then Me.Dirty = False
    
    ' Not working
    [COLOR="Red"]DoCmd.Close acForm, Me.Name[/COLOR]

Exit_btnOpenCustomerForm_Click:
    Exit Sub

Err_btnOpenCustomerForm_Click:
    MsgBox Err.Description
    Resume Exit_btnOpenCustomerForm_Click
    
End Sub
Private Sub btnIgnoreAlarm_Click()
On Error GoTo Err_btnIgnoreAlarm_Click

    If MsgBox("This will dismiss/ignore the alarm, it will not show again. Continue?", vbOKCancel, "Warning") = vbOK Then
            DoEvents
            
            Me![cbAlarmViewed] = -1
    
            ' Force any unsaved changes
            If Me.Dirty Then Me.Dirty = False
            
            ' Working
            [COLOR="red"]DoCmd.Close[/COLOR]
    End If

Exit_btnIgnoreAlarm_Click:
    Exit Sub

Err_btnIgnoreAlarm_Click:
    MsgBox Err.Description
    Resume Exit_btnIgnoreAlarm_Click
    
End Sub

Here's the rest of the code for the function...

Option Compare Database
Option Explicit

Public clnPopUpAlarm As New Collection  'Instances of frmPopUpAlarm

Function OpenAnAlarm(ByVal strFilter As String)

    'Purpose:   Open an independent instance of form frmPopUpAlarm
    Dim frm As Form
    
    'Open a new instance, show it, and set a caption.
    Set frm = New Form_frmPopUpAlarm
    frm.Visible = True
    frm.Caption = frm.hWnd & ", opened " & Now()
    
    'Append it to our collection.
    clnPopUpAlarm.Add Item:=frm, Key:=CStr(frm.hWnd)
    
    frm.Filter = strFilter
    frm.FilterOn = True
    
    Set frm = Nothing
    
End Function

The 'startup' form timer code which Calls the function and opens the frmPopUpAlarm'...

Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 60000
End Sub

Private Sub Form_Timer()
    
    Dim strWhere As String
    Dim varCommentNumber As Variant
  
    strWhere = "Format([CommentAlarm], ""yyyymmddhhnn"") = '" & _
    Format(Now(), "yyyymmddhhnn") & "' AND " & _
    "[AlarmComputerName] = '" & Environ("Computername") & "'"
    varCommentNumber = (DLookup("[CommentNumber]", "tblCustomerComments", strWhere))
  
    If IsNull(varCommentNumber) Then
    ' There's no current comment for that machine
    Else
    Call OpenAnAlarm("[CommentNumber] = " & varCommentNumber)
    End If

End Sub

And the code in the Close event of frmPopUpAlarm which removes the instance of the form when the form is closed...

Private Sub Form_Close()
    'Purpose: Remove this instance from the clnPopUpAlarm collection.
    Dim obj As Object           'Object in clnPopUpAlarm
    Dim blnRemove As Boolean    'Flag to remove it.
    
    'Check if this instance is in the collection.
    '   (It won't be if form was opened directly, or code was reset.)
    For Each obj In clnPopUpAlarm
        If obj.hWnd = Me.hWnd Then
            blnRemove = True
            Exit For
        End If
    Next
    
    'Deassign the object before removing from collection.
    Set obj = Nothing
    If blnRemove Then
        clnPopUpAlarm.Remove CStr(Me.hWnd)
    End If
End Sub

I found lengthy code to close a form with an API command, but I'm not sure if there's a simpler way/if this code is needed, or if it will work ok with my code?

You can use the following code which sends an API command to the specific
window:
 
============== start code ==============
Option Explicit
 
Private Declare Function SendMessage _
Lib "user32" _
Alias "SendMessageA" ( _
ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any _
) As Long
 
Private Const WM_CLOSE = &H10
 
Public Function frm_Close( _
ByRef frm As Access.Form, _
Optional fQuiet As Boolean _
) As Long
' Returns 0 if successful, otherwise an error value
Dim lRet As Long, hWnd As Long, Name
On Error GoTo ProcErr
hWnd = frm.hWnd
Call SendMessage(hWnd, WM_CLOSE, 0&, 0&)
1:
If frm.hWnd = hWnd Then
' form still open
Err.Raise 2501, , "Form '" & frm.Caption & "' may not be closed at this
time"
End If
ProcEnd:
If lRet = 0 Then Set frm = Nothing
frm_Close = lRet
Exit Function
ProcErr:
If Err = 2467 And Erl = 1 Then
' form was successfully closed
lRet = 0
Else
If Not fQuiet Then MsgBox Err.Description, vbExclamation, "Cannot close
form"
lRet = Err
End If
Resume ProcEnd
End Function
=============== End Code =================
 
Call it like this:
 
frm_Close Me

 

[ChrisO]

I think you are going to kick yourself…

' Not working
' DoCmd.Close acForm, Me.Name
Form_Close

Chris.

 

[ChrisO]

Addendum:

You show a considerable effort in your posting and the code presented.
However, you may wish to condense the code to its essence for testing.


For testing, this is all that is required behind the popup Form: -

Private Sub btnOpenCustomerForm_Click()
    DoCmd.OpenForm "frmCustomerSearchDataEntry"
    Form_Close
End Sub

Private Sub btnIgnoreAlarm_Click()
    Form_Close
End Sub

Private Sub Form_Timer()
    Me.Visible = True
    Me.TimerInterval = 0
End Sub

Private Sub btnDismiss_Click()
    If Len(Me.txtDismissMins) Then
        Me.TimerInterval = Me.txtDismissMins * 20000
        Me.Visible = False
    End If
End Sub

Private Sub Form_Close()
    Dim obj As Object
    
    For Each obj In clnPopUpAlarm
        If obj.Hwnd = Me.Hwnd Then
            clnPopUpAlarm.Remove CStr(Me.Hwnd)
        End If
    Next
    
End Sub

That code describes the flow of execution, and that’s all.
If we strip out all the unnecessary junk we can more easily see what’s happening.
No comments; the code during development has not been proved so why comment unproved code?

Example: in the Form_Close procedure I removed the Boolean flag and the exit for. The reason is that they are not required. Allen may have put them in there for some reason, probably speed, but I very much doubt it could be justified in this case.

Let’s look at that point a little more closely. In the clnPopUpAlarm collection there is one and one only instance of obj.Hwnd. One could therefore assume that once we have found a match then we will not find another match. That is correct, but the assumption that once we have found a match we necessarily need to stop checking is incorrect.

Here’s why. A collection of Form instances could not be very large. The time to get to the end of the collection would probably be measured in microseconds. Let’s for the sake of the argument assume 1000 microseconds. Your screen update time is around 16000 microseconds. How long does it take the user to see the removal of the Form from the screen? At least 16 times longer it takes to reach the end of the collection. Furthermore, the removal of the Form was based on a user initiated event. Did the user hit the button Now() or 16 milliseconds after Now()? (Rhetorical)

So in order to save time, that the user could not possibly see, we include a Boolean which supposedly needs commenting. More code to go wrong, more code that needs commenting, more maintenance and for what? (Rhetorical) The Boolean has no practical return for effort.

The same sort of thing goes for: -
Set obj = Nothing

Why? The common answer to that question is that everybody recommends it. Well, that’s not true, I don’t.
It’s an urban myth, a superstition, a carry over from times gone by and which may not have existed in the first place. (Apart from DOS which did not have garbage collection.)
Personally I’ll leave it out, hoping that something might go wrong in order to prove the point one way or the other.

Until then I think we should program for a reason and not for some myth or superstition.

Chris.

 

[Garindan]

Brilliant, thank you Chris! You're a star and thanks for the little lesson