VBA Filtering in Access

[Course 4 Escapee]

Using VBA, I'm trying to get Access 2000 to open a table that displays filtered contents based on criteria specified in a form (Year, State, Plan) so that rate factors can be edited.

All I can seem to find is a way to open the unfiltered table. Since there are thousands of records in this table and I only need about 10 for any given input combination, I'd like to just see those 10 records.

Any suggestions?

:wall:

[Cho Da]

Use a query?

[Oblomov retired (student)]

Use a query?
Yup, and use VBA to change the SQL so you show the proper records.

If you need to use a filter (as defined in MS Access) i think you are going to have to create a form and then use VBA to set the filter (don't forget FilterOn) and open the form. It's been a while so I might be wrong :-)

[Course 4 Escapee]

Use a query?

But I want the ability to manually edit the contents of those records in the table. A query won't do that.... right?

[Oblomov retired (student)]

Use a query?

But I want the ability to manually edit the contents of those records in the table. A query won't do that.... right?

Depends on the query. For simple "SELECT FROM X WHERE cond" queries, it will.

[Course 4 Escapee]

Wow. I had no idea query results were still tied to their source table. Thanks for the help.


:notworth:

[Bama Gambler]

Let's assume your form that has the year, state and plan is called Form1. In your query criteria for year put the following: [Forms]![Form1]![Year]. Likewise for state and plan. Now draw a comand button on your form. Select the category Miscellaneous. Then select run query. And so on.

You could also use a sub-form that links on those three fields. Feel free to send me a copy of the access database. It would take 5 seconds to set it up.

[Course 4 Escapee]

I've tried two methods with no success

1. DoCmd.OpenQuery - it requires an already saved query to execute. This prevents me from using the (Year, State, Plan) items from the userform.

2. DoCmd.RunSQL - it apparently requires the output to be written to a table first (it crashes if I leave that bit of code out). That defeats the whole purpose of this exercise.

The only solution I can think of is to have the RunSQL save it to another table and then I have to update the original table with the edited table. I really don't want to add that extra step if I can help it. Any other ideas?

:wall:
:wall:
:wall:
:wall:

[Bama Gambler]

I've tried two methods with no success

1. DoCmd.OpenQuery - it requires an already saved query to execute. This prevents me from using the (Year, State, Plan) items from the userform.
Why? Make an already saved query and in the criteria row of the saved query (in query builder) write [Forms]![Form1]![Year] and so on.

[Bama Gambler]

By the way, I do this all the time. Try downloading some samples from Microsoft. The book Running MS Access 2000 is pretty good.

[Course 4 Escapee]

That works great except for one last step: the table that needs to be queried varies by year. In other words the rates for years 2001 and 2002 are in different tables. Is there a way to build this into the query?

[Oblomov retired (student)]

In other words the rates for years 2001 and 2002 are in different tables. Get a stick and hit the person that created that database. (Potentially, hit yourself on the head)

If even remotely possible, join 'm together first. Thank me in 5 years,

[Oblomov retired (student)]

That works great except for one last step: the table that needs to be queried varies by year. In other words the rates for years 2001 and 2002 are in different tables. Is there a way to build this into the query?
As an alternative solution: use the DoCmd.OpenQuery but dynamically construct the SQL in the Query just before opening it.

[Course 4 Escapee]

In other words the rates for years 2001 and 2002 are in different tables. Get a stick and hit the person that created that database. (Potentially, hit yourself on the head)

If even remotely possible, join 'm together first. Thank me in 5 years,

It's a mammoth database that I inherited. I could join them but I'm sure that would create more problems elsewhere.

[Oblomov retired (student)]

Is it possible to create a union query where all fields are included plus an additional field with the name of the table of origin?I don't think you would be able to edit the results of a UNION, try it with a small test database first to make sure.

[Course 4 Escapee]

I think I'm back to my RunSQL option.

:swear: :swear: :swear:

[snafu]

FTC4L, please provide a few points of additional information.

How are you trying to open the table?

