New - Member need help with Combo box and subform

[dfkdfk]

Hi all,

I am new to these forums and new to access. I can set up the most basic of things but after that I get confused. I work in a research laboratory am using access 2003 to set up a lab inventory database. I only have 1 table with all of the inventory.

I am looking to set up a few simple forms with queries. One idea I had was to set up a form, put a combo box in that has several categories from one of the fields in my database and for the user to use the combo box to bring up specific types of inventory (ie chemicals, biologicals, consumables). When the user selected what they wanted from the combo box, a sub form would output all of the entries with that category. It seems reasonable enough to me, but is this possible to do with only 1 table?

I've tried messing around with it and I can't get it to work. My knowledge of SQL is very limited and I feel like I'm missing a fundemental here. I tried looking up some tutorials and doing a search on here but I haven't found anything relevant. Any help would be appreciated.

Thanks,

Dan

 

[sbooth]

Your problem is probably because you have a database with one table. It is not normalized. You want to break up your data into related tables. When your table structure is correct, your form and queries will be much easier to setup. If you want attach a copy of your db (remove any sensitive date) and someone will help you with your structure. In the meantime, you may want to read about databse normalization.

 

[dfkdfk]

Sbooth,

First off, thank you for the response.

I actually got my form to work but I am interested to see how I should really set up my table. Plus I want to set up more complex forms and queries so I realize that I need to get my tables looking right. Here is my database... it is a bit of a mess but if someone wants to take a stab at setting up the tables logically with the data I have I would be interested in seeing this.

Thanks again,

Dan

 

[apr pillai]

ComboBox and Sub-Form

Create a Main Form with only a combobox with inventory type codes (Use a Total Query to group and take Unique Inventory type codes to use for the combo box) in the header section of the main form, from your main table.

Design a sub-form based on your table with all the fields (with the type code filed of course). Drag this form into the Detail section of the Main form.

Click on the sub-form. Display the Property Sheet (View --> Properties) and set the Link Master Fields Property to the combobox name and Link Child Fields property to the Inventory Type field name on the sub-form.

Try changing the type codes in the combo box. Its corresponding record with all details should appear in the sub-form.

http://www.msaccesstips.com

 

[sbooth]

Okay, I am attaching the altered database. I have created separate tables for category, general location, specific location, and user. This will allow you to create forms to maintain these tables without having to go into the individual forms or queries and changing them. Also, I have changed Category Query Form and the Add Inventory Item form to use the new tables. Take a look and see if you can understand the benefit of the relationship. Also, look under the tools menu to the relationships window for a visual.

Now, there is still work to be done to fix the database. In the Inventory table there are the following fields: quantity and date received. I would not store either of these in this table. If the idea is to keep track of the inventory on this database you will want to setup tables to store beginning quantities and transactions.

Hopefully this will give you a start.

 

[dfkdfk]

sbooth,

Thanks for taking the time to look at my database... I don't completely understand why you would need separate tables yet, but I know thats just because I'm ignorant. I still see access as something of a glorified spreadsheet. Luckily, I have all summer to learn. I'll try to fix that problem by spending some time with the database as well as looking at some of the sample databases they have on here.

Thanks again for the help.

Dan