Main form Option group to filter subform

[Scott Sabo]

Hello-

I have a main form called frmCustomers that has a subform called frmTransactions. On the subform, there are 3 check boxes; 1st contact, 2nd contact, and 3rd contact. These check boxes show how many times we have reached out to the customer and the associated table carries a date to indicate when the contact was made. I did not use a combo box to do this as I wanted to be able to record a date for each contact attempt.

I have an option group on the main form that i would like to use to filter the subform with. The option group has 4 options; No contacts,1 contact, 2 contacts, 3 contacts. I cannot get the option group to correctly filter the subform, below is the code I have put in the AfterUpdate event of the option group:

Private Sub Frame34_AfterUpdate()

Dim strFilter As String

Select Case [Frame34]

Case 1
Forms!frmTransactions.Form.FilterOn = False

Case 2
strFilter = "[Contact 1] = -1"
Forms!frmTransactions.Form.Filter = strFilter
Forms!frmTransactions.Form.FilterOn = True

Case 3
strFilter = "[Contact 2] = -1"
Forms!frmTransactions.Form.Filter = strFilter
Forms!frmTransactions.Form.FilterOn = True

Case 4
strFilter = "[Contact 3] = -1"
Forms!frmTransactions.Form.Filter = strFilter
Forms!frmTransactions.Form.FilterOn = True

End Select

End Sub

Any ideas as to why this is not working? I have looked through a ton of newsgroup posts and am having a hard time understanding how to write this code to control the filtering of the subform. Any help is greatly appreciated.

Thanks

Scott

[Phil]

On 05/09/2013 23:39:24, Scott Sabo wrote:
> Hello-
>
> I have a main form called frmCustomers that has a subform called

> frmTransac tions. On the subform, there are 3 check boxes; 1st contact,
> 2nd contact, a nd 3rd contact. These check boxes show how many times we
> have reached out t o the customer and the associated table carries a date

> to indicate when the contact was made. I did not use a combo box to do

> this as I wanted to be a ble to record a date for each contact attempt.

>
> I have an option group on the main form that i would like to use to filter
> the subform with. The option group has 4 options; No contacts,1 contact, 2
> contacts, 3 contacts. I cannot get the option group to correctly filter
> the subform, below is the code I have put in the AfterUpdate event of the

> opti on group:

>
> Private Sub Frame34_AfterUpdate()
>
> Dim strFilter As String
>
> Select Case [Frame34]
>
> Case 1
> Forms!frmTransactions.Form.FilterOn = False
>
> Case 2
> strFilter = "[Contact 1] = -1"
> Forms!frmTransactions.Form.Filter = strFilter
> Forms!frmTransactions.Form.FilterOn = True
>
> Case 3
> strFilter = "[Contact 2] = -1"
> Forms!frmTransactions.Form.Filter = strFilter
> Forms!frmTransactions.Form.FilterOn = True
>
> Case 4
> strFilter = "[Contact 3] = -1"
> Forms!frmTransactions.Form.Filter = strFilter
> Forms!frmTransactions.Form.FilterOn = True
>
> End Select
>
> End Sub
>
> Any ideas as to why this is not working? I have looked through a ton of

> new sgroup posts and am having a hard time understanding how to write this

> code to control the filtering of the subform. Any help is greatly
> appreciated.
>
> Thanks
>
> Scott
>

I don�t think much of your design. What happens when you decide to contact
your customers 4 times � Redesign? Personally I would use a subform to show
the dates of the contacts, and on the main form enter a number representing
the count of the date. Basically �Show me those customers who have had 2
dates recorded against them)

If you want to do it that way, the option group (depending on setting the
values of the option buttons to 1,2,3,& 4) will return one of those values

So I think your code should be
Private Sub Frame34_AfterUpdate() � The option group

Dim strFilter As String
Select Case [Frame34]
Case 1

CustomerForm!frmTransactions.Form.FilterOn = False

Case 2
strFilter = "[Contact 1] = -1"

CustomerForm!frmTransactions.Form.Filter = strFilter
CustomerForm!frmTransactions.Form.FilterOn = True

Case 3
strFilter = "[Contact 2] = -1"

CustomerForm!frmTransactions.Form.Filter = strFilter
CustomerForm!frmTransactions.Form.FilterOn = True

Case 4
strFilter = "[Contact 3] = -1"

CustomerForm!frmTransactions.Form.Filter = strFilter
CustomerForm!frmTransactions.Form.FilterOn = True
End Select
End Sub

Note the correct way of refering to a subform
Mainform!Subform.form

Phil

[Patrick Finucane]

Are you sure you are correctly stating you are using a form/subform? Your syntax seems incorrect.

Forms!frmCustomer!fmrTransactions.Form.Filter = strFilter
Forms!frmCustomer!fmrTransactions.Form.FilterOn = True
or
Me.fmrTransactions.form.filter = strFilter
Me.fmrTransactions.form.filteron =

[Scott Sabo]

Hello-

I tried Patricks suggestion and changed the code I had originally posted to read as shown below:

Private Sub Frame34_AfterUpdate()

Dim strFilter As String

Select Case [Frame34]

Case 1

Forms!frmCustomers!frmTransactions.Form.FilterOn = False

Case 2
strFilter = "[Contact 1] = -1"

