[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel - Dynamic drop down from Access database

Posted on 2011-04-29
6
Medium Priority
?
403 Views
Last Modified: 2012-05-11
Hello experts,

Just started working with excel and need help in populating a few drop down.  I have an excel file with a spec sheet for T-Shirt.  I would like to create drop down list for each selection from an access database.  Base on the selection for that table, I would like for it to limit(dynamic) items for the next selection and so on.  Pretty much just a filtering down of selections and give me the final product.

I need help with bounding each column in the database to a cell in excel as a dropdown.  How to filter it so that it shortens the list down as selection is happening.  Any examples would do.  I provided an example excel file layout and access database.  This is only a small portion of a huge project, and if I can learn how to accomplish this part, that would help tremendously. TSHIRT-SPEC.xls TShirt.mdb
0
Comment
Question by:holemania
  • 3
  • 2
6 Comments
 
LVL 9

Assisted Solution

by:hitsdoshi1
hitsdoshi1 earned 400 total points
ID: 35492439
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35495735
Ok.  I leveraged some techniques from a recent question I answered here: http:/Q_26964006.html, leveraging techniques in a past article I wrote, here:  http:/A_5062.html.

I'm not an access expert, but I created a data connection to your sample MDB and then put a list to it in the Product Connections Tab, with a criteria range on top all set for advanced query.  To the right of that is the result of queries to populate the DV lists, which have dynamic ranges linked to them for Sleeve, Size, Color, etc...

If you like this approach, I can help with the rest, or you can take it from here.  Didn't want to go too far as your Access skills from Excel may outweigh what I've done.

This demonstrates how to use dynamic drop down lists with a data validation list base, with cascading (that's why I'm doing the advanced filter query) and I've put all the code modules in the sample spreadsheet attached.

Give it a shot and let me know if you need further assistance, would like to pursue this route, or that I'm way off base :)


I can provide more in the way of explaining how it works, but getting this out quick for you to test.

Cheers,

Dave
TSHIRT-SPEC-r1.xlsm
0
 

Author Comment

by:holemania
ID: 35505556
Thanks for the information and help.  I will take a look and update.

dlmille,

I tried the excel file you sent, but for some reason it's not working.  I noticed that you also create a link from excel to the access database.  Then pull the data to an excel spreadsheet.  Is it possible to connect directly to the database and then bind the list to the drop down?  

I'm afraid the excel file will get huge if I start dumping data to one of the worksheet to work with.  I can do this easily in windows vb.net form and bind data to a drop down/combo box.  Just not sure how this is done via excel.  

If an example can be provided where there's a direct connect to the access database, bind to a drop down or cell that is treated like a drop down, then filter base on selection.


Both examples provided, I can see how it is done via having the data in a worksheet and treat it as a drop down.  I'll play around and see what I can come up with.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 42

Expert Comment

by:dlmille
ID: 35506197
Ok - will take a look at direct connection - should not be a problem...

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 1600 total points
ID: 35507360
Ok - this was not too difficult (had to re-school up on connecting to Access as its been a while).

The attached works as originally designed, except there's a link to the database and only results associated with populating the range for the DV list is brought in (ultimately, could be a hidden sheet).  You could as well pull directly into the combobox, but going down this path at least first allows us to keep the queries relatively simple and use the generic utility capability from   http:/A_5062.html.

Let me know what you think...  If you can take it from here - great.  If you'd like further assistance (and like this approach) - that's great, too!

Enjoy!

Dave



TSHIRT-SPEC-r5.xlsm
0
 

Author Comment

by:holemania
ID: 35507659
Thank you.  I think i can work off what you have provided.  Thanks again.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question