Change subform on control click

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

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.
 
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
 
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.


Jeanette Cunningham said:
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 said:
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.
Click to expand...
Click to expand...
 
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

MeSteve said:
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.


Jeanette Cunningham said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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?

Jeanette Cunningham said:
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

MeSteve said:
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.


Jeanette Cunningham said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

MeSteve said:
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?

Jeanette Cunningham said:
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

MeSteve said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top