Filtering within Switchboard

[Cummings]

I knoiw you gguys don't particularly like the Switchboard, and after beating
my head against the wall for the past few days, I can't say that I blame you.

But, I'm stuck with it for the forseeable future, so here it goes . . . .

We run yearly events with the saem information collected on different
technologies. When I got the database last week, I was told to get it ready
for 2008 abd that "all had to do was copy the 2007 structure." Needless
to say, that wasn't quite right.

So, I've added a "Year" column to the main table. Now, what I'm looking to
do is to be able to display Forms with only the appropriate Year's
information.

The Properties for the command button currently displays (for "Event-On
Click") =HandleButtonClick(1). I would like to be able to do this in-line,
if possible. Alternatively, I could amend "HandleButtonClick(1)". I saw it
once, but haven't been able to find it since.

Sorry for the long-winded explanation, just trying to give enough info to
let you know what's happening.

Thanks

 

[Pat Hartman]

You would not modify the code behind the switchboard (which I happen to
like) to do this. You would modify the queries that the forms/reports are
based on to include selection criteria for the year - I hope you didn't
actually name the field Year since Year() is the name of a function and you
should avoid at all costs using function or property names as names for user
defined objects.

You could add a control to the switchboard form and in the switchboard's
load event, set the value to the current year:

Me.txtYear = Year(Date())

This will allow your users to override the value should they want to look at
data for a different year.

Change all your queries to include selection criteria that points to the
year field on the switchboard form:

Select ...
From ...
Where TranDate = Forms!Switchboard!txtYear

 

[Cummings]

Pat --

Thanks for being so quick, but I hate to admit that you're speakign a
foreign language -- EXCEL I understand. Access not so much.

Anyway, the Switchboard "Open(s) the Form in Edit Mode" (using the
Switchboard Manager). I'm not sure what to do with the "Me.txtYear =
Year(Date())" from below. And, no, I named the field "Spiral" vice "Year",
with an alpha identifier. Just thought it would be easier to say "Year" in
the initial request.

So, I opened form in Design View. I see the FILTER field and think I'm home
free. Except that I can't get it to filter.

Probably doesn't help that I'm using an ACCESS 97 book working on an ACCESS
2000 database in ACCESS 2003. . . .

I like your solution better, as I have a boat load of forms and entries to
modify, but I'm not sure how to modify the Switchboard.

Thanks

JPC


--
JP Cummings

You would not modify the code behind the switchboard (which I happen to
like) to do this. You would modify the queries that the forms/reports are
based on to include selection criteria for the year - I hope you didn't
actually name the field Year since Year() is the name of a function and you
should avoid at all costs using function or property names as names for user
defined objects.

You could add a control to the switchboard form and in the switchboard's
load event, set the value to the current year:

Me.txtYear = Year(Date())

This will allow your users to override the value should they want to look at
data for a different year.

Change all your queries to include selection criteria that points to the
year field on the switchboard form:

Select ...
From ...
Where TranDate = Forms!Switchboard!txtYear

I knoiw you gguys don't particularly like the Switchboard, and after
beating
my head against the wall for the past few days, I can't say that I blame
you.

But, I'm stuck with it for the forseeable future, so here it goes . . . .

We run yearly events with the saem information collected on different
technologies. When I got the database last week, I was told to get it
ready
for 2008 abd that "all had to do was copy the 2007 structure."
Needless
to say, that wasn't quite right.

So, I've added a "Year" column to the main table. Now, what I'm looking
to
do is to be able to display Forms with only the appropriate Year's
information.

The Properties for the command button currently displays (for "Event-On
Click") =HandleButtonClick(1). I would like to be able to do this
in-line,
if possible. Alternatively, I could amend "HandleButtonClick(1)". I saw
it
once, but haven't been able to find it since.

Sorry for the long-winded explanation, just trying to give enough info to
let you know what's happening.

Thanks

 

[Pat Hartman]

Open the Switchboard form in design view.
Somewhere on the left side in the blank area, add a new text box.
Change the Name of the textbox (Other tab) to txtSpiral.
Click on the square at the upper left corner of the form to bring up the
form properties.
On the event tab, select the builder button (three dots - ...) at the far
right edge of the data entry box for the Load event.
Depending on how the database properties are set, you will either open a
code window directly for the Load event or a dialog asking what you want to
do. Choose code.
Enter the single line of code below into the spot where the cursor is
placed:

Where TranDate = Forms!Switchboard!txtSpiral

That gives you a single place where the user can select the year he wants to
work with. He only needs to do it once as long as the switchboard form
remains open throughout the session.

The queries that need to be filtered by year would refer to the txtSpiral
field on the switchboard form to supply the criteria value.

Pat --

Thanks for being so quick, but I hate to admit that you're speakign a
foreign language -- EXCEL I understand. Access not so much.

Anyway, the Switchboard "Open(s) the Form in Edit Mode" (using the
Switchboard Manager). I'm not sure what to do with the "Me.txtYear =
Year(Date())" from below. And, no, I named the field "Spiral" vice
"Year",
with an alpha identifier. Just thought it would be easier to say "Year"
in
the initial request.

So, I opened form in Design View. I see the FILTER field and think I'm
home
free. Except that I can't get it to filter.

Probably doesn't help that I'm using an ACCESS 97 book working on an
ACCESS
2000 database in ACCESS 2003. . . .

I like your solution better, as I have a boat load of forms and entries to
modify, but I'm not sure how to modify the Switchboard.

Thanks

JPC


--
JP Cummings

You would not modify the code behind the switchboard (which I happen to
like) to do this. You would modify the queries that the forms/reports
are
based on to include selection criteria for the year - I hope you didn't
actually name the field Year since Year() is the name of a function and
you
should avoid at all costs using function or property names as names for
user
defined objects.

You could add a control to the switchboard form and in the switchboard's
load event, set the value to the current year:

Me.txtYear = Year(Date())

This will allow your users to override the value should they want to look
at
data for a different year.

Change all your queries to include selection criteria that points to the
year field on the switchboard form:

Select ...
From ...
Where TranDate = Forms!Switchboard!txtYear

I knoiw you gguys don't particularly like the Switchboard, and after
beating
my head against the wall for the past few days, I can't say that I
blame
you.

But, I'm stuck with it for the forseeable future, so here it goes . . .
.

We run yearly events with the saem information collected on different
technologies. When I got the database last week, I was told to get it
ready
for 2008 abd that "all had to do was copy the 2007 structure."
Needless
to say, that wasn't quite right.

So, I've added a "Year" column to the main table. Now, what I'm
looking
to
do is to be able to display Forms with only the appropriate Year's
information.

The Properties for the command button currently displays (for "Event-On
Click") =HandleButtonClick(1). I would like to be able to do this
in-line,
if possible. Alternatively, I could amend "HandleButtonClick(1)". I
saw
it
once, but haven't been able to find it since.

Sorry for the long-winded explanation, just trying to give enough info
to
let you know what's happening.

Thanks

 

[Cummings]

Pat --

Thanks for your assistance, but I keep getting "Sub or Function Undefined",
with "!txtSpiral" highlighted when I try to open the modified switchboard.

Jim
--
JP Cummings

Open the Switchboard form in design view.
Somewhere on the left side in the blank area, add a new text box.
Change the Name of the textbox (Other tab) to txtSpiral.
Click on the square at the upper left corner of the form to bring up the
form properties.
On the event tab, select the builder button (three dots - ...) at the far
right edge of the data entry box for the Load event.
Depending on how the database properties are set, you will either open a
code window directly for the Load event or a dialog asking what you want to
do. Choose code.
Enter the single line of code below into the spot where the cursor is
placed:

Where TranDate = Forms!Switchboard!txtSpiral

That gives you a single place where the user can select the year he wants to
work with. He only needs to do it once as long as the switchboard form
remains open throughout the session.

The queries that need to be filtered by year would refer to the txtSpiral
field on the switchboard form to supply the criteria value.

Pat --

Thanks for being so quick, but I hate to admit that you're speakign a
foreign language -- EXCEL I understand. Access not so much.

Anyway, the Switchboard "Open(s) the Form in Edit Mode" (using the
Switchboard Manager). I'm not sure what to do with the "Me.txtYear =
Year(Date())" from below. And, no, I named the field "Spiral" vice
"Year",
with an alpha identifier. Just thought it would be easier to say "Year"
in
the initial request.

So, I opened form in Design View. I see the FILTER field and think I'm
home
free. Except that I can't get it to filter.

Probably doesn't help that I'm using an ACCESS 97 book working on an
ACCESS
2000 database in ACCESS 2003. . . .

I like your solution better, as I have a boat load of forms and entries to
modify, but I'm not sure how to modify the Switchboard.

Thanks

JPC


--
JP Cummings

You would not modify the code behind the switchboard (which I happen to
like) to do this. You would modify the queries that the forms/reports
are
based on to include selection criteria for the year - I hope you didn't
actually name the field Year since Year() is the name of a function and
you
should avoid at all costs using function or property names as names for
user
defined objects.

You could add a control to the switchboard form and in the switchboard's
load event, set the value to the current year:

Me.txtYear = Year(Date())

This will allow your users to override the value should they want to look
at
data for a different year.

Change all your queries to include selection criteria that points to the
year field on the switchboard form:

Select ...
From ...
Where TranDate = Forms!Switchboard!txtYear


I knoiw you gguys don't particularly like the Switchboard, and after
beating
my head against the wall for the past few days, I can't say that I
blame
you.

But, I'm stuck with it for the forseeable future, so here it goes . . .
.

We run yearly events with the saem information collected on different
technologies. When I got the database last week, I was told to get it
ready
for 2008 abd that "all had to do was copy the 2007 structure."
Needless
to say, that wasn't quite right.

So, I've added a "Year" column to the main table. Now, what I'm
looking
to
do is to be able to display Forms with only the appropriate Year's
information.

The Properties for the command button currently displays (for "Event-On
Click") =HandleButtonClick(1). I would like to be able to do this
in-line,
if possible. Alternatively, I could amend "HandleButtonClick(1)". I
saw
it
once, but haven't been able to find it since.

Sorry for the long-winded explanation, just trying to give enough info
to
let you know what's happening.

Thanks

 

[Pat Hartman]

Sorry - Where TranDate = Forms!Switchboard!txtSpiral
should be
Where Spiral = Forms!Switchboard!txtSpiral
and I got interrupted when I was posting and lost my train of thought. No
code goes in the switchboard form. Just add the field and forget the rest
of the directions. The criteria, needs to go into each of your queries that
you want to filter by the date.

Pat --

Thanks for your assistance, but I keep getting "Sub or Function
Undefined",
with "!txtSpiral" highlighted when I try to open the modified
switchboard.

Jim
--
JP Cummings

Open the Switchboard form in design view.
Somewhere on the left side in the blank area, add a new text box.
Change the Name of the textbox (Other tab) to txtSpiral.
Click on the square at the upper left corner of the form to bring up the
form properties.
On the event tab, select the builder button (three dots - ...) at the far
right edge of the data entry box for the Load event.
Depending on how the database properties are set, you will either open a
code window directly for the Load event or a dialog asking what you want
to
do. Choose code.
Enter the single line of code below into the spot where the cursor is
placed:

Where TranDate = Forms!Switchboard!txtSpiral

That gives you a single place where the user can select the year he wants
to
work with. He only needs to do it once as long as the switchboard form
remains open throughout the session.

The queries that need to be filtered by year would refer to the txtSpiral
field on the switchboard form to supply the criteria value.

Pat --

Thanks for being so quick, but I hate to admit that you're speakign a
foreign language -- EXCEL I understand. Access not so much.

Anyway, the Switchboard "Open(s) the Form in Edit Mode" (using the
Switchboard Manager). I'm not sure what to do with the "Me.txtYear =
Year(Date())" from below. And, no, I named the field "Spiral" vice
"Year",
with an alpha identifier. Just thought it would be easier to say
"Year"
in
the initial request.

So, I opened form in Design View. I see the FILTER field and think I'm
home
free. Except that I can't get it to filter.

Probably doesn't help that I'm using an ACCESS 97 book working on an
ACCESS
2000 database in ACCESS 2003. . . .

I like your solution better, as I have a boat load of forms and entries
to
modify, but I'm not sure how to modify the Switchboard.

Thanks

JPC


--
JP Cummings




:

You would not modify the code behind the switchboard (which I happen
to
like) to do this. You would modify the queries that the forms/reports
are
based on to include selection criteria for the year - I hope you
didn't
actually name the field Year since Year() is the name of a function
and
you
should avoid at all costs using function or property names as names
for
user
defined objects.

You could add a control to the switchboard form and in the
switchboard's
load event, set the value to the current year:

Me.txtYear = Year(Date())

This will allow your users to override the value should they want to
look
at
data for a different year.

Change all your queries to include selection criteria that points to
the
year field on the switchboard form:

Select ...
From ...
Where TranDate = Forms!Switchboard!txtYear


I knoiw you gguys don't particularly like the Switchboard, and after
beating
my head against the wall for the past few days, I can't say that I
blame
you.

But, I'm stuck with it for the forseeable future, so here it goes .
. .
.

We run yearly events with the saem information collected on
different
technologies. When I got the database last week, I was told to get
it
ready
for 2008 abd that "all had to do was copy the 2007 structure."
Needless
to say, that wasn't quite right.

So, I've added a "Year" column to the main table. Now, what I'm
looking
to
do is to be able to display Forms with only the appropriate Year's
information.

The Properties for the command button currently displays (for
"Event-On
Click") =HandleButtonClick(1). I would like to be able to do this
in-line,
if possible. Alternatively, I could amend "HandleButtonClick(1)".
I
saw
it
once, but haven't been able to find it since.

Sorry for the long-winded explanation, just trying to give enough
info
to
let you know what's happening.

Thanks