Call function arguments to open form at record number?

Garindan · May 10, 2011

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....

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 · May 10, 2011

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

Garindan · May 10, 2011

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

Code:

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 · May 10, 2011

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

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

Code:

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

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

And:-

Code:

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 · May 11, 2011

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

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
    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

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 · May 11, 2011

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!

Call function arguments to open form at record number?

Garindan

Registered User.

pbaldy

Wino Moderator

Garindan

Registered User.

ChrisO

Registered User.

Garindan

Registered User.

Garindan

Registered User.

Similar threads

Users who are viewing this thread