open subform filtered

  • Thread starter Thread starter alex
  • Start date Start date
A

alex

using access '03...

I’m trying to open a subform (filtered) related to a table called
tblIO2…

I’m using the following code to no avail (it opens the form filtered,
but nothing shows):

Dim stDocName As String
Dim strFilter As String
Dim strSQL As String

strSQL = "select tblIO.IDENTIFIER from tblIO where tblIO.UserID =
[Forms]![frmLogin]![LoginName]"

strFilter = "IDENTIFIER = 'strSQL'"
stDocName = "sfrmIO"

DoCmd.OpenForm stDocName, acFormDS, , strFilter

The SQL statement works (I’ve tested it). It appears that the
strFilter does not recognize strSQL!
If I remove strSQL from ["IDENTIFIER = 'strSQL'"] and insert an actual
result, the filter works fine.

Thanks for lookin'
 
You could set the filter of the subform like this:

Private Sub Form_Open(Cancel As Integer)
Dim strWhere As String
If CurrentProject.AllForms("frmLogin").IsLoaded Then
With Forms("frmLogin")!LoginName
If Not IsNull(.Value) Then
strWhere = "UserID = """ & .Value & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End If
End Sub

If UserID is a Number field (not a Text field), remove the extra quotes:
strWhere = "UserID = " & .Value
Explanation of the quotes:
http://allenbrowne.com/casu-17.html

If you don't want the user removing the filter, you might set the form's
RecordSource instead:
strSQL = "select tblIO.IDENTIFIER from tblIO where tblIO.UserID = """ &
..Value & """;"
Me.RecordSource = strSql

This would also avoid a bug in Access if you filter both the main form and
subform:
http://allenbrowne.com/bug-02.html
But check that the LinkMasterFields/LinkChildFields are still correct after
setting RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

using access '03...

I’m trying to open a subform (filtered) related to a table called
tblIO2…

I’m using the following code to no avail (it opens the form filtered,
but nothing shows):

Dim stDocName As String
Dim strFilter As String
Dim strSQL As String

strSQL = "select tblIO.IDENTIFIER from tblIO where tblIO.UserID =
[Forms]![frmLogin]![LoginName]"

strFilter = "IDENTIFIER = 'strSQL'"
stDocName = "sfrmIO"

DoCmd.OpenForm stDocName, acFormDS, , strFilter

The SQL statement works (I’ve tested it). It appears that the
strFilter does not recognize strSQL!
If I remove strSQL from ["IDENTIFIER = 'strSQL'"] and insert an actual
result, the filter works fine.

Thanks for lookin'
 
You could set the filter of the subform like this:

Private Sub Form_Open(Cancel As Integer)
    Dim strWhere As String
    If CurrentProject.AllForms("frmLogin").IsLoaded Then
        With Forms("frmLogin")!LoginName
            If Not IsNull(.Value) Then
                strWhere = "UserID = """ & .Value & """"
                Me.Filter = strWhere
                Me.FilterOn = True
            End If
        End With
    End If
End Sub

If UserID is a Number field (not a Text field), remove the extra quotes:
                strWhere = "UserID = " & .Value
Explanation of the quotes:
   http://allenbrowne.com/casu-17.html

If you don't want the user removing the filter, you might set the form's
RecordSource instead:
    strSQL = "select tblIO.IDENTIFIER from tblIO where tblIO.UserID= """ &
.Value & """;"
    Me.RecordSource = strSql

This would also avoid a bug in Access if you filter both the main form and
subform:
   http://allenbrowne.com/bug-02.html
But check that the LinkMasterFields/LinkChildFields are still correct after
setting RecordSource.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


using access '03...

I’m trying to open a subform (filtered) related to a table called
tblIO2…

I’m using the following code to no avail (it opens the form filtered,
but nothing shows):

Dim stDocName As String
Dim strFilter As String
Dim strSQL As String

 strSQL = "select tblIO.IDENTIFIER from tblIO where tblIO.UserID =
[Forms]![frmLogin]![LoginName]"

 strFilter = "IDENTIFIER = 'strSQL'"
 stDocName = "sfrmIO"

DoCmd.OpenForm stDocName, acFormDS, , strFilter

The SQL statement works (I’ve tested it).  It appears that the
strFilter does not recognize strSQL!
If I remove strSQL from ["IDENTIFIER = 'strSQL'"] and insert an actual
result, the filter works fine.

Thanks for lookin'
Click to expand...

Thanks Allen...I do filter both forms; I'll give it a try.
 
Back
Top