Main form button filter - where subform data?

[Guest]

I have a main form backed by a table. It is for (ie) customers. This
main form has 3 subforms on tabs. One of them is for each customer's
activities.

I want to have a button on the main form where a user can click it and
the main form will filter to only the main records that have a sub
record in the activities = activity 5 or =activity 6.

I didn't think it would be that complicated, but I am getting stumped.

I have created apps from the ground up, and never do filters. I guess
it's time!

Any ideas?

Thanks!

 

[chriske911]

(e-mail address removed) has brought this to us :

I have a main form backed by a table. It is for (ie) customers. This
main form has 3 subforms on tabs. One of them is for each customer's
activities.

I want to have a button on the main form where a user can click it and
the main form will filter to only the main records that have a sub
record in the activities = activity 5 or =activity 6.

I didn't think it would be that complicated, but I am getting stumped.

I have created apps from the ground up, and never do filters. I guess
it's time!

Any ideas?

Thanks!

there is a field that links the subforms to the main form
most likely a activity foreign ID, right?
if you filter the records in the main form to hold only records where
activity_foreign_ID = 5 or where activity_foreign_ID = 6 then the
subform will filter out as well
it is that simple

use a look up combo box for one activity, or write a custom event to
have multiple activities in the filter

grtz

 

[Guest]

No, maybe I didnt explain well. They do link by activity foreign id,
which is the activity table pk. The activity id I am talking about is
the activity type id, which is a lookup to at table of activity types.

I have come up with some code, but getting some bugginess to it...

-----------------------

Private Sub tglVerifiedFilter_AfterUpdate()
On Error GoTo Err_tglVerifiedFilter_AfterUpdate

'Check user choice
If Me.tglVerifiedFilter.Value = -1 Then 'apply filter
Forms![frmMemberEntry]![subfrmActivitiesEntry].DataSource =
"qryCustomerActivityFiltered"
Me.tglVerifiedFilter.Caption = "Now Viewing Only Verified
Members"
Else
Forms![frmMemberEntry]![subfrmActivitiesEntry].DataSource =
"qryCustomerActivityShowAll"
Me.tglVerifiedFilter.Caption = "Now Viewing All Members"
End If

Exit_tglVerifiedFilter_AfterUpdate:
Exit Sub

Err_tglVerifiedFilter_AfterUpdate:
MsgBox Err.Description
Resume Exit_tglVerifiedFilter_AfterUpdate

End Sub

-------------------------------------------

Two things:

1 - when I click it, I get an error message that says 'object doesn't
support this property or method'

2- when I open the form initially, the button says nothing. I need the
form to always know what setting the toggle is set to hence its needed
result. (default should be unclicked.)

Any thoughts?

 

[chriske911]

(e-mail address removed) explained :

No, maybe I didnt explain well. They do link by activity foreign id,
which is the activity table pk. The activity id I am talking about is
the activity type id, which is a lookup to at table of activity types.

I have come up with some code, but getting some bugginess to it...

Private Sub tglVerifiedFilter_AfterUpdate()
On Error GoTo Err_tglVerifiedFilter_AfterUpdate

'Check user choice
If Me.tglVerifiedFilter.Value = -1 Then 'apply filter
Forms![frmMemberEntry]![subfrmActivitiesEntry].DataSource =
"qryCustomerActivityFiltered"
Me.tglVerifiedFilter.Caption = "Now Viewing Only Verified
Members"
Else
Forms![frmMemberEntry]![subfrmActivitiesEntry].DataSource =
"qryCustomerActivityShowAll"
Me.tglVerifiedFilter.Caption = "Now Viewing All Members"
End If

Exit_tglVerifiedFilter_AfterUpdate:
Exit Sub

Err_tglVerifiedFilter_AfterUpdate:
MsgBox Err.Description
Resume Exit_tglVerifiedFilter_AfterUpdate

End Sub

Two things:

1 - when I click it, I get an error message that says 'object doesn't
support this property or method'

2- when I open the form initially, the button says nothing. I need the
form to always know what setting the toggle is set to hence its needed
result. (default should be unclicked.)

Any thoughts?

so you want to filter records that are shown on the subform?
then put a combo box to filter out those records on the subform
you will have to point to the fields in the subform by using the full
reference, being something like:
mainform.childformcontrolname.form.filter= "[activity_type_ID] = "
& somecontrol.value
mainform.childformcontrolname.form.filteron = true

it is actually no harder than applying a filter on the mainform but a
lot more typing is involved ;-)

grtz

 

[Guest]

Thanks all. i actually got this working with a toggle button which put
a query recordsource onto the subform, which contained an In Select
statement.

I do have one more issue with this. As users toggle on and off, I want
them to see how many records there are.

It would be soemthing like this:

(a calculated control on a form)

= DCount(*,"tblMembers") -
DCount([ID],"tblMembers","[NotMember]=True")& " Total Records"