Call Tab Subform action

R

Robert_L_Ross

This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs total,
0-3).

User wants me to automatically apply a filter in one of the tabs instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button (0, -1
represent And/Or query) then applies form formatting and then the filter.

I have the action of 'OnChange' for the tab control to detect when tab 3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the OnClick of
the ApplyFilterToggle button on the subform AND having the exact same code on
the 'OnChange' action of the tab control, how can I make the 'OnChange'
action of the tab control run the 'OnClick' function of the tab's subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties Display_Contacts
subform
Button on the Subform who's action I want to run: FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria string.
Dim AndOr As String 'Set And/Or search string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
strWhere = "" 'Clear criteria string.


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" & AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a trailng " AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) & Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Else 'String has data, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] = True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] = True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own filter
debug line as the criteiria and just apply filter (didn't work...asked me for
the value of each field)...tried calling the OnClick action and that fails
too (can't find the action). I think it's the syntax when calling the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
 
R

Robert_L_Ross

I found a way around it - by setting the subform filter up (setting
checkboxes and the and/or toggle) then focusing on the ApplyContactsFilter
button then using sendkeys to send a space bar hit...but there's got to be a
better way around this.

Robert_L_Ross said:
This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs total,
0-3).

User wants me to automatically apply a filter in one of the tabs instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button (0, -1
represent And/Or query) then applies form formatting and then the filter.

I have the action of 'OnChange' for the tab control to detect when tab 3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the OnClick of
the ApplyFilterToggle button on the subform AND having the exact same code on
the 'OnChange' action of the tab control, how can I make the 'OnChange'
action of the tab control run the 'OnClick' function of the tab's subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties Display_Contacts
subform
Button on the Subform who's action I want to run: FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria string.
Dim AndOr As String 'Set And/Or search string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
strWhere = "" 'Clear criteria string.


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" & AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a trailng " AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) & Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Else 'String has data, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] = True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] = True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own filter
debug line as the criteiria and just apply filter (didn't work...asked me for
the value of each field)...tried calling the OnClick action and that fails
too (can't find the action). I think it's the syntax when calling the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
 
S

Scott Whetsell, A.S. - WVSP

If the code in your OnClick event works the way you want it then in your
OnChange event place the line

Call [YourControlName]_OnClick

When your OnChange event triggers that line will trigger the OnClick code as
well

Robert_L_Ross said:
This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs total,
0-3).

User wants me to automatically apply a filter in one of the tabs instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button (0, -1
represent And/Or query) then applies form formatting and then the filter.

I have the action of 'OnChange' for the tab control to detect when tab 3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the OnClick of
the ApplyFilterToggle button on the subform AND having the exact same code on
the 'OnChange' action of the tab control, how can I make the 'OnChange'
action of the tab control run the 'OnClick' function of the tab's subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties Display_Contacts
subform
Button on the Subform who's action I want to run: FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria string.
Dim AndOr As String 'Set And/Or search string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
strWhere = "" 'Clear criteria string.


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" & AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a trailng " AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) & Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Else 'String has data, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] = True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] = True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own filter
debug line as the criteiria and just apply filter (didn't work...asked me for
the value of each field)...tried calling the OnClick action and that fails
too (can't find the action). I think it's the syntax when calling the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
 
R

Robert_L_Ross

That doesn't work. Tried Call FilterContactsToggle_Click and Call
FilterContactsToggle_OnClick, both produce "Compile Error: Sub or Function
not defined"

The sub is public as shown in the first post.

Any other ideas?

Scott Whetsell said:
If the code in your OnClick event works the way you want it then in your
OnChange event place the line

Call [YourControlName]_OnClick

When your OnChange event triggers that line will trigger the OnClick code as
well

Robert_L_Ross said:
This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs total,
0-3).

User wants me to automatically apply a filter in one of the tabs instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button (0, -1
represent And/Or query) then applies form formatting and then the filter.

I have the action of 'OnChange' for the tab control to detect when tab 3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the OnClick of
the ApplyFilterToggle button on the subform AND having the exact same code on
the 'OnChange' action of the tab control, how can I make the 'OnChange'
action of the tab control run the 'OnClick' function of the tab's subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties Display_Contacts
subform
Button on the Subform who's action I want to run: FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria string.
Dim AndOr As String 'Set And/Or search string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
strWhere = "" 'Clear criteria string.


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" & AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a trailng " AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) & Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Else 'String has data, so remove the " AND " at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] = True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] = True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own filter
debug line as the criteiria and just apply filter (didn't work...asked me for
the value of each field)...tried calling the OnClick action and that fails
too (can't find the action). I think it's the syntax when calling the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
Click to expand...
 
S

Scott Whetsell, A.S. - WVSP

Try the same line without the Call command

FilterContactsToggle_Click

Let me know if it works.


Robert_L_Ross said:
That doesn't work. Tried Call FilterContactsToggle_Click and Call
FilterContactsToggle_OnClick, both produce "Compile Error: Sub or Function
not defined"

The sub is public as shown in the first post.

Any other ideas?

Scott Whetsell said:
If the code in your OnClick event works the way you want it then in your
OnChange event place the line

Call [YourControlName]_OnClick

When your OnChange event triggers that line will trigger the OnClick code
as
well

Robert_L_Ross said:
This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs
total,
0-3).

