Call function arguments to open form at record number?

[Garindan]

Hi all, I have been implementing code to allow me to have multiple instances of a 'pop up alarm' form from Allen Browne.

I have added and altered all the code, I just need a little help with the last bit.

My startup form timer had code to open the alarm form at the correct alarm using the 'CommentNumber' field and a string as below. Then simple DoCmd.Open code....

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
    DoCmd.OpenForm "frmPopUpAlarm", acNormal, , "[CommentNumber] = " & varCommentNumber
    End If

End Sub

The final part I need to do is change this line DoCmd.OpenForm "frmPopUpAlarm", acNormal, , "[CommentNumber] = " & varCommentNumber for a Call function - Call OpenAnAlarm()

However, I'm unsure how to call the alarm form at the correct 'CommentNumber'. Could somebody help me with this bit?

I have Call OpenAnAlarm("[CommentNumber] = " & varCommentNumber) but I know it isn't correct

Many thanks for any help!!!

 

[pbaldy]

That could work fine, as long as OpenAnAlarm was set up to use it that way. What does that function look like?

 

[Garindan]

Hi Paul, thanks for the reply. The function is below...

Option Compare Database
Option Explicit

Public clnPopUpAlarm As New Collection  'Instances of frmPopUpAlarm

Function OpenAnAlarm()
    '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)
    Set frm = Nothing
End Function

 

[ChrisO]

Assuming it is being used as a Filter on the popup…

A simplified test, add the other bits back in after testing: -

Private Sub Form_Timer()
    Dim varCommentNumber As Variant
    
    varCommentNumber = 1234

    Call OpenAnAlarm("[CommentNumber] = " & varCommentNumber)
    
End Sub

And:-

Public clnPopUpAlarm As New Collection


Function OpenAnAlarm(ByVal strFilter As String)
    Dim frm As Form
    
    Set frm = New Form_frmPopUpAlarm
    frm.Visible = True
    frm.Caption = frm.Hwnd & ", opened " & Now()
    
    clnPopUpAlarm.Add Item:=frm, Key:=CStr(frm.Hwnd)
    
    frm.Filter = strFilter
    frm.FilterOn = True
    
    Set frm = Nothing
    
End Function

Hope that gets you started.

Chris.

 

[Garindan]

Hi Chris thanks for the reply. I tried both code suggestions you gave, and have found the following...

If I try the first bit of code and just try the varCommentNumber as a simple number with the Call OpenAnAlarm() code, I get the same error as before... the form opens at the first record and a dialog box opens with error 13, type mismatch.

I think there wasn't much wrong with the form_timer code, I already knew the strwhere string and varCommentNumber worked fine because the original code worked (until I tried to add the Call OpenAnAlarm ()

So I put the first code back as follows

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 tried to add the form filter to the code as per your second bit of code.

Once I had added the filter, everything worked fine! The form opened at the correct record, and there were no errors. I thought it was done!

However.... I had a 'AlarmViewed' Yes/No field on the 'PopUpAlarm' form as a checkbox, and the code

Private Sub Form_Load()
Me![cbAlarmViewed] = -1
End Sub

This was to mark the record as viewed when the form loaded, which was then used in another form. With the form filter set in the code I noticed it doesn't change the AlarmViewed field to Yes for the record. Instead it changes this field for the first record in the table! I guess this is because the first record loads first with form_load, and THEN the filter is applied. Does this sound correct?

Could I just move the Me![cbAlarmViewed] = -1 to another event to solve this?

Cheers!!

 

[Garindan]

Ok I moved the Me![cbAlarmViewed] = -1 to the command buttons which close the form (one to open another form, one to ignore the alarm) and it all seems to be working fine at the moment fingers crossed!