Copy into an excel sheet from selection in two dropdown boxes

I want to populate an Excel sheet from an Access DB.  

Ideally I will pick a dropdown box with Name1 and a second dropdown box with name2.

From that I want to go to either an access DB or a hidden sheet and copy all the information that matches the Name1 and Name2 fields.  I can use a query, a hidden sheet or whatever is the best way of doing this.
vmccuneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
TransferSpreadsheet is a one line export.  Create a query that references the combos on the form and selects data that matches.  Save the querydef with a meaningful name.  Use that querydef in the TransferSpreadsheet method.

Select ...
From ...
Where SomeName1 = Forms!yourform!cbo1Name and SomeName2 = Forms!yourform!cbo2Name

PS, your request is unclear.  Do you want the query to match BOTH names or EITHER name?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If your data is on a hidden sheet and you want to extract the data based on drop down selections on another sheet, the Advanced Filter seems to be the obvious way to achieve that.

Please upload a sample workbook to help us to provide a solution.
0
vmccuneAuthor Commented:
Pat,

Name1 and Name2 would be entered from a dropdown on the worksheet not on the access side.  You are right though I was not very clear.  Name1 and name2 are separate values found in separate fields.  An example would be ManagerName and then supervisorname.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
If this is an Excel question, why include Access in the topic list?  I will remove it.
0
Rob HensonFinance AnalystCommented:
You can create a link to an Access Database from within Excel using the equivalent to MS Query.

On the Data Tab, select the Button for New Query and choose from Database and then Microsoft Access Database. That will then allow to browse to the Database and I believe it will then show you tables within the database from which you can then pick fields and specify values for those fields.

I can't detail the full process as I don't have a database to link to.

This will create a dynamic link to the Database which can be refreshed as and when required; by adapting it to a VBA routine for refreshing, I believe you will be able to replace the query criteria with cell values, ie select the names and then run the refresh VBA to update the data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
If your data is in Excel, you can also use slicers to  filter your data.

You can store the data on one sheet and then have multiple slicers for different fields on another sheet. Using your example of manager, Supervisor and then presumably Employee, you would have a Slicer for the Manager field, a slicer for the Supervisor field and potentially a slicer for Employee field. When you select an item in the Manager slicer the items in the Supervisor slicer will be reduced to only those Supervisors under that Manager, when you then select a Supervisor the Employee slicer will reduce to only those Employees under that Supervisor giving you a list of Employees to choose from if you want to reduce further.

At this point if you were to look at the data sheet, it will be filtered to show only those employees; copying from this sheet in this state will only copy the visible rows of data so you can then use just that data.

I did something similar last year with a Supplier Database; fields were Category, Service and Supplier. This was used across the Organisation to help users find a specific supplier based on their Category and Service criteria.

Another alternative could be use of a Pivot Table depending on what data you are trying to extract for the criteria combination. Pivot tables are ideal for numerical summation. You could get a summary table showing:

Manager 1
   Supervisor 1
      Employee 1       Salary      Start Date    Holiday Balance
      Employee 2       Salary      Start Date    Holiday Balance
      Employee 3       Salary      Start Date    Holiday Balance
Manager 1
   Supervisor 2
      Employee 4       Salary      Start Date    Holiday Balance
      Employee 5       Salary      Start Date    Holiday Balance
      Employee 6       Salary      Start Date    Holiday Balance

It can be configured so the Manager and Supervisor are shown as filter values so that the table just shows the Employee details.
0
vmccuneAuthor Commented:
Thanks Rob and I will try this tonight.
0
vmccuneAuthor Commented:
Just what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.