Change subform on control click

[MeSteve]

Is there a way to change whcih subform is displayed by clicking on a control
button. I want to switch from open records to closed records without closing
the main form.

 

[Jeanette Cunningham]

Steve,
this sounds like filtering the form.

Me.Filter = "[ClosedRecords]" = True
Me.FilterOn = True

Where ClosedRecords is the name of the Yes/No field that records which
records are closed.

When you want to show Open records
Me.Filter = "[ClosedRecords]" = False
Me.FilterOn = True

It would be easy to use an option group for this.
Assuming an option group called optFilter

Private Sub optFilter_AfterUpdate()
Dim strFilter as String

Select case Me.optFilter
Case 1
strFilter = "[ClosedRecords]" = True

Case 2
strFilter = "[ClosedRecords]" = False

Case 3
End Select

If Len(strFilter) >0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
'show all records
Me.FilterOn = False
End If
End Sub

Jeanette Cunningham

 

[MeSteve]

So, you're saying use a filter on the entire recordset instead of having 2
queries, 1 open, one closed? I would code each of those into a click action
for a button? Currently I have 2 forms and 2 queries, but this seems like a
much cleaner way to go about it.

Steve,
this sounds like filtering the form.

Me.Filter = "[ClosedRecords]" = True
Me.FilterOn = True

Where ClosedRecords is the name of the Yes/No field that records which
records are closed.

When you want to show Open records
Me.Filter = "[ClosedRecords]" = False
Me.FilterOn = True

It would be easy to use an option group for this.
Assuming an option group called optFilter

Private Sub optFilter_AfterUpdate()
Dim strFilter as String

Select case Me.optFilter
Case 1
strFilter = "[ClosedRecords]" = True

Case 2
strFilter = "[ClosedRecords]" = False

Case 3
End Select

If Len(strFilter) >0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
'show all records
Me.FilterOn = False
End If
End Sub

Jeanette Cunningham

Is there a way to change whcih subform is displayed by clicking on a
control
button. I want to switch from open records to closed records without
closing
the main form.

 

[Jeanette Cunningham]

Yes if both are for the same recordset, the only difference is that one
field has the value open or closed - if that field can also be null, you
could tweak the code a bit.

Jeanette Cunningham

So, you're saying use a filter on the entire recordset instead of having 2
queries, 1 open, one closed? I would code each of those into a click
action
for a button? Currently I have 2 forms and 2 queries, but this seems like
a
much cleaner way to go about it.

Steve,
this sounds like filtering the form.

Me.Filter = "[ClosedRecords]" = True
Me.FilterOn = True

Where ClosedRecords is the name of the Yes/No field that records which
records are closed.

When you want to show Open records
Me.Filter = "[ClosedRecords]" = False
Me.FilterOn = True

It would be easy to use an option group for this.
Assuming an option group called optFilter

Private Sub optFilter_AfterUpdate()
Dim strFilter as String

Select case Me.optFilter
Case 1
strFilter = "[ClosedRecords]" = True

Case 2
strFilter = "[ClosedRecords]" = False

Case 3
End Select

If Len(strFilter) >0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
'show all records
Me.FilterOn = False
End If
End Sub

Jeanette Cunningham

Is there a way to change whcih subform is displayed by clicking on a
control
button. I want to switch from open records to closed records without
closing
the main form.

 

[MeSteve]

Working great, thanks. But it opened a new can of worms. How can I change
the values listed in a list box based on the results of that filter?

Yes if both are for the same recordset, the only difference is that one
field has the value open or closed - if that field can also be null, you
could tweak the code a bit.

Jeanette Cunningham

So, you're saying use a filter on the entire recordset instead of having 2
queries, 1 open, one closed? I would code each of those into a click
action
for a button? Currently I have 2 forms and 2 queries, but this seems like
a
much cleaner way to go about it.

Steve,
this sounds like filtering the form.

Me.Filter = "[ClosedRecords]" = True
Me.FilterOn = True

Where ClosedRecords is the name of the Yes/No field that records which
records are closed.

When you want to show Open records
Me.Filter = "[ClosedRecords]" = False
Me.FilterOn = True

It would be easy to use an option group for this.
Assuming an option group called optFilter

Private Sub optFilter_AfterUpdate()
Dim strFilter as String

Select case Me.optFilter
Case 1
strFilter = "[ClosedRecords]" = True

Case 2
strFilter = "[ClosedRecords]" = False

Case 3
End Select

If Len(strFilter) >0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
'show all records
Me.FilterOn = False
End If
End Sub

Jeanette Cunningham

Is there a way to change whcih subform is displayed by clicking on a
control
button. I want to switch from open records to closed records without
closing
the main form.

 

[Jeanette Cunningham]

If the listbox is on the subform.
You could create 2 saved queries to use as the rowsource for the listbox.
One query shows a list for closed records the other shows a list for open
records.
I will call them qryClosed and qryOpen
In the code below I have added 2 extra lines marked with *

Private Sub optFilter_AfterUpdate()
Dim strFilter as String

Select case Me.optFilter
Case 1
strFilter = "[ClosedRecords]" = True
*Me.SubformControlName.Form.lstboxControlName.Rowsource = "qryClosed"

Case 2
strFilter = "[ClosedRecords]" = False
*Me.SubformControlName.Form.lstboxControlName.Rowsource = "qryOpen"

Case 3
End Select

If Len(strFilter) >0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
'show all records
Me.FilterOn = False
End If
End Sub

Jeanette Cunningham

Working great, thanks. But it opened a new can of worms. How can I
change
the values listed in a list box based on the results of that filter?

Yes if both are for the same recordset, the only difference is that one
field has the value open or closed - if that field can also be null, you
could tweak the code a bit.

Jeanette Cunningham

So, you're saying use a filter on the entire recordset instead of
having 2
queries, 1 open, one closed? I would code each of those into a click
action
for a button? Currently I have 2 forms and 2 queries, but this seems
like
a
much cleaner way to go about it.


:

Steve,
this sounds like filtering the form.

Me.Filter = "[ClosedRecords]" = True
Me.FilterOn = True

Where ClosedRecords is the name of the Yes/No field that records which
records are closed.

When you want to show Open records
Me.Filter = "[ClosedRecords]" = False
Me.FilterOn = True

It would be easy to use an option group for this.
Assuming an option group called optFilter

Private Sub optFilter_AfterUpdate()
Dim strFilter as String

Select case Me.optFilter
Case 1
strFilter = "[ClosedRecords]" = True

Case 2
strFilter = "[ClosedRecords]" = False

Case 3
End Select

If Len(strFilter) >0 Then
Me.Filter = strFilter
Me.FilterOn = True
Else
'show all records
Me.FilterOn = False
End If
End Sub

Jeanette Cunningham

Is there a way to change whcih subform is displayed by clicking on a
control
button. I want to switch from open records to closed records
without
closing
the main form.