Solved Using a query to filter a different query

[ClaraBarton]

Probably an oft repeated question but I can't seem to find the right question to get an answer...
I have FormA based on a calculated query. When I call FormB using FormA.Filter, the filter uses FormA query field name which doesn't work on FormB query field names even though they are both using the same tables. Using a where condition of Me.filter does not work.

 

[tvanstiphout]

>even though they are both using the same tables
If both forms use the same underlying tables, the filter should work in FormB.
Example filter: TableA.FieldA = 5
If FormB is also bound to TableA, the filter should work.

If that's not what you are seeing, can you upload a very simple repro database?

 

[Gasman]

You have to include the same fields in each form, at least those you are using.

 

[ClaraBarton]

Private Sub btnSplit_Click()
    WriteCheck 1, Me.Filter
End Sub

Public Sub WriteCheck(iMode As Integer, Optional strWhere As String)
     'acAdd = 0; acEdit = 1
 Dim strWhere2 As String
 
    If strWhere & "" = "" Then
       strWhere = ""
    Else: strWhere = " AND " & strWhere
    End If

    strWhere2 = "fAccountID = " & Me.Parent.txtAccountID
 strWhere = strWhere2 & strWhere

    DoCmd.OpenForm "frmCheck", _
        DataMode:=iMode, _
        WhereCondition:=strWhere, _
        OpenArgs:="frmAccounts"
End Sub

This is the Where Condition:

(Year([CkDate])=2025) AND ([5qryRegister].[FullName]="Fidelity Index Fund")

This is the query behind FormB

SELECT tblTransactions.TransactionID, tblTransactions.fAccountID, tblTransactions.
    fNameID, qryNames.FullName, tblTransactions.TTransID, tblTransactions.CkDate,
    tblTransactions.Num, tblTransactions.Payment, tblTransactions.Receipt,
    tblTransactions.Amount, tblTransactions.Cleared, tblTransactions.Modified,
    tblTransactions.PrintCk, tblTransactions.Memo, tblAccount.AccountName
FROM tblAccount
INNER JOIN (tblTransactions
LEFT JOIN qryNames ON tblTransactions.fNameID = qryNames.NameID)
ON tblAccount.AccountID = tblTransactions.fAccountID;

It balks on the FullName. I assumed because 5qryRegister.FullName isn't matching to qryNames.FullName. Maybe I'm missing something else?

 

[arnelgp]

you add this code in a Module or on your Form.
The code will Remove the Form/Table/Query reference from
your filter:

' Copilot
Function RemoveWordBeforeDot(sText As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    With regex
        .Pattern = "(\[?\w+\]?\.)"
        .Global = True
        .IgnoreCase = True
        
        RemoveWordBeforeDot = .Replace(sText, "")
    End With
    Set regex = Nothing

End Function

then change your code to:

Private Sub btnSplit_Click()
    WriteCheck 1, Me.Filter
End Sub

Public Sub WriteCheck(iMode As Integer, Optional strWhere As String)
     'acAdd = 0; acEdit = 1
 Dim strWhere2 As String
 
    If strWhere & "" = "" Then
       strWhere = ""
    Else: strWhere = " AND " & strWhere
    End If

    strWhere2 = "fAccountID = " & Me.Parent.txtAccountID
 strWhere = strWhere2 & strWhere

' arnelgp
strWhere = RemoveWordBeforeDot(strWhere)

    DoCmd.OpenForm "frmCheck", _
        DataMode:=iMode, _
        WhereCondition:=strWhere, _
        OpenArgs:="frmAccounts"
End Sub

the result will be:

(Year([CkDate])=2025) AND ([FullName]="Fidelity Index Fund")

 

[Gasman]

Private Sub btnSplit_Click()
    WriteCheck 1, Me.Filter
End Sub

Public Sub WriteCheck(iMode As Integer, Optional strWhere As String)
     'acAdd = 0; acEdit = 1
 Dim strWhere2 As String
 
    If strWhere & "" = "" Then
       strWhere = ""
    Else: strWhere = " AND " & strWhere
    End If

    strWhere2 = "fAccountID = " & Me.Parent.txtAccountID
 strWhere = strWhere2 & strWhere

    DoCmd.OpenForm "frmCheck", _
        DataMode:=iMode, _
        WhereCondition:=strWhere, _
        OpenArgs:="frmAccounts"
End Sub

This is the Where Condition:

(Year([CkDate])=2025) AND ([5qryRegister].[FullName]="Fidelity Index Fund")

This is the query behind FormB

SELECT tblTransactions.TransactionID, tblTransactions.fAccountID, tblTransactions.
    fNameID, qryNames.FullName, tblTransactions.TTransID, tblTransactions.CkDate,
    tblTransactions.Num, tblTransactions.Payment, tblTransactions.Receipt,
    tblTransactions.Amount, tblTransactions.Cleared, tblTransactions.Modified,
    tblTransactions.PrintCk, tblTransactions.Memo, tblAccount.AccountName
FROM tblAccount
INNER JOIN (tblTransactions
LEFT JOIN qryNames ON tblTransactions.fNameID = qryNames.NameID)
ON tblAccount.AccountID = tblTransactions.fAccountID;

It balks on the FullName. I assumed because 5qryRegister.FullName isn't matching to qryNames.FullName. Maybe I'm missing something else?

No, it is just not in that source?
I would just get rid of the word 5qryRegister

 

[ClaraBarton]

Thank you so much, Arnelgp. It works great!

 

[Gasman]

Thank you so much, Arnelgp. It works great!

Yep. Add a word and then remove it. Makes a lot of sense to me, NOT.