Create a "Control" Form to specify filters to display specific forms & reports

[Bellina]

Hi,
I am a newbie using Access 2013.
I have a database with information of our group of companies, list of banks and the list of bank accounts these companies have with the respective banks.

The existing relationship between the main tables currently:
Banks(one)-->(many)Bank Accounts(many)<--(one)Companies

I would like to create a main "Control" Form that will allow me to specify the filters I need, to display my records in my existing forms/reports as such:

1) View and edit a specific Company Details in the Company Details Form

2)Filter Bank Accounts and view by:
- by specified currency
- by specified bank
- by specified company
- by specified account type

Of course I have plenty more filter requirements but I believe if the ones above are solved, I'm good to go with the others.

Thank you very much in advance!

[jimatqsi]

That's a pretty tall order. It's one thing if you approach a new design project with that idea in mind, but to retrofit an existing system is something else. If one guy wrote the system or one design philosophy was consistently followed there may be some patterns to take advantage of. But of course we can't see what your're starting with.

Is this an all-Access app or is the data in linked tables?

You might consider making a parameter table and a matching pop-up form to edit the parameters. Then every form, report, or query will need to either join a query appropriate to the target (company or bank account); or you could write a vba function that gets called from each of the forms/reports/queries and returns a true/false value based on comparing parameters passed. I guess you wouldn't have to have a parameter table, you could just keep the form that maintains parameters open all the time and each form/report/query makes a comparison against the objects on the form rather than in a table. But I think it's cleaner with a parameter table.

Jim

[zmbd]

Agreed with jimatqsi.
Also please provide the answers to J's questions, these will be most helpful - I would also ask, which version of Access are you using and, if you are working with linked tables, what type of database are the linked tables linked to (access, sql, mysql, oracle, etc..)?

You may want to consider more than one form for what you describe.

In the meantime, you may find the following to be of some use, don't let the code and the like in these weigh you down. Read thru them first and see if the descriptions are anything like what you want, from there we can help you weed thru the concepts:

-filtering-

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

[Bellina]

Hi,
I am using Access 2013, and yes, it's all Access based.

Here are the list of Objects that I have:

Tables|{Fields}
Bank Accounts| {ID, Company, Bank Name, Account Number, Account Type, Currency, Account Status, Purpose}
Banks| {ID, Bank Name, Branch Name, Contact Person,Phone, Country}
Companies| {ID, Company Short Name, Company Name, Company Status, Functional Currency, Contact Person, Phone, Country}
Currency List| {Currency Code, Currency Name}

Queries|{Fields}
Active bank accounts| {Company, Bank Name, Account Number, Currency, Account Type, Purpose}
Bank Extended|{Bank Name(combination of Bank Name & Branch Name), Banks.*}
Companies Extended|{Company(combination of Company Short Name & Company Name), Companies.*}
Private companies bank accounts| {Company Status, Company Name,Bank Name, Account Number, Currency, Account Type}

Forms
Bank Account Details
Bank Account List
Bank Accounts Datasheet Subform (included in the Company Details form)
Bank Details
Bank List
Company Details
Company List

Reports - still working on this one, hoping to solve this issue in order to display the required data in the reports.

I like the idea on the parameter table. How do I start on this?

Thanks guys!

[zmbd]

Would you mind adding in the relationships:
For example
Bank Accounts| {ID, Company, Bank Name, Account Number, Account Type, Currency, Account Status, Purpose}
Banks(one)-->(many)Bank Accounts(many)<--(one)Companies

You can simply do this:
Bank Accounts| {Banks(one)-->(many)Bank Accounts(many)<--(one)Companies}

for each of the relationships and I can edit this back into your post.


"parameter table" you may not need this... however, once we understand the table relationships we can go from there.

[jimatqsi]

First, design your parameter table. You'll need a column for each parameter - maybe even two columns if you propose to use a from/to range for some elements. You may also want a column to specify the report or query that is to be filtered by the particular row of parameters.

Then, make a form to edit the parameter values. You might even consider making this form a launcher, basically a menu to run programs based on these parameters. So you add objects to edit each of the parameters, and a button for each report or query you want to run after setting the parameters. You'll of course have to modify the reports and queries to pay attention to the parameter values. One way to do that might be to use DLookup calls in the where strings of each query involved. Another would be to join the parameter table to your queries, but it complicates things a bit.

I've avoided getting too granular with this explanation. We'll depend on you to let us know the level of detail help you need.

Jim

[jimatqsi]

By the way, I don't like so much the parameter table idea for forms. Look at the links zmbd provided regarding form filtering. It's much preferable to give the user control over filters right there in the form they're working on.

Jim

[zmbd]

There are two ways to approach the parameters table:
As J has pointed out, one row, multiple columns
or
three/four columns and multiple rows.

The first is ok for small setups that will not need to be maintained and can be fairly easy to use, the second is easier to maintain but a little harder to use.

So the First Option:

[PK][parameter1][parameter2][prameter3][...limit 255...]
[1]   [first]   [second]     [third]      [...]
 

and the Second:

[pk][report_form_filter][parametername][parametervalue]
[1] [report1]    [pname1]       [pvalue1]
[2] [report1]    [pname2]       [pvalue2]
[3] [report1]    [pname3]       [pvalue3]
[4] [Filter1]    [pname1]       [pvalue1]
[5] [Filter1]    [pname2]       [pvalue2]
[6] [Filter1]    [pname3]       [pvalue3]
[...]

Personally, when using a form to find records, and I want a report based on those records, I pass the form's filters on to the report.

So, say I have a query that pulls all of the table data, formats it and does all of the calculations. I have a form based on this query and a report. So using a combobox as a filter on the form, I can filter out everything except what I'm after... so I can pass this filter value to the report and open it for preview before printing:
DoCmd.OpenReport"rptReport",acViewPreview,,"fieldn ame =" & Me.CBO

A little more information on that:
DoCmd.OpenReport Method - Office 2007

[Bellina]

Hi,

To reply on the relationships. The one I listed down earlier is basically it.

Bank Accounts| {Banks(one)-->(many)Bank Accounts(many)<--(one)Companies}

Currency List|{Companies(many)-->(one)Currency List(one)<--(many)Bank Accounts}
*this is basically to allow users to select from the currency list for the Companies' main currency and the bank accounts currency

I'm reading on the form filtering like you guys have mentioned. Yeah, the parameters table looks a bit tough for a newbie like me.

Thanks!