Combo box in form to filter subform results.

[StacyinArabia]

Hi guys, I've gotten sick of my 300 Excel spreadsheets and decided to try my hand at creating an inventory management system in Access 2007 for my laboratory to track our rather extensive ordering process. This is my first real effort on Access and its safe to assume my knowledge of SQL/VBA is nil and I'd prefer to find solutions that avoid me having to spend the next 6 months learning it (coz I don't think my boss will be that patient with me).

Ok, now I've set my experience level, here is my issue:

I would like to create a form where I can select a supplier's name from a drop-down box (Combo box) in the main form, which will then result in a subform datasheet being populated with the details of all the items that company can supply.I have an Item Details table with a multivalued lookup column with the Supplier name (linked to a Supplier Details table).

It feels like it shouldn't be very difficult but I've tried everything I can think of and topics on this forum don't seem to match what I'm looking for, or get a little too technical for me.

I would love to get some advice on this as I just can't seem to work it out. I can provide more info about my table if needed but I didn't want to complicate things.

Thanks.
Stacy.

 

[John Big Booty]

First up welcome to the forum.

Coming from an excel background, the first thing you must do is forget everything you have learned from excel regarding the storage and manipulation of data.

A good place to start is by becoming familiar with the concept of data normalisation which is what really makes Access different to Excel. Once you can get your head around that, you will have made a great start down the road of database development.

Good luck and have fun

 

[John Big Booty]

...to answer your question you could use;

Forms!YourFormName!ComboName

as the criteria in a query to select the data relevant to any given supplier.

 

[StacyinArabia]

Thanks John, I've been trying to avoid thinking in Excel ways coz that was definitely causing me some serious confusion when I started out. I've been reading as much as I can but its a bit haphazard as I try and create new tables or queries.

In response to your answer, I'm going to have to ask you for a little bit more context for that code. Like where it would go?

 

[John Big Booty]

OK, I'll assume your combo has two columns the first (hidden) Contains the SupplierID, the second the supplier name.

Now create yourself a query that selects all the suppliers and their related products. Products will be linked to their supplier via the supplier ID. Now in the criteria row under SupllierID put the code mentioned above.

 

[spikepl]

If you prefer solutions then google Access Templates - some are free, some aren't. There is quite a choice of free ones here: http://office.microsoft.com/en-us/templates/CT010152732.aspx Sometimes you'll find something that covers 90% of your needs - then you can modify it step by step, while you already have something working. And note that developing yourself takes time - 90 % of the development will consume 90% of your time, and the remaining 10% another 90 %

 

[StacyinArabia]

I tried all afternoon to get your query suggestion working but the only success I had was to create a text box that I could scroll through to show me the records for each company in a subform, not a combo box where I could choose a company from a list.

I dont know what I am doing wrong but its just not working for me today.

 

[DCrake]

Try this attachment. It's not one that I did it's jut a simpl one I found lurking around on my hard drive.