Help with vba module opening form with blank record

[Garindan]

Hi all.

I have the following module in my database to allow for multiple alarms.

It all works great, except before showing the actual alarm record called, the form first pops up showing the first record in its record source.

I.e. it flashes up showing the first record in the table, then changes to the 'correct' record.

I would just like to change it so it either doesn't show until the record is loaded, or it shows blank to begin with. I hope that makes sense?

Here's the module...

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 = "Alarm! 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

Function CloseAllAlarms()
    'Purpose: Close all instances in the clnPopUpAlarm collection.
    'Note: Leaves the copy opened directly from database window.
    Dim lngKt As Long
    Dim lngI As Long
    
    lngKt = clnPopUpAlarm.Count
    For lngI = 1 To lngKt
        clnPopUpAlarm.Remove 1
    Next
End Function

And here's the form and event it's called from...

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

Thanks for any help!!!

 

[MarkK]

Try moving the Visible = True line to AFTER you set the filter.

 

[Garindan]

Brilliant! Thanks! Worked perfectly