User wants me to automatically apply a filter in one of the tabs
instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button
(0, -1
represent And/Or query) then applies form formatting and then the
filter.

I have the action of 'OnChange' for the tab control to detect when tab
3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the
OnClick of
the ApplyFilterToggle button on the subform AND having the exact same
code on
the 'OnChange' action of the tab control, how can I make the 'OnChange'
action of the tab control run the 'OnClick' function of the tab's
subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties Display_Contacts
subform
Button on the Subform who's action I want to run: FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the
Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria string.
Dim AndOr As String 'Set And/Or search
string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected
for
dates in a JET query string.
strWhere = "" 'Clear criteria string.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" & AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a trailng "
AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) & Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All
button
Else 'String has data, so remove the " AND "
at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] =
True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] =
True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own
filter
debug line as the criteiria and just apply filter (didn't work...asked
me for
the value of each field)...tried calling the OnClick action and that
fails
too (can't find the action). I think it's the syntax when calling the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
Click to expand...
Click to expand...
 
R

Robert_L_Ross

Scott,

No, I receive a Sub or Function Not Defined error.

Scott Whetsell said:
Try the same line without the Call command

FilterContactsToggle_Click

Let me know if it works.


Robert_L_Ross said:
That doesn't work. Tried Call FilterContactsToggle_Click and Call
FilterContactsToggle_OnClick, both produce "Compile Error: Sub or Function
not defined"

The sub is public as shown in the first post.

Any other ideas?

Scott Whetsell said:
If the code in your OnClick event works the way you want it then in your
OnChange event place the line

Call [YourControlName]_OnClick

When your OnChange event triggers that line will trigger the OnClick code
as
well

:

This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs
total,
0-3).

User wants me to automatically apply a filter in one of the tabs
instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button
(0, -1
represent And/Or query) then applies form formatting and then the
filter.

I have the action of 'OnChange' for the tab control to detect when tab
3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the
OnClick of
the ApplyFilterToggle button on the subform AND having the exact same
code on
the 'OnChange' action of the tab control, how can I make the 'OnChange'
action of the tab control run the 'OnClick' function of the tab's
subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties Display_Contacts
subform
Button on the Subform who's action I want to run: FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the
Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria string.
Dim AndOr As String 'Set And/Or search
string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected
for
dates in a JET query string.
strWhere = "" 'Clear criteria string.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" & AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a trailng "
AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) & Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All
button
Else 'String has data, so remove the " AND "
at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] =
True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] =
True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own
filter
debug line as the criteiria and just apply filter (didn't work...asked
me for
the value of each field)...tried calling the OnClick action and that
fails
too (can't find the action). I think it's the syntax when calling the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
Click to expand...
Click to expand...
 
S

Scott Whetsell, A.S. - WVSP

Next question. You have the sub as a Public sub instead of a Private sub.
Any particular reason? I understand the tabs, but as long as everything
that you are targeting is on the same form, a private sub should suffice.
I'm having trouble recreating your error on my forms. Your sub should
consist of:

Private Sub Tabs_Click()
FilterContactsToggle_Click
End Sub



Robert_L_Ross said:
Scott,

No, I receive a Sub or Function Not Defined error.

Scott Whetsell said:
Try the same line without the Call command

FilterContactsToggle_Click

Let me know if it works.


Robert_L_Ross said:
That doesn't work. Tried Call FilterContactsToggle_Click and Call
FilterContactsToggle_OnClick, both produce "Compile Error: Sub or
Function
not defined"

The sub is public as shown in the first post.

Any other ideas?

:

If the code in your OnClick event works the way you want it then in
your
OnChange event place the line

Call [YourControlName]_OnClick

When your OnChange event triggers that line will trigger the OnClick
code
as
well

:

This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs
total,
0-3).

User wants me to automatically apply a filter in one of the tabs
instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button
(0, -1
represent And/Or query) then applies form formatting and then the
filter.