Forms!frmCustomers!frmTransactions.Form.Filter = strFilter
Forms!frmCustomers!frmTransactions.Form.FilterOn = True

Case 3
strFilter = "[Contact 2] = -1"

Forms!frmCustomers!frmTransactions.Form.Filter = strFilter
Forms!frmCustomers!frmTransactions.Form.FilterOn = True

Case 4
strFilter = "[Contact 3] = -1"

Forms!frmCustomers!frmTransactions.Form.Filter = strFilter
Forms!frmCustomers!frmTransactions.Form.FilterOn = True

End Select

End Sub

Now, when I hit the option group buttons representing Case 2, 3, or 4, I get the following error:

"Database cant find the field "frmTransactions" referred to in your expression."
When I debug, it is highlighting the line "Forms!frmCustomers!frmTransactions.Form.Filter = strFilter". It seems that I should be referring to the field in there somewhere, any ideas?

[Ulrich Möller]

Hallo Scott,

there might be something wrong in your reference to the subform. Please
check this for the correct syntax: ACC: How to Refer to a Control on a
Subform or Subreport <http://support.microsoft.com/kb/113352/en-us>

Regards

Ulrich

[Scott Sabo]

I tried Ulrichs response which had the following advice:

To refer to a control on a subform, use the following syntax:

Forms![main form name]![subform control name].Form![control name]

So I cahnged my expression to:

Case 2
strFilter = "[Contact 1] = -1"

Forms![frmCustomers]![Contact 1].Form![Contact 1] = strFilter
Forms![frmCustomers]![Contact 1].Form![Contact 1].FilterOn = True

Now the error message says it cannot find the field Contact 1 referenced in my expression.

I don't understand in the syntax suggested by Ulrich where it first refers to the "subform control name" then later to the "control name". It seems that the latter is supposed to be a control on the main form, not on the subform?

[Ulrich Möller]

Hallo Scott,

there was a litte misunderstanding by my side. Hier is a little Sample,
how you can do that:

Private Sub optGroupCategory_AfterUpdate()

If optGroupCategory.Value = 0 Then
Me!subFormControl.Form.FilterOn = False
Else
Me!subFormControl.Form.FILTER = "[CategoryID] = " &
CStr(optGroupCategory.Value)
Me!subFormControl.Form.FilterOn = True
End If

End Sub

There is an OptionGroup on the main with for example four checkboxes
which i have assigned numbers from 0..3. Then in the AfterUpdate event
you format the filter expression to your needs and assign it to the
filter property of your subform. The subform in this example is
addressed by the name of the subform control (here "subFormControl") and
not by the name of the subform itself!. In the filter expression you
choose normally a valid fieldname of the underlying recordsource.

Regards

Ulrich

[David Hare-Scott]

The subform does not sit on the main form naked, it sits inside a subform
control on the main form. The subform form then has its own controls. It
is even possible to do this at a third level so that the subform has a
subform control containing a form, that would be a subsubform but this is
rarely required. So inspect the main form to find the correct name of the
subform control (not the sub form itself), it probably isn't Contact 1 but
something that was automagically assigned by Access.

Try not to give objects of different types the same name, using standardised
naming conventions will help with this. Also you will save much typing of
brackets and messing about if you don't give objects names with blanks in
them but that is not the source of your problem on this occasion.

David

[Patrick Finucane]

That is why I asked you if frmTransactions was really a subform. A subform is part of the main form and resides within it.

When you write the code like
Me.
you should see frmTransactions as an option in Intellisense.

[Access Developer]

This has continued on, and on, and on...

Why don't you summarize the following: name of the main form, name of the
subform control, name of the control in the form embedded in the subform
control. And, very importantly, where the VBA code is to be executed: (1)
in the module associated with the main form, or (2) in the module associated
with the embedded form in the Subform Control.

Without that information, no one can tell you if you are using the proper
reference.

If you have used the same name for any of these objects, correct that before
proceeding -- sometimes people say "It confuses Access" but Access does what
you tell it to do, and in fact, it is we (developers, users) who confuse
ourselves if we do something that allows us to write ambiguous names.

And, your data is apparently not "normalized" (following relational
guidelines). You need not, and should not, use "Yes / No" values, as would
be shown in check boxes. You indicate you can't use a Combo Box because you
need to also include a date for the Contact. A table containing the customer
id and date for each contact can be used in a Combo Box, and you can
validate that an entry in that table will not exceed your given limit for
number of contacts for customer, and Count in a Query that you use to select
the particular record.

On the other hand, if you are willing to go to some extra work if anything
changes in the future, you can continue with the approach you're using...
once you get the reference correct and working.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Scott Sabo" <scott...@henryschein.com> wrote in message
news:debcd3a8-7ac3-46a2...@googlegroups.com...

[Phil]

Pretty well back to my original reply :)

Phil

[Access Developer]

Yep, data layout redesign would be desirable.

But the o.p. might be able to make his approach work as is (though it could
be more work later). And I still haven't seen the data details needed to
write a correct reference to a Control in the Form embedded in the Subform,
nor where the code is executing.

I did not think I ought to confuse the issue by explaining that there is no
Access object called a "Subform" -- there are Subform Controls into which a
Form (or datasheet, or, in some recent versions, a Report) can be imbedded.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Phil" <ph...@stantonfamily.co.uk> wrote in message
news:l0h8a4$8ls$1...@speranza.aioe.org...