Creating a dropdown filter on a report

[nwest]

Hello,

I need some help with this seemingly simple DB. I'd like to use this as a very basic log for customers and activities.

I will import customers from another table, so it will be filled with relevant data (address, acount manager etc.). It will be also possible to amend everything via the "Customer" form if needed.

It works mostly how I wanted, except:

1) When I'm logging an event such as a call via the "interactions" form, I'd like the account manager to be pulled from the "Customers" table (i.e. pre-filled).

2) I created a simple "Interactions" report, which lists all the events logged. And this report works fine, but I wanted to create 2 filters on top, so that it can be filtered by Account Manager and/or Company Name if necessary (in other words, no filter would be applied unless selected).

I tinketered with various VBA codes I found online/youtube but couldn't manage it work. I would appreciate it if you can help with this please.

Many thanks..

 

[The_Doc_Man]

In general, putting an active filter on a form works well, but on a report, not so well. If your report launches to the point that you could see your filter controls, it has already started building report sections. Any filters would arrive late to the party, so to speak.

If you are seriously talking about filtering a report, I would advise having a FORM with the filter options and then have the form launch the report.

As to pulling data from the Customer table, the question is, where is that customer determined? IF it is from a single-select combo box or list box, you can have multiple columns returned from the combo's underlying table and can then refer to the combobox.Coumn() property to get both the customer number and the account manager.

ComboBox.Column property (Access)

Office VBA reference topic

learn.microsoft.com

 

[Pat Hartman]

1. If when you create the customer, you choose an account manager for the customer, that's all you need to do. When you create the query for the report's RecordSource, select both the customer table and and the account manager table and draw the appropriate join line. Access will always do this for you if you have already created a relationship between the two tables which you should already have done. If you haven't, ask us how if you need help. Then when to finish the query, just pick the columns you need from either table and they will be available on the report.
2. I agree with Doc's suggestion regarding filtering the report.

 

[tvanstiphout]

Reports are very limited in what you can do interactively. The Northwind 2 Dev Edition has an example of a clickable listbox, but otherwise I agree with the prevailing sentiment of doing the filtering in a form before launching the report.

 

[Edgar_]

Create a form
Add a combobox to that form
Add a subform control to that form and place your report there
Option 1: Have the recordsource of the report read the dropdown content
Option 2: Have the dropdown write the recordsource
Option 3: Add a Filter criteria to the subform and then user FilterOn

You have your options.

 

[nwest]

Thanks for the comments - I've tried the form route with no luck, this is the video that I tried to follow but it got a bit too complicated for me.

I've not explored the recommendation that @Edgar_ has made, I will explore that. If it is not too much to ask, if someone can help with an example (e.g. a form that has a filter for the "account manager") I can figure out of to do a "customer" filter.

Thanks in advance!

 

[Edgar_]

Check the attached file. I don't know your database structure, so I assumed there was a table with roles. That table feeds the combo box. The report's recordsource has a foreign key to those roles, so the filter simply filters by that Id.

 

[Edgar_]

Here's a version with master/child links instead

 

[Gasman]

Thanks for the comments - I've tried the form route with no luck, this is the video that I tried to follow but it got a bit too complicated for me.

I've not explored the recommendation that @Edgar_ has made, I will explore that. If it is not too much to ask, if someone can help with an example (e.g. a form that has a filter for the "account manager") I can figure out of to do a "customer" filter.

Thanks in advance!

It would just be a different field to compare?

If strSearch = "Account manager" then 
    strWhere = "AccountManager = '" & Me.txtSearch & "'"    
Else
    strWhere = "Customer = '" & Me.txtSearch & "'"

If you have more choices, use a Select Case.

 

[Pat Hartman]

this is the video that I tried to follow but it got a bit too complicated for me.

It is confusing to me and I know how this works. Not a good video. Too monotone. Too many filler ums. Too much not relevant information. Five minutes would be better for this topic.