By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,457 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

reference field on subform on tab control

P: n/a
How can I filter records of a subform that is part of a tab control?
The filter works fine on the form when it is not part of the tab using
this in the macro condition:
[Forms]![sfmEmploymentVerification]![OpenedDateFilter]="Today"

I have a main form with a tab control which has three tabs and on each
tab there is a subform.

Main Form: frmEmploymentVerification
Tab Control Name: TabEmployment
Sub Form: sfmEmploymentVerification6M
Field on Sub Form: OpenedDateFilter

My filter is a combo box that lists "This Week", "Last Month" etc to
show the appropriate records. This combo box is currently located on
the subform itself and is using a macro to apply the filter. This is
what I have in the condition field:

[Forms]![frmEmployment]![subMySubform].[Form]![OpenedDateFilter]="This
Week"

I get an error saying: The action or method is invalid because the
formor report isn't bound to a table or query.

My subform is bound to a query so I'm not sure where to go from here.
Should the combo box be part of the tab page instead of the subform?

Any help to get me going in the right direction would be great.
Thanks

Aug 18 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
the first thing to do is forget the tab control - it has nothing to do with
the syntax of the subform reference.
The filter works fine on the form when it is not part of the tab
i suspect that you opened the subform by itself, from the database window -
and assumed that it didn't work when you opened the main form because of the
tab control, but that assumption is not correct.
This is what I have in the condition field:

[Forms]![frmEmployment]![subMySubform].[Form]![OpenedDateFilter]="This
Week"

okay, the only matching name in that expression is OpenedDateFilter - the
form and subform names do not match the names that you posted for those
objects.
Main Form: frmEmploymentVerification
Tab Control Name: TabEmployment
Sub Form: sfmEmploymentVerification6M
Field on Sub Form: OpenedDateFilter
assuming that those are the "real" and correct names of your objects, try
the following conditional expression, as

[Forms]![frmEmploymentVerification]![sfmEmploymentVerification6M].[Form]![Op
enedDateFilter]="This
Week"

hth
"mchlle" <mc****@austin.rr.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
How can I filter records of a subform that is part of a tab control?
The filter works fine on the form when it is not part of the tab using
this in the macro condition:
[Forms]![sfmEmploymentVerification]![OpenedDateFilter]="Today"

I have a main form with a tab control which has three tabs and on each
tab there is a subform.

Main Form: frmEmploymentVerification
Tab Control Name: TabEmployment
Sub Form: sfmEmploymentVerification6M
Field on Sub Form: OpenedDateFilter

My filter is a combo box that lists "This Week", "Last Month" etc to
show the appropriate records. This combo box is currently located on
the subform itself and is using a macro to apply the filter. This is
what I have in the condition field:

[Forms]![frmEmployment]![subMySubform].[Form]![OpenedDateFilter]="This
Week"

I get an error saying: The action or method is invalid because the
formor report isn't bound to a table or query.

My subform is bound to a query so I'm not sure where to go from here.
Should the combo box be part of the tab page instead of the subform?

Any help to get me going in the right direction would be great.
Thanks

Aug 19 '06 #2

P: n/a
You're right I tested my filter out on the subform by itself from the
database window but I did also run it from the main form which includes
the tab control.
i suspect that you opened the subform by itself, from the database window -
and assumed that it didn't work when you opened the main form because of the
tab control, but that assumption is not correct.
Sorry about this condition field I was testing it on another form and
copied and pasted the wrong one.
This is what I have in the condition field:

[Forms]![frmEmployment]![subMySubform].[Form]![OpenedDateFilter]="This
Week"
This is the "real" and correct names of my objects.
Main Form: frmEmploymentVerification
Tab Control Name: TabEmployment
Sub Form: sfmEmploymentVerification6M
Field on Sub Form: OpenedDateFilter
>>[Forms]![frmEmploymentVerification]![sfmEmploymentVerification6M].[Form]!
[OpenedDateFilter]="This Week"
This is what I had originally but copied and pasted yours just in case
I missed something and I got the same error:

The action or method is invalid because the form or report isn't bound
to a table or query.
With an action failed window following.

My main form or tab control are not bound to anything. Do they need to
be?

Aug 19 '06 #3

P: n/a
comments inline.

"mchlle" <mc****@austin.rr.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
This is the "real" and correct names of my objects.
Main Form: frmEmploymentVerification
Tab Control Name: TabEmployment
Sub Form: sfmEmploymentVerification6M
Field on Sub Form: OpenedDateFilter

