Same form Multiple Times

[Martyh]

Hi,

I have followed the Access Cookbook by Ken Getz et al. number 2.11 "Open Multiple Instances of a Form" for recreating my forms and the following code is what I came up with:

Public Sub acbAddForm(sCriteria As String, sCaption As String)
'Two major changes: 1) sCriteria, and 2)sCaption
'which change the criteria of the query and the caption of the form    

Dim frm As Form
    Set frm = New Form_frmqsNSN_NSNERN_ERN
    'My form called frmqsNSN_NSNERN_ERN
    'From the book:
    ' You have to convert the key to a string, so tack a "" onto
    ' the hWnd (which uniquely identifies each form instance)
    ' to convert it to a string.
    colForms.Add Item:=frm, Key:=frm.Hwnd & ""
    
    ' Build up the caption for each new instance.
    mintForm = mintForm + 1
    
    frm.Caption = sCaption

    'Added in two statements as follows:
    frm.FilterOn = True
    frm.Filter = sCriteria
    
    ' The numbers used here are arbitrary and are really useful
    ' only for this simple example.
    frm.SetFocus
    
    'Left this next statement out
    'DoCmd.MoveSize mintForm * acbcOffsetHoriz, mintForm * acbcOffsetVert
    
    ' Finally, set this form to be visible.
    frm.visible = True
End Sub

I work out the criteria in other portions of the program. A sample is given below:

...
    '%----------------------------------------------------------------
    If Me.cmdPostCMSG.Caption = "Post-CMSG" Then
        strCriteria = "Environment = 'L' AND Project = 'MR' AND (State = 3 OR State = 4)"
        tempQry = "LAND: NSNs on Obsolete ERNs"
    Else
        strCriteria = "Environment = 'L' AND Project = 'MR' AND (State = 3 OR State = 4) AND CMSG = No "
        tempQry = "LAND Post-CMSG: NSNs on Obsolete ERNs"
    End If
    Call acbAddForm(strCriteria, tempQry)
    
...

The problem is that it doesn't seem to understand the criteria even though it read the value of sCriteria as proven by the debugger!

I had this working about a year ago, but then management changed its criteria and I had to revise the input data and couple of other things. Also changed the operating system from Win 7 to Win 10, although I don't think this had anything to do with my problem.

I have turned all the filter command on and off and I am at the end of my knowledge. Perhaps you could help.

 

[theDBguy]

Hi Marty. What exactly is happening now? Are you getting any errors? Or, are the forms simply opening not filtered or maybe empty?

 

[Gasman]

Shouldn't you be setting the criteria first, then setting filter on?

 

[isladogs]

Well spotted Gasman.

I also wonder if the spaces or the colon are an issue. Try putting the names in [] brackets.
Better still, don't use either in your object names

 

[Martyh]

Everyone: Thanks for answering so quickly...
Gasman: BRILLIANT ... I can't believe how long I was looking at this .... and for the Second time

Thanks to all!

 

[theDBguy]

Everyone: Thanks for answering so quickly...
Gasman: BRILLIANT ... I can't believe how long I was looking at this .... and for the Second time

Thanks to all!

Hi. Glad to hear you got it sorted out. Good luck with your project.