Subform doesn't appear if filter applied to main form

[markmarko]

I have a procedure that concatenates some WHERE style sql, sticks that into
the .filter property for the form, then sets .filteron = true. The moment
this happens, one of the subforms on the page stops displaying.

Using breakpoints I've seen that it is setting .filteron = true that
triggers it.

The subform will display the header which consists of a text label, but none
of the textbox controls in the Detail section appears. I know that there is
data for the subform to display, since it had displayed prior to applying a
filter to the main form.

It seems to affect all records, except the first (!?). The problem persists
even after the filter is removed.

Help!

 

[markmarko]

A little more info...

As a test I've removed the me.filteron = true line from the code, then ran
the filter procedure. It ends up setting me.filter = WHEREstring (ie
WHEREstring = [AccountNumber] Like "*435335*")

So basically, the procedure has set the filter, but has not turned it on. I
then turn it on manually with the filter toggle in the Nav panel. This is
effectively does what the procedure is meant to.

After applying the filter manually, the subform continues to show data.

The upshot is that it is literally the code 'me.filteron = true' that is
causing this problem.

Any thoughts?

 

[Ken Snell \(MVP\)]

Show us the entire code that you're running for this, including the code
that sets the filter string. Is the code running in the main form or in the
subform?

 

[markmarko]

The code was running on the main form. It's pretty lengthy so I was hoping
not to have to do this, but here it is...


Private Sub FilterButton_Click() ' Used to filter the form.
' This filter originally operated by concatenating an sql string, and
applying it as recordsource. It's has been changed to keep the recordsource
the same (Query-SalesOrderForm), and apply the sqlWHEREString
' to the filter, and turn the filter on.
Dim lngLen As Long 'Length of the criteria string
to append to.
Dim strRecordSource As String
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
Dim FirstDayOfWeek As Integer ' to allow we searches to vary if systems
have different weekendings - applies to DatePart function
Dim DateCriteria As String
Dim SELECTString As String
Dim FROMString As String
Dim WhereString As String
Dim WHEREStringBase As String
Dim WHEREStringAdditions As String 'The criteria string.
Dim ORDERBYString As String
Dim SalesRepCriteria As String
Dim InstallerCriteria As String
Dim WHEREDateFilterString As String
Dim WHERECompopr As String 'comparison operator (=, <, >, etc)
' Initialize Error Handling
If Not (fOSMachineName = "MARKDESKTOP" And TempVars!TempUser = 3) Then ' to
inhibit error handling, allowing easier debuging
On Error GoTo ErrorCode:
End If

'Begin Main Code

DoCmd.Hourglass True