>>[Forms]![frmEmploymentVerification]![sfmEmploymentVerification6M].[Form]!
>[OpenedDateFilter]="This Week"

This is what I had originally but copied and pasted yours just in case
I missed something and I got the same error:

The action or method is invalid because the form or report isn't bound
to a table or query.
With an action failed window following.

My main form or tab control are not bound to anything. Do they need to
be?
forget the tab control entirely. no, the main form doesn't need to be bound
to a recordset when you're filtering a bound subform. actually, i think i
had a stupid attack in my last post. since the macro is running from within
the subform, and the control OpenedDateFilter is also in the subform, you
shouldn't have to use the full reference at all. try

[OpenedDateFilter] = "Today"

hth
Aug 20 '06 #4

P: n/a
I get the same error.

The action or method is invalid because the form or report isn't bound
to a table or query.

With an action failed window:

Macro Name:
Mcr_DateFilter.OpenedDateFilter

Condition:
True: [OpenedDateFilter]="This Week"

Action Name:
ApplyFilter

Arguments:
(Year([6MonthCheck])=Year(Date()) And
DatePart("ww",[6MonthCheck],0)=DatePart("ww",Date(),0))

Aug 21 '06 #5

P: n/a
hmm, well, i'm stumped. if you want me to take a look at your db (if it's
Access97 or newer) and see if i can fix the problem, do the following: copy
the db and remove any proprietary data *from the copy* (if that means
deleting all records from some tables, put in a few dummy records so there's
something for the filter to work on). compact the copy, and zip if possible.
email it to me, referring to the newsgroups in the subject line, and
referring to this thread in the message text, so i can find it to post back
to. to get my email address, go to
http://home.att.net/~california.db/tips.html#aTip11 and follow the
instructions in the Example, also changing the number 2 to a number 1.

hth
"mchlle" <mc****@austin.rr.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
I get the same error.

The action or method is invalid because the form or report isn't bound
to a table or query.

With an action failed window:

Macro Name:
Mcr_DateFilter.OpenedDateFilter

Condition:
True: [OpenedDateFilter]="This Week"

Action Name:
ApplyFilter

Arguments:
(Year([6MonthCheck])=Year(Date()) And
DatePart("ww",[6MonthCheck],0)=DatePart("ww",Date(),0))

Aug 21 '06 #6

P: n/a
okay, i've looked at your database. the ApplyFilter action would not run in
the macro, as you said; it also would not run when i converted the macro to
VBA. beats me why - i've never used the ApplyFilter action before. since i
tend to have problems with macros in subforms anyway (that's probably me,
rather than Access, at fault), i didn't spend much time beating my head
against the wall - instead, i just set the subform's Filter property in VBA
with the following procedure, as

Private Sub OpenedDateFilter_AfterUpdate()

Select Case Me!OpenedDateFilter
Case "Today"
Me.Filter = "[6MonthCheck]=Date()"
Case "This Week"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And DatePart(""ww"",[6MonthCheck],0)=" _
& "DatePart(""ww"",Date(),0))"
Case "Last Week"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And DatePart(""ww"",[6MonthCheck],0)=" _
& "DatePart(""ww"",Date(),0)-1)"
Case "This Month"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And Month([6MonthCheck])=Month(Date()))"
Case "Last Month"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And Month([6MonthCheck])=(Month(Date())-1))"
Case Else
Me.FilterOn = False
Exit Sub
End Select

Me.FilterOn = True

End Sub

note that i changed your expressions used in the "Today" and "Last Week"
filters, to make them a bit simpler. i also added the following code in the
subform (just in case the subform saves the current Filter property setting
on Close) to make sure you see all records when the subform loads, as

Private Sub Form_Load()

Me.FilterOn = False

End Sub

i didn't do an exhaustive review of the database structure, Michelle, since
you only asked for help with this specific issue. but i do strongly
recommend that you study the principles of relational design - if not for
restructuring this database, then definitely before building another. for
more information, see http://home.att.net/~california.db/tips.html#aTip1.

hth
"tina" <no****@address.comwrote in message
news:Ud*********************@bgtnsc04-news.ops.worldnet.att.net...
hmm, well, i'm stumped. if you want me to take a look at your db (if it's
Access97 or newer) and see if i can fix the problem, do the following:
copy
the db and remove any proprietary data *from the copy* (if that means
deleting all records from some tables, put in a few dummy records so
there's
something for the filter to work on). compact the copy, and zip if
possible.
email it to me, referring to the newsgroups in the subject line, and
referring to this thread in the message text, so i can find it to post
back
to. to get my email address, go to
http://home.att.net/~california.db/tips.html#aTip11 and follow the
instructions in the Example, also changing the number 2 to a number 1.