I have the action of 'OnChange' for the tab control to detect when
tab
3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the
OnClick of
the ApplyFilterToggle button on the subform AND having the exact
same
code on
the 'OnChange' action of the tab control, how can I make the
'OnChange'
action of the tab control run the 'OnClick' function of the tab's
subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties
Display_Contacts
subform
Button on the Subform who's action I want to run:
FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the
Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria
string.
Dim AndOr As String 'Set And/Or search
string.
Dim lngLen As Long 'Length of the
criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected
for
dates in a JET query string.
strWhere = "" 'Clear criteria
string.


'***********************************************************************
'Look at each search box, and build up the criteria string
from
the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" &
AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a
trailng "
AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) &
Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All
button
Else 'String has data, so remove the "
AND "
at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next
line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All
button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] =
True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] =
True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own
filter
debug line as the criteiria and just apply filter (didn't
work...asked
me for
the value of each field)...tried calling the OnClick action and that
fails
too (can't find the action). I think it's the syntax when calling
the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
Click to expand...
Click to expand...
 
R

Robert_L_Ross

No, I still receive a Sub or Function Not Defined error.

The sub was Public from another solution I was trying to implement. I made
it Public because it was a subform on the tab control. Making it Private
still returns the Sub or Function Not Defined error.

This is my code on the Tabs_Change event:
===start code====
Private Sub Tabs_Change()
If Me.Tabs = 3 Then
If Left(Forms![Lender ID Properties Display].Contacts.Caption, 2) =
"--" Then
Exit Sub
End If
FilterContactsToggle_Click
End If
End Sub

The first If (Me.Tabs=3) is to make sure this only happens when the user
clicks the appropriate tab.

The second If (Left(Forms![Lender ID Properties Display].Contacts.Caption,
2) makes sure it doesn't try to apply a filter when the filter is already on
(if the caption starts with two dashes, it means the filter is already on -
applying it twice messes up the tab caption).



Scott Whetsell said:
Next question. You have the sub as a Public sub instead of a Private sub.
Any particular reason? I understand the tabs, but as long as everything
that you are targeting is on the same form, a private sub should suffice.
I'm having trouble recreating your error on my forms. Your sub should
consist of:

Private Sub Tabs_Click()
FilterContactsToggle_Click
End Sub



Robert_L_Ross said:
Scott,

No, I receive a Sub or Function Not Defined error.

Scott Whetsell said:
Try the same line without the Call command

FilterContactsToggle_Click

Let me know if it works.


That doesn't work. Tried Call FilterContactsToggle_Click and Call
FilterContactsToggle_OnClick, both produce "Compile Error: Sub or
Function
not defined"

The sub is public as shown in the first post.

Any other ideas?

:

If the code in your OnClick event works the way you want it then in
your
OnChange event place the line

Call [YourControlName]_OnClick

When your OnChange event triggers that line will trigger the OnClick
code
as
well

:

This is a weird setup but you'll see why in a second.

I have a main form with a tab set and a subform in each tab (4 tabs
total,
0-3).

User wants me to automatically apply a filter in one of the tabs
instead of
having to load the filter criteria and apply.

My subform in tab 3 checks each of 6 checkboxes and a toggle button
(0, -1
represent And/Or query) then applies form formatting and then the
filter.

I have the action of 'OnChange' for the tab control to detect when
tab
3 has
been selected, and it loads the form criteria (checking the first 5
checkboxes and setting the And/Or toggle to Or) and even selects the
ApplyFilter toggle for the subform.

Now, rather than having to have the subform filter loaded in the
OnClick of
the ApplyFilterToggle button on the subform AND having the exact
same
code on
the 'OnChange' action of the tab control, how can I make the
'OnChange'
action of the tab control run the 'OnClick' function of the tab's
subform?

To clarify, here's the vitals:
Main Form Name: Lender ID Properties Display
Tab Control Name: Tabs
Tab in question: 3 (last of 4 tabs).
Tab Name: Contacts
Subform Name in the Contacts Tab: Lender ID Properties
Display_Contacts
subform
Button on the Subform who's action I want to run:
FilterContactsToggle
Event I want to run: OnClick
Code in the OnClick event of the FilterContactsToggle button on the
Lender
ID Properties Display_Contacts subform:

--start code--
Public Sub FilterContactsToggle_Click()

If Me.FilterContactsToggle = -1 Then

Dim strWhere As String 'The criteria
string.
Dim AndOr As String 'Set And/Or search
string.
Dim lngLen As Long 'Length of the
criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected
for
dates in a JET query string.
strWhere = "" 'Clear criteria
string.


'***********************************************************************
'Look at each search box, and build up the criteria string
from
the
non-blank ones.

'***********************************************************************

If Me.AndOrToggle = -1 Then
AndOr = " Or "
Else
AndOr = " And "
End If

'PORTAL
If Me.PortalFilter = -1 Then
strWhere = strWhere & "([Portal Contact] = True)" &
AndOr
Me.PORTALFilterlLabel.ForeColor = 255
End If

'FDF
If Me.FDFFilter = -1 Then
strWhere = strWhere & "([FDF Contact] = True)" & AndOr
Me.FDFFilterLabel.ForeColor = 255
End If

'SLATE
If Me.SLATEFilter = -1 Then
strWhere = strWhere & "([SLATE Contact] = True)" & AndOr
Me.SLATEFilterLabel.ForeColor = 255
End If

'MANAGEMENT
If Me.MANAGEMENTFilter = -1 Then
strWhere = strWhere & "([Management] = True)" & AndOr
Me.MANAGEMENTFilterLabel.ForeColor = 255
End If

'AGREEMENTS
If Me.AGREEMENTSFilter = -1 Then
strWhere = strWhere & "([Agreements] = True)" & AndOr
Me.AGREEMENTSFilterLabel.ForeColor = 255
End If

'GENERAL
If Me.GENERALFilter = -1 Then
strWhere = strWhere & "([General] = True)" & AndOr
Me.GENERALFilterLabel.ForeColor = 255
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'***********************************************************************
'See if the string has more than 3 or 4 characters (a
trailng "
AND
" or " OR ") to remove.
If AndOr = " And " Then
lngLen = Len(strWhere) - 5
End If

If AndOr = " Or " Then
lngLen = Len(strWhere) - 4
End If

If lngLen <= 0 Then 'String is Empty.
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
MsgBox "No filter criteria provided." & Chr(13) &
Chr(10) &
"Filter is unavailable.", vbExclamation, "No Criteria"
Me.FilterContactsToggle = 0
Me.FilterOn = False
Me.ContactFilterText.ForeColor = -2147483630
'Me.ClearContactTypes.Enabled = True 'Enable Clear All
button
Else 'String has data, so remove the "
AND "
at
the end.
strWhere = Left$(strWhere, lngLen)


'***********************************************************************
'Adjust Form to reflect filter mode.

'***********************************************************************
Me.FilterContactsToggle.ForeColor = 255

'For debugging, remove the leading quote on the next
line.
Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
ElseIf Me.FilterContactsToggle = 0 Then
Me.FilterOn = False
'Me.ClearContactTypes.Enabled = True 'Enable Clear All
button
Me.FilterContactsToggle.ForeColor = -2147483630
Me.PORTALFilterlLabel.ForeColor = -2147483630
Me.FDFFilterLabel.ForeColor = -2147483630
Me.SLATEFilterLabel.ForeColor = -2147483630
Me.MANAGEMENTFilterLabel.ForeColor = -2147483630
Me.AGREEMENTSFilterLabel.ForeColor = -2147483630
Me.GENERALFilterLabel.ForeColor = -2147483630
End If

End Sub
--End Code--

Here's what I'm using on the OnChange event of the Tabs control:
--Start Code--
Private Sub Tabs_Change()

Dim strWhere As String

If Me.Tabs = 3 Then
'Set up the form for filter
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![PortalFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FDFFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![SLATEFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![MANAGEMENTFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AGREEMENTSFilter].Value = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![AndOrToggle].Caption = "Or"
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle] = -1
Forms![Lender ID Properties Display]![Lender ID Properties
Display_Contacts subform].Form![FilterContactsToggle].SetFocus

Call Forms("Forms![Lender ID Properties Display]![Lender ID
Properties Display_Contacts
subform].Form![FilterContactsToggle]").FilterContactsToggle_onclick
' strWhere = "'([Portal Contact] = True) Or ([FDF Contact] =
True) Or
([SLATE Contact] = True) Or ([Management] = True) Or ([Agreements] =
True)'"
' Me.Filter = strWhere
' Me.FilterOn = True
End If

End Sub
--End Code--

As you can see, I tried just using the output of the subform's own
filter
debug line as the criteiria and just apply filter (didn't
work...asked
me for
the value of each field)...tried calling the OnClick action and that
fails
too (can't find the action). I think it's the syntax when calling
the
OnClick that's messing me up.

Any ideas?

Thanks!!

Rob
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search Form with a SubForm 3
Filter a subform 4
Double filter for subform 1
Allen Brownes ECount() 5
Problems with filtering a subform 1
Syntax Error WHY? 16
Error on modified Allenbrowne SearchForm 6
Filtering using two controls 2

Top