UtterAccess Discussion Forums > Main Form Filter Button Based on Subform Criteria?

Full Version: Main Form Filter Button Based on Subform Criteria?

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

misscrf

Sep 14 2006, 12:40 PM

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 form's records that have a sub record in the activities table, hence activities sub form which = activity5 or =activity6.

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!

fredrisg

Sep 14 2006, 03:30 PM

You could use a button to change the record source of the form based on the criteria you've suggested.

For me, I use a frame with Option Buttons to give users such options as changing the criteria for the records.

Steve

misscrf

Sep 14 2006, 03:35 PM

I am trying a toggle button but having an issue...

CODE

Private Sub tglVerifiedFilter_Click()
On Error GoTo Err_tglVerifiedFilter_Click

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

Exit_tglVerifiedFilter_Click:
     Exit 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 the button is depressed, but shouldn't be until the first time someone clicks it.

Any thoughts?

Thanks,

fredrisg

Sep 14 2006, 06:26 PM

I use a frame/tgl button combination myself having a default toggle but selected and two additional tog buttons for sorting.

Here's the code:

CODE

    Select Case fraProcess

          Case 1

            Forms!frmPurchase!subformfrmPurchasesubfrmPaidInvoices.Form.RecordSource = "qryfrmPurchasesubfrmPaidInvoices"

          Case 2

            Forms!frmPurchase!subformfrmPurchasesubfrmPaidInvoices.Form.RecordSource = "qryfrmPurchasesubfrmPaidInvoices_SortDate"

          Case 3    '8/22/05

            Forms!frmPurchase!subformfrmPurchasesubfrmPaidInvoices.Form.RecordSource = "qryfrmPurchasesubfrmPaidInvoices_SortCut"

          Case Else

            Dim lstrMessage As String
            Dim lintResponse As Integer
            lstrMessage = "Sort unsuccessful - please inform System Administrator if problem persist."
            lintResponse = MsgBox(lstrMessage, vbOKOnly + vbInformation, "Sort Failed")

                Exit Sub

      End Select

Note this name: subformfrmPurchasesubfrmPaidInvoices is the subform object on the form and not the actual source object which is the actual form used as the subform.

As far as the button being 'pushed', check your default value of the frame the tgl button is located. That will 'depresse' the tgl.

I'd recommend you have the 'base case' as the default and allow other cases as well as returning to the base case . . . which is what I've done in this code with case 1.

Steve

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.