hth
"mchlle" <mc****@austin.rr.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
I get the same error.

The action or method is invalid because the form or report isn't bound
to a table or query.

With an action failed window:

Macro Name:
Mcr_DateFilter.OpenedDateFilter

Condition:
True: [OpenedDateFilter]="This Week"

Action Name:
ApplyFilter

Arguments:
(Year([6MonthCheck])=Year(Date()) And
DatePart("ww",[6MonthCheck],0)=DatePart("ww",Date(),0))


Aug 22 '06 #7

P: n/a
okay, i just can't refrain from making one additional suggestion: open your
database and, from the database window's menu bar, click Tools | Options |
General tab and remove the checkmark from the Name AutoCorrect option. then
Compact/Repair the database. that should be the *first* thing you do each
time you create a new database file in A2000 or newer. for more information,
see http://home.att.net/~california.db/tips.html#aTip3.

hth
"tina" <no****@address.comwrote in message
news:Ud*********************@bgtnsc04-news.ops.worldnet.att.net...
hmm, well, i'm stumped. if you want me to take a look at your db (if it's
Access97 or newer) and see if i can fix the problem, do the following:
copy
the db and remove any proprietary data *from the copy* (if that means
deleting all records from some tables, put in a few dummy records so
there's
something for the filter to work on). compact the copy, and zip if
possible.
email it to me, referring to the newsgroups in the subject line, and
referring to this thread in the message text, so i can find it to post
back
to. to get my email address, go to
http://home.att.net/~california.db/tips.html#aTip11 and follow the
instructions in the Example, also changing the number 2 to a number 1.

hth
"mchlle" <mc****@austin.rr.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...
I get the same error.

The action or method is invalid because the form or report isn't bound
to a table or query.

With an action failed window:

Macro Name:
Mcr_DateFilter.OpenedDateFilter

Condition:
True: [OpenedDateFilter]="This Week"

Action Name:
ApplyFilter

Arguments:
(Year([6MonthCheck])=Year(Date()) And
DatePart("ww",[6MonthCheck],0)=DatePart("ww",Date(),0))


Aug 22 '06 #8

P: n/a
Thanks for taking a look at it and all the feedback. I'm new to this
type of stuff so I'll definitely take look at some of the resources you
provided.

I copied the code above to the combo box's After Update and get an
ambiguous name error so I'm probably putting this in the wrong place.
Can you email the working db or let me know what I did wrong?

Any other suggestions are welcome to make this more efficient.
Thanks again.

Aug 22 '06 #9

P: n/a

Sorry, I figured it out. I myself had converted the macro to VBA a few
days ago trying to figure it out. So I removed it and now it's working.
Thanks again for all your help.

Aug 22 '06 #10

P: n/a
you're welcome :)
"mchlle" <mc****@austin.rr.comwrote in message
news:11*********************@m79g2000cwm.googlegro ups.com...
>
Sorry, I figured it out. I myself had converted the macro to VBA a few
days ago trying to figure it out. So I removed it and now it's working.
Thanks again for all your help.

Aug 23 '06 #11

P: n/a
One last thing:
I want to add another option in the drop down box "Last Three Months"

Which will include the last three months starting with this one. So it
would show me August, July and June.

I adjusted the code but it gives me everything greater than May all the
way up to December of 2006

Case "Last 3 Months"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And Month([6MonthCheck])>=(Month(Date())-3))"

Aug 23 '06 #12

P: n/a
try

Case "Last 3 Months"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And Month([6MonthCheck]) " _
& "Between (Month(Date()))) And " _
& "(Month(Date())-2))"

hth
"mchlle" <mc****@austin.rr.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...
One last thing:
I want to add another option in the drop down box "Last Three Months"

Which will include the last three months starting with this one. So it
would show me August, July and June.

I adjusted the code but it gives me everything greater than May all the
way up to December of 2006

Case "Last 3 Months"
Me.Filter = "(Year([6MonthCheck])=Year(Date()) " _
& "And Month([6MonthCheck])>=(Month(Date())-3))"

Aug 24 '06 #13

P: n/a

Thanks, that worked perfectly.

Aug 24 '06 #14

P: n/a
you're welcome again :)
"mchlle" <mc****@austin.rr.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
>
Thanks, that worked perfectly.

Aug 25 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.