Button passesalong filter criteria

[Fred]

I'm experienced at Access but new to programming.

I'm doing something in a way that is cumbersome to the point of being
unworkable. I'm simplifying a bit in order to keep my question short. I
have a table which has action items for people. Three of the fields are used
for selection criteria regarding which records get displayed.

"WhoBy" Contents = John, Mary, Joe etc.
"ActionDate" (data field)
"Priority" Contents = A, B.C,D

I need 15 buttons on a "menu" form, each of which will bring up the same
"view" form, except with different record selection criteria. For example,
the first button brings up all records where "WhoBy" = Mary, "ActionDate"
is older than today, and "Priority" = "A". (And I don't want the people
to have to type in the selection criteria)

So, not knowing a better way, right now those 15 buttons who's "on click"
events open 15 different forms which draw from 15 different queries, and I
have those selection criteria implemented in the 15 queries.

And so, amongst othr problems, when I make a design change on a form, I have
to make it on all 15 forms.

Is there a better way to have the button pass along / implement the filter
criteria for the display of the form?

Again, I'm experienced at Access but new at Access programming, to please
give any programming answers at my dummy level.

Thanks.

Fred

 

[Klatuu]

You do not need all those forms and queries. You need only one. Instead of
text boxes, use combo boxes for the whoby and priority fields. When the user
has selected the filtering values, use the Where argument of the OpenForm
method to filter the form's recordset. Read VBA Help on the Where argument
of the OpenForm method for details on how to use it.

 

[Fred]

Dear Dave,

Thanks for the info.

My main takeaway is that OpenForm with a where argument is a good way to
open forms while filtering. I did a first read on the help on that and will
learn it.

Beyond that I didn't understand what you meant about where I should
substitute combo boxes for text boxes. I have the "menu" form which just
has buttons. And then the main view form which has lots of text boxes. But
by the time I get to that form, I'm done.

 

[Klatuu]

Sorry, Fred. I thought you were using text boxes to enter the data.
Replacing the buttons specific to whoby, etc. with a combo box would do the
same thing.

One concept to keep in mind is that code belongs in code and data belongs in
tables. Hard coding values, while not always avoidable, should be. Even if
you have a short list of status codes, for example, you can never be sure
that list will not change. It is easier to add a row to a table than to
modify your code and redistribute your application.

 

[Fred]

Dear Dave,

Thanks for the post. Your "OpenForm" pointer did a great deal of help both
on the application and also to my code learning process. I only slightly
know what I'm doing but it worked.

Hard coding the buttons comes from the fact that they are used hundreds of
times per day, plus, even now, I don't know how to do what you are
describing. I only know how to use combo boxes for loading fields in tables,
not how to get their selections to affect a filter.

I wish I could find a book to learn Access VBA faster and with less
bothering of you experts. I've asked for a recommendation in this family of
discussion groups before and the only one they recommended was like a 2000
page 10 year old set. The 2000 pages is fine but I gotta think that being
that old would be a problem.

Thanks again.

Fred

 

[Klatuu]

VBA hasn't changed a lot in 10 years. There have been some features and
functions added, however. I don't know which book they are recommending.

As to using a combo box to set a filter, you use the current value of the
combo in the filtering statment. The simplest form of the technique is like
this:

Docmd.OpenForm "SomeForm", , , "[SomeField] = " & Me.MyCombo

SomeField is the name of a field in SomeForm's record source you want to
filter on. It will open the form with only records that have the value in
the combo in SomeField.

Now, it gets a bit different if for some reason the combo's bound column is
not the one you need to use. If it is the second column of the combo's row
source you need to filter on, then it would be:

Docmd.OpenForm "SomeForm", , , "[SomeField] = " & Me.MyCombo.Column(1)

Note the 1 for the second column. Combo and List Box column indexes are
zero based, so the first column is Column(0).

And there is the correct syntax for the data type. The code above assumes
[SomeField] is a numeric field.
For a text field
Docmd.OpenForm "SomeForm", , , "[SomeField] = """ & Me.MyCombo & """"

For a date field
Docmd.OpenForm "SomeForm", , , "[SomeField] = #" & Me.MyCombo & "#"

Now to make use of it using a combo in your case, you would need to create a
table with a record for each entity in the who by list. Then you would use a
query based on that table as the row source of your combo.

I see your posts out here from time to time, so I think you will get there,
it just takes a lot of bruises on the forehead and some dents in the wall.

 

[Fred]

Dear Dave,

Thank you very very much!

FYI the book(s) that several recommended were Access 2002 Developer's
Handbook Set by Ken Getz and others.

So my "10 years" from memory was wrong.....actually 6 years.

Sincerely,

Fred

VBA hasn't changed a lot in 10 years. There have been some features and
functions added, however. I don't know which book they are recommending.

As to using a combo box to set a filter, you use the current value of the
combo in the filtering statment. The simplest form of the technique is like
this:

Docmd.OpenForm "SomeForm", , , "[SomeField] = " & Me.MyCombo

SomeField is the name of a field in SomeForm's record source you want to
filter on. It will open the form with only records that have the value in
the combo in SomeField.

Now, it gets a bit different if for some reason the combo's bound column is
not the one you need to use. If it is the second column of the combo's row
source you need to filter on, then it would be:

Docmd.OpenForm "SomeForm", , , "[SomeField] = " & Me.MyCombo.Column(1)

Note the 1 for the second column. Combo and List Box column indexes are
zero based, so the first column is Column(0).

And there is the correct syntax for the data type. The code above assumes
[SomeField] is a numeric field.
For a text field
Docmd.OpenForm "SomeForm", , , "[SomeField] = """ & Me.MyCombo & """"

For a date field
Docmd.OpenForm "SomeForm", , , "[SomeField] = #" & Me.MyCombo & "#"

Now to make use of it using a combo in your case, you would need to create a
table with a record for each entity in the who by list. Then you would use a
query based on that table as the row source of your combo.

I see your posts out here from time to time, so I think you will get there,
it just takes a lot of bruises on the forehead and some dents in the wall.

--
Dave Hargis, Microsoft Access MVP

Dear Dave,

Thanks for the post. Your "OpenForm" pointer did a great deal of help both
on the application and also to my code learning process. I only slightly
know what I'm doing but it worked.

Hard coding the buttons comes from the fact that they are used hundreds of
times per day, plus, even now, I don't know how to do what you are
describing. I only know how to use combo boxes for loading fields in tables,
not how to get their selections to affect a filter.

I wish I could find a book to learn Access VBA faster and with less
bothering of you experts. I've asked for a recommendation in this family of
discussion groups before and the only one they recommended was like a 2000
page 10 year old set. The 2000 pages is fine but I gotta think that being
that old would be a problem.

Thanks again.

Fred