If Me.Dirty = True Then 'make sure enough to make a record
If Not (Nz([AssociatedCustAcct#Junction])) > 0 Then 'if there's a junction
MsgBox ("Some data is entered, but not enough to make a record. Either
cancel (hit ESC twice), or enter at least the items marked with red.")
GoTo ExitPoint:
End If
End If

Me!FoundMatchesIndicator.Visible = False

DoCmd.GoToControl "CriteriaTabControl"

' SELECTString = "SELECT [Record-Orders-Sales].* ,
[Query-Junction-Cust_Account].*, [List-InstallJobStatuses].InstallStatus"
' FROMString = "FROM ([Record-Orders-Sales] INNER JOIN
[Query-Junction-Cust_Account] ON
[Record-Orders-Sales].[AssociatedCustAcct#Junction] =
[Query-Junction-Cust_Account].JunctionID) " & vbCrLf & _
"LEFT JOIN ([List-InstallJobStatuses] RIGHT
JOIN [Record-Orders-Install] ON [List-InstallJobStatuses].ID =
[Record-Orders-Install].JobStatus) " & vbCrLf & _
"ON [Record-Orders-Sales].SalesOrderID =
[Record-Orders-Install].AssociatedSalesOrder"

SELECTString = "SELECT [Record-Orders-Sales].*,
[Query-Junction-Cust_Account].*"
FROMString = "FROM [Record-Orders-Sales] INNER JOIN
[Query-Junction-Cust_Account] " & vbCrLf & _
" ON [Record-Orders-Sales].[AssociatedCustAcct#Junction] =
[Query-Junction-Cust_Account].JunctionID"

'allow to filter for 'dead-data entry error'
' If Nz(Me.[Filter-OrderStatus]) = 6 Then
' WHEREStringBase = "WHERE nz([OrderStatus])= 6 " & vbCrLf
' Else
' WHEREStringBase = "WHERE nz([OrderStatus])<> 6 " & vbCrLf
' End If
WHEREStringBase = "WHERE 1=1 " & vbCrLf ' dummy value just so
WHEREStringBase is not null

ORDERBYString = "ORDER BY [Record-Orders-Sales].SalesOrderID;"


'Set initial Values
FirstDayOfWeek = 0 'Sunday.
strRecordSource = "Query-SalesOrderForm"

'SalesOrderID
If Nz(Me.[Filter-SalesOrderID]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([SalesOrderID]
= " & Me.[Filter-SalesOrderID] & ")" & vbCrLf
End If
'Corp
If Nz(Me.[Filter-Corp]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([Corp] = " &
Me.[Filter-Corp] & ")" & vbCrLf
End If
'System
If Nz(Me.[Filter-System]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([System] = " &
Me.[Filter-System] & ")" & vbCrLf
End If
'Area
If Nz(Me.[Filter-Area]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([System] = " &
Me.[Filter-System] & ")" & vbCrLf
End If
'AccountNumber
If Nz(Me.[Filter-AccountNumber]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND
([AccountNumber] Like ""*" & Me.[Filter-AccountNumber] & "*"")" & vbCrLf
End If
'AddressNumber
If Nz(Me.[Filter-AddressNumber]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND
([AddressNumber] Like ""*" & Me.[Filter-AddressNumber] & "*"")" & vbCrLf
End If
'StreetName
If Nz(Me.[Filter-StreetName]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([StreetName]
Like ""*" & Me.[Filter-StreetName] & "*"")" & vbCrLf
End If
'CustomerLastName
If Nz(Me.[Filter-CustomerLastName]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND
([Core-CustomerNames.CustomerLastName] Like ""*" &
Me.[Filter-CustomerLastName] & "*"")" & vbCrLf
End If
'Phone Number (Check against both phone fields)


If Nz(Me.[Filter-Phone]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & _
" AND exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Junction-Customers_Account#s].[JunctionID]" &
vbCrLf & _
" FROM [Junction-Customers_Account#s] " & vbCrLf & _
" WHERE [Junction-Customers_Account#s].[JunctionID] =
[Record-Orders-Sales].[AssociatedCustAcct#Junction] " & vbCrLf & _
" AND" & vbCrLf & _
" (exists " & vbCrLf & _
" (SELECT [Core-CustomerNames].ID" & vbCrLf & _
" FROM [Core-CustomerNames] " & vbCrLf & _
" WHERE [Core-CustomerNames].[ID] =
[Junction-Customers_Account#s].[CustomerLastName] " & vbCrLf & _
" AND [Core-CustomerNames].[HomePhone] Like ""*" &
Me.[Filter-Phone] & "*"") " & vbCrLf & _
" OR exists" & vbCrLf & _
" (SELECT DupePhone.ID" & vbCrLf & _
" FROM [Core-CustomerNames] as DupePhone " & vbCrLf & _
" WHERE DupePhone.[ID] =
[Junction-Customers_Account#s].[CustomerLastName] " & vbCrLf & _
" AND DupePhone.[AltPhone] Like ""*" &
Me.[Filter-Phone] & "*"") " & vbCrLf & _
" )" & vbCrLf & _
" )"
End If

'Order Status
If Nz(Me.[Filter-OrderStatus]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([OrderStatus]
= " & Me.[Filter-OrderStatus] & ")" & vbCrLf
End If
'Type Of Sale
If Nz(Me.[Filter-TypeOfSale]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND
([TypeOfSalesOrder] = " & Me.[Filter-TypeOfSale] & ")" & vbCrLf
End If
'Hide Dead Jobs
If Nz(Me.[Filter-HideDeadJobs]) = -1 Then
WHEREStringAdditions = WHEREStringAdditions & " AND
nz([OrderStatus])<> 6 AND nz([OrderStatus])<> 7 AND nz([OrderStatus])<> 8 AND
nz([OrderStatus])<> 12" & vbCrLf
End If

'Alchemy
If Nz(Me![Filter-SpecialProject]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([SpecialProject]
= " & Me.[Filter-SpecialProject] & ")" & vbCrLf
End If



'Sale Only
If Nz(Me.[Filter-SOorSI]) = "Sale Only" Then 'determine if Sale Only or
Sale with
WHEREStringAdditions = WHEREStringAdditions & _
" AND not exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
" FROM [Record-Orders-Install] " & vbCrLf & _
" WHERE [Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" )" & vbCrLf
End If
'Sale with Install
If Nz(Me.[Filter-SOorSI]) = "Sale with Install" Then 'determine if Sale
Only or Sale with
WHEREStringAdditions = WHEREStringAdditions & _
" AND exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
" FROM [Record-Orders-Install] " & vbCrLf & _
" WHERE [Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" )" & vbCrLf
End If
'Notes
If Nz(Me.[Filter-Notes]) > 0 Then
WHEREStringAdditions = WHEREStringAdditions & " AND ([Notes] Like
""*" & Me.[Filter-Notes] & "*"")" & vbCrLf
End If

'NeedsQualifying **************************
If Me.[Filter-NeedsQualifying] = True Then
' WHEREStringAdditions = WHEREStringAdditions & " AND
(not(len(nz([QualifyingStatus])) > 0 ) or QualifyingStatus <> 'Awaiting
Qualifying') " & vbCrLf & _
" AND exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
" FROM [Record-Orders-Install] " & vbCrLf & _
" WHERE [Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" )" & vbCrLf
WHEREStringAdditions = WHEREStringAdditions & " AND
(not(len(nz([QualifyingStatus])) > 0 ) ) " & vbCrLf & _
" AND exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
" FROM [Record-Orders-Install] " & vbCrLf & _
" WHERE [Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" )" & vbCrLf
End If
'AwaitingQualifying **************************
If Me.[Filter-AwaitingQualifying] = True Then
WHEREStringAdditions = WHEREStringAdditions & " AND
QualifyingStatus = 'Awaiting Qualifying' " & vbCrLf
End If
'NeedsToBeScheduled **************************
If Me.[Filter-NeedsScheduling] = True Then
WHEREStringAdditions = WHEREStringAdditions & vbCrLf & _
" AND not exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Record-SalesScheduling].[ID]" & vbCrLf & _
" FROM [Record-SalesScheduling] " & vbCrLf & _
" WHERE [Record-SalesScheduling].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" AND not
isnull([Record-SalesScheduling].ScheduledDate)" & vbCrLf & _
" )" & vbCrLf
End If


'NeedsPrecall
'If Me.[Filter-NeedsPrecall] = True Then
' WHEREStringAdditions = WHEREStringAdditions & " AND
(not(len(nz([PrecallStatus])) > 0 ))" & vbCrLf
'End If

'SalesRep1 and
SalesRep2_____________________________________________________
If Nz(Me.[Filter-SalesRep1]) > 0 Then
SalesRepCriteria = " AND exists " & vbCrLf & _
" (SELECT [Junction-SalesOrder_Contractors].ID" & vbCrLf & _
" FROM [Junction-SalesOrder_Contractors] " & vbCrLf & _
" WHERE
[Junction-SalesOrder_Contractors].[AssociatedSalesOrderID] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" AND
[Junction-SalesOrder_Contractors].[AssociatedContractorID] = " &
Me.[Filter-SalesRep1] & ") " & vbCrLf
End If

If Nz(Me.[Filter-SalesRep2]) > 0 Then
SalesRepCriteria = SalesRepCriteria & vbCrLf & " AND exists " &
vbCrLf & _
" (SELECT Dupe.ID" & vbCrLf & _
" FROM [Junction-SalesOrder_Contractors] as Dupe" & vbCrLf
& _
" WHERE Dupe.[AssociatedSalesOrderID] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" AND Dupe.[AssociatedContractorID] = " &
Me.[Filter-SalesRep2] & ")" & vbCrLf
End If

WHEREStringAdditions = WHEREStringAdditions & SalesRepCriteria

'Installer1 and
Installer2_____________________________________________________
If Nz(Me.[Filter-Installer1]) > 0 Then
InstallerCriteria = " AND exists " & vbCrLf & _
" (" & vbCrLf & _
" SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
" FROM [Record-Orders-Install] " & vbCrLf & _
" WHERE [Record-Orders-Install].[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" AND exists " & vbCrLf & _
" (SELECT [Junction-InstallOrder_Contractors].ID" &
vbCrLf & _
" FROM [Junction-InstallOrder_Contractors] " & vbCrLf
& _
" WHERE
[Junction-InstallOrder_Contractors].[AssociatedInstallOrderID] =
[Record-Orders-Install].[InstallOrderID] " & vbCrLf & _
" AND
[Junction-InstallOrder_Contractors].[AssociatedContractorID] = " &
Me.[Filter-Installer1] & ") " & vbCrLf & _
" )"
End If

If Nz(Me.[Filter-Installer2]) > 0 Then
InstallerCriteria = InstallerCriteria & vbCrLf & " AND exists " &
vbCrLf & _
" (" & vbCrLf & _
" SELECT Dupe.InstallOrderID" & vbCrLf & _
" FROM [Record-Orders-Install] as Dupe" & vbCrLf & _
" WHERE Dupe.[AssociatedSalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" AND exists " & vbCrLf & _
" (SELECT Dupe2.ID" & vbCrLf & _
" FROM [Junction-InstallOrder_Contractors] as Dupe2" &
vbCrLf & _
" WHERE Dupe2.[AssociatedInstallOrderID] =
Dupe.[InstallOrderID] " & vbCrLf & _
" AND Dupe2.[AssociatedContractorID] = " &
Me.[Filter-Installer2] & ") " & vbCrLf & _
" )" & vbCrLf
End If

WHEREStringAdditions = WHEREStringAdditions & InstallerCriteria


'____________________________________________________________________________

'OrderHasCDV
If Me.[Filter-OrderHasCDV] = True Then
WHEREStringAdditions = WHEREStringAdditions _
& " AND exists " & vbCrLf & _
" (SELECT [Query-SalesDetailsSubform].ID" & vbCrLf & _
" FROM [Query-SalesDetailsSubform] " & vbCrLf & _
" WHERE [Query-SalesDetailsSubform].[SalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
" AND left([Query-SalesDetailsSubform].

,3) =
""CDV"") "
End If

'IsSepCDV
If Me.[Filter-IsSepCDV] = True Then
WHEREStringAdditions = WHEREStringAdditions & "  AND
(nz([ForCDVAssociatedSalesOrder]) > 0 )" & vbCrLf
End If
'IsNOTSepCDV
If Me.[Filter-IsNOTSepCDV] = True Then
WHEREStringAdditions = WHEREStringAdditions & "  AND
(nz([ForCDVAssociatedSalesOrder]) = 0 )" & vbCrLf
End If

'CDV Needs ScheduleDate
'   If Me.[Filter-CDVneedsScheduleDate] = True Then
'       WHEREStringAdditions = WHEREStringAdditions & vbCrLf _
'        & "  AND not exists " & vbCrLf & _
'              "    (SELECT [Query-CDVdetails].ID" & vbCrLf & _
'              "    FROM [Query-CDVdetails] " & vbCrLf & _
'              "    WHERE [Query-CDVdetails].[AssociatedCDV_SalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
'              "    AND len(nz([Query-CDVdetails].[CDV_ScheduledDate])) > 0
) " & vbCrLf _
'        & "   AND exists " & vbCrLf & _
'              "    (SELECT [Query-SalesDetailsSubform].ID" & vbCrLf & _
'              "    FROM [Query-SalesDetailsSubform] " & vbCrLf & _
'              "    WHERE [Query-SalesDetailsSubform].[SalesOrder] =
[Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
'              "    AND left([Query-SalesDetailsSubform].[Code],3) =
""CDV"") "
'   End If


'_____________________________________________TimeFrame
Main__________________________________________________

' Each Date Filter field has on on-change event to clear out other
choices, so only one of the following options is possible

If Nz(Me.[Filter-WeekEnding]) > 0 Then

ElseIf Nz(Me.[Filter-DateSingleDay]) > 0 Then
WHEREDateFilterString = "#" & Me.[Filter-DateSingleDay] & "#"
WHERECompopr = " = "

Select Case [OptionGroup-DateCriteria]
Case 1
DateCriteria = "  AND [DateOfSale]" & WHERECompopr &
WHEREDateFilterString
Case 2
DateCriteria = "  AND [RequestedInstallDate]" & WHERECompopr &
WHEREDateFilterString
Case 3
DateCriteria = "  AND exists " & vbCrLf & _
"    (SELECT [Recent-SalesScheduling].ID" & vbCrLf & _
"    FROM [Recent-SalesScheduling] " & vbCrLf & _
"    WHERE [Recent-SalesScheduling].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Recent-SalesScheduling].[ScheduledDate] " &
WHERECompopr & WHEREDateFilterString & ")"
Case 4
DateCriteria = "  AND exists " & vbCrLf & _
"    (SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
"    FROM [Record-Orders-Install] " & vbCrLf & _
"    WHERE [Record-Orders-Install].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Record-Orders-Install].[JobStatusDate] " &
WHERECompopr & WHEREDateFilterString & ")" & _
vbCrLf & "   AND exists " & vbCrLf & _
"    (SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
"    FROM [Record-Orders-Install] " & vbCrLf & _
"    WHERE [Record-Orders-Install].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Record-Orders-Install].[JobStatus] = 5 )" ' The
number 5 here refers to Status "Complete"

End Select
WHEREStringAdditions = WHEREStringAdditions & DateCriteria

ElseIf Nz(Me.[Filter-DateFrom]) > 0 Or Nz(Me.[Filter-DateTo]) > 0 Then

'DateOfSale From    *Use the format string to add the # delimiters and get
the right international format.
If Nz(Me.[Filter-DateFrom]) > 0 Then
WHEREDateFilterString = "#" & Me.[Filter-DateFrom] & "#"
WHERECompopr = " >= "

Select Case [OptionGroup-DateCriteria]
Case 1
DateCriteria = "  AND [DateOfSale]" & WHERECompopr &
WHEREDateFilterString
Case 2
DateCriteria = "  AND [RequestedInstallDate]" & WHERECompopr &
WHEREDateFilterString
Case 3
DateCriteria = "  AND exists " & vbCrLf & _
"    (SELECT [Recent-SalesScheduling].ID" & vbCrLf & _
"    FROM [Recent-SalesScheduling] " & vbCrLf & _
"    WHERE [Recent-SalesScheduling].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Recent-SalesScheduling].[ScheduledDate] " &
WHERECompopr & WHEREDateFilterString & ")"
'    Case 4  *** Option CDV ScheduleDate Removed ****
'     DateCriteria = "  AND exists " & vbCrLf & _
'                  "    (SELECT [Query-CDVdetails].ID" & vbCrLf & _
'                  "    FROM [Query-CDVdetails] " & vbCrLf & _
'                  "    WHERE [Query-CDVdetails].[AssociatedCDV_SalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
'                  "    AND [Query-CDVdetails].[CDV_ScheduledDate] " &
WHERECompopr & WHEREDateFilterString & ")"
Case 4
DateCriteria = "  AND exists " & vbCrLf & _
"    (SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
"    FROM [Record-Orders-Install] " & vbCrLf & _
"    WHERE [Record-Orders-Install].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Record-Orders-Install].[JobStatusDate] " &
WHERECompopr & WHEREDateFilterString & ")" & _
vbCrLf & "   AND exists " & vbCrLf & _
"    (SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
"    FROM [Record-Orders-Install] " & vbCrLf & _
"    WHERE [Record-Orders-Install].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Record-Orders-Install].[JobStatus] = 5 )" ' The
number 5 here refers to Status "Complete"

End Select
WHEREStringAdditions = WHEREStringAdditions & DateCriteria
End If 'Filter Date From


'DateOfSale To    *Use the format string to add the # delimiters and get
the right international format.
If Nz(Me.[Filter-DateTo]) > 0 Then
WHEREDateFilterString = "#" & Me.[Filter-DateTo] & "#"
WHERECompopr = " <= "

Select Case [OptionGroup-DateCriteria]
Case 1
DateCriteria = "  AND [DateOfSale]" & WHERECompopr &
WHEREDateFilterString
Case 2
DateCriteria = "  AND [RequestedInstallDate]" & WHERECompopr &
WHEREDateFilterString
Case 3
DateCriteria = "  AND exists " & vbCrLf & _
"    (SELECT [Recent-SalesScheduling].ID" & vbCrLf & _
"    FROM [Recent-SalesScheduling] " & vbCrLf & _
"    WHERE [Recent-SalesScheduling].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Recent-SalesScheduling].[ScheduledDate] " &
WHERECompopr & WHEREDateFilterString & ")"
'  Case 4 *** Option CDV ScheduleDate Removed ****
'   DateCriteria = "  AND exists " & vbCrLf & _
'                "    (SELECT [Query-CDVdetails].ID" & vbCrLf & _
'                "    FROM [Query-CDVdetails] " & vbCrLf & _
'                "    WHERE [Query-CDVdetails].[AssociatedCDV_SalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
'                "    AND [Query-CDVdetails].[CDV_ScheduledDate] " &
WHERECompopr & WHEREDateFilterString & ")"
Case 4
DateCriteria = "  AND exists " & vbCrLf & _
"    (SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
"    FROM [Record-Orders-Install] " & vbCrLf & _
"    WHERE [Record-Orders-Install].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Record-Orders-Install].[JobStatusDate] " &
WHERECompopr & WHEREDateFilterString & ")" & _
vbCrLf & "   AND exists " & vbCrLf & _
"    (SELECT [Record-Orders-Install].[InstallOrderID]" &
vbCrLf & _
"    FROM [Record-Orders-Install] " & vbCrLf & _
"    WHERE [Record-Orders-Install].[AssociatedSalesOrder]
= [Record-Orders-Sales].[SalesOrderID] " & vbCrLf & _
"    AND [Record-Orders-Install].[JobStatus] = 5 )" ' The
number 5 here refers to Status "Complete"

End Select
WHEREStringAdditions = WHEREStringAdditions & DateCriteria
End If 'Filter Date To

End If 'TimeFrame Main

'Build WHERE string (the WHERE of the recordsource)
WhereString = WHEREStringBase & WHEREStringAdditions 'Combine base with
additions
lngLen = Len(WHEREStringAdditions)
' - 5
'    If WhereString <= WHEREStringBase Then     'No criteria have been added
to filter string
'        'If strRecordSource = "Query-SalesOrderForm" Then
'            MsgBox "No criteria", vbInformation, "Nothing to do."
'        'End If
'    Else


''Set recordSource
'        strRecordSource = SELECTString & vbCrLf & FROMString & vbCrLf &
WhereString & vbCrLf & ORDERBYString
'        Debug.Print vbCrLf & vbCrLf & vbCrLf & strRecordSource
'        Me.RecordSource = strRecordSource

'Origianlly changed recordsource on filter, but now just apply to
filter
WhereString = Right(WhereString, Len(WhereString) - 5)  'Remove
leading WHERE
'Debug.Print WhereString
Me.Filter = WhereString










'XXXXXXXXX Me.FilterOn = True


Debug.Print WhereString, Me.RecordSource, Me.Filter, Me.FilterOn

MsgBox "As a temporary fix to a filtering problem, you must now click the
'Unfiltered' button at the bottom of the screen"






GoTo ExitPoint:
'End Main Code

'Execute Error Handling
ErrorCode:
If Err.Number > 0 Then 'beforeupdate is preventing save
Call GlobalCode.LogError(Err.Number, Err.Description, Me.Name,
"FilterButton_Click()")
End If
' Conclude Error Handling
ExitPoint:
DoCmd.Hourglass False
'Application.Echo True

FilterIndicator
DoCmd.Hourglass False
End Sub

 

[markmarko]

The subform control that's at issue is called
[Recent-SalesScheduling-Subform] and should not be affected at all by the
code.

I'll also point out that this code used to work.

 

[markmarko]

Also, in the above copy of the code, I had disabled the me.filteron = true ,
the problem occurs when that is not commented out.

 

[Ken Snell \(MVP\)]

OK. I see that you have Debug.Print lines in the code to see the strings
that you've generated. Show us what those string values actually are. I'm
guessing that you have a syntax error in the string.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

Also, what are the values of the LinkChildFields and LinkMasterFields
properties of the subform control?

 

[markmarko]

As an example, if I filter on Account Number , the concatenated sql is this:

1=1
AND ([AccountNumber] Like "*435335*")

The 1=1 part is just to replace what used to be the WHEREstring Base. This
filter has functioned well for months with it, so I don't think its related
to the problem.

Regarding the link fields...
The LinkMasterField = SalesOrderID
The LinkChildField = AssociatedSalesOrder

These properties are those values before and after the filter procedure runs.

 

[markmarko]

Also, 4 other subforms on the main form continue to function, when this one
fails.

 

[markmarko]

Oh my.... Ok, so mystery solved (to a certain extent)

Somehow, the form that is used in the subform control had gained a filter
property of its very own. Apparently, turning the me.filter on for the main
form was also triggering the .filteron for that subform. This is my best
guess.

I've removed the value from the filter property of that subform, and now it
functions fine. How that .filter property came to have a value in it, I have
no idea. I haven't worked on that form in months, and there's no code to set
the property programatically.

Thanks so much for trying to tackle this issue!

 

[Ken Snell \(MVP\)]

Thanks for posting back with your information; what you found is new to me,
so I appreciate learning something new.