You have different tables for each year and want to open the appropriate table with filtered records based on some selected criteria in a form and a button click. Have I correctly understood the problem?

If you will help me understand the problem I think I can help provide a solution.

[Course 4 Escapee]

The historical rates are archived in tables by their rate year. Occasionally there will need to be a change made to one of the archived rates. So I have created a form which asks for the rate year, state, plan and rate type. The rate year is needed to select the correct archived table and the state, plan and rate type are needed as the filter criteria.

After the user has selected the values for those 4 fields the form then brings up the filtered table. Then the user only sees the records that need editing.

[snafu]

A bit cludgy for my tastes but here is one possible solution

DoCmd.OpenTable "Table1"
DoCmd.ApplyFilter WhereCondition:="Field1 like '[1-9]'"

Please note that when the user closes the table they will be asked if they want to save the changes to the table. Also this will take the focus away from the form.

Like I said, very cludgy but also very simple.

A more elegant solution would be to create a subform that is in datasheet view
The subform is not visible until the criteria are selected and the user clicks the button
Set the recordsource of the subform to the appropriate table through code
Set the filter property of the subform through code
Display the subform
Have a button that then "closes" the subform but really just hide it

[Course 4 Escapee]

That worked!! If you use "DoCmd.SetWarnings False" you can avoid the Save Changes prompt and the focus is back on the form upon closing the table. Thanks for the help guys!


:toast: :party: :tup: :guitarwo:

[IAm@Work.com]

I have never used code in my Access databases. I always use macros instead. I see nothing in what you are describing that could not be done with a simple "OpenQuery" macro and perhaps a condition statement or two.

Two reasons I don't use code - I learned Access back with version 1.0 when macros were the only choice, and I can figure out what to do by simply reading through the choices, no need to learn exotic code.

I have never come up with anything I wanted to do that I could not do with macros.

Additional benefit is that mere mortals can read the macros and know what they are doing (assuming that have the appropriate Access background.)

Do you guys really see any benefit to learning stuff like "DoCmd.OpenQuery" when you could simply select "OpenQuery" from the drop down list in a macro?

[snafu]

Additional benefit is that mere mortals can read the macros and know what they are doing (assuming that have the appropriate Access background.)

And I would put forth that mere mortals can also read the VBA and know what they are doing if you assume the appropriate Access background as well. No more of a background, just a slightly different one.

I have worked extensively in MSAccess (starting about 1-month after version 2.0 came out) and have made use of both macros and code. The VBA code is tremendously more powerful and via the docmd functionality contains everything that the macros do and tons more.

For simpler functionality the macro approach is fine. And if macros works for you then that is fantastic. You won't catch me putting someone down for their choice of tools. Your job, your tools. If you can get the job done then power to you.

But for my job, I find the code generally easier to document and read (that would include following behind someone else's work). I also find the code much more powerful than the macro. If you would like I can point to at least one example posted on this board within the last month of using code to make something tremendously easier than non-code solutions.

For Course-4's problem, he/she had already chosen to go the code route. In doing so, they came across a problem easily solved via macro's. Because the macro language is now a subset of the VBA language that means the problem is also easily solved via code. I give solutions based on the nature of the problem and the questioner's choice of tools. Unless asked, I assume the person has made their tool choice for a reason.

[Course 4 Escapee]

I am working on adding functionality to an existing database that already contains over 100 printed pages of VBA code, so going the Macro route wasn't really going to make it any easier to follow what's going on in this Accessian labyrinth.

However, in smaller-scale databases that I have created on my own, Macros have sufficed.

[Arlie_Proctor]

When you get more than 15-20 pages of VBA code sitting behind an Access database, it's time to tap IT on the shoulder and call for a more robust implementation. Access is a wonderful ad-hoc and prototyping tool, but version control, scalability, and lack of reliable code locking make it unsuitable for large applications.

That said, I'll echo Snafu. Getting the job done is critical and everything else is secondary. Personally, I don't use macros due to their limits, but they work well for some purposes.