List box with sublist?

[zwheeling]

I am new to access and I am building a database to keep track of parts and assemblies, I have one table that holds all my parts and assemblies and I have a second table that holds a separate list of all parts that belong to each assembly. What I want to do is to be able to show an over all list of the parts and assemblies and when you click on an assembly it drops down an indented list of the parts that make up that assembly. Is a list box the way I want to go for this and if so is there an easy way to do this or is there another way that would be easier? Thanks.

 

[AccessMc]

When you say you're new to access, what experience do you have?

Can you build a query using the wizard, and then add expression criteria using build?
Can you use a sub-form?
Can you build a macro using the Macro builder?

 

[zwheeling]

Yes I can build a query using the wizard, and then add expression criteria. I have read about using sub-forms and macros in access but haven't really put the knowledge to use yet. I only recently started building this database and everyhthing I have learned is off the web or what I have read in the access bible, I am now trying to put together forms, which is where this question popped up.

 

[AccessMc]

Okay, you need to build a form with a sub-form. The form will contain information on each assembly, and the sub-form will contain information on each part.

I'm sure this project is meant to be a learning experience, and I'd hate to appear patronising given my fairly low level of personal experience, so I'll try not to be too detailed.

To make the form and sub-form work, you will need relationships between the Assembly and Parts tables. If you don't know how to do that I'll explain, but if not then make sure you have a working 1 to many relationship (each assembly can have many parts).

You then need a combo box in the form section to select the assembly.

Based on the value in the primary key of the assembly table, you will need to create a query on the parts table. So, the criteria of the field which links assembly and parts is the assembly ID set in the combo box in the form.

Next you will need to build a combo box in the sub-form based on that query - that will only display the parts in the assembly chosen in the first combo box.

To make that update each time you change the assembly combo box you will need to build a requery macro. That should requery the sub-form combo box. Set that macro to the sub-form's OnCurrent event.

Hopefully that'll be enough to help you make your form have dynamic combo boxes, if you need any more help just ask and I'll try to help.

 

[zwheeling]

Thanks for the help, I was able to get the combo boxes working and I can choose the assembly then get the combo box to show the parts, but is there anyway to do it without the combo box. Basically what I want to do is as follows:
Start with my list of parts and assemblies:
Part 1
Part 2
Part 3
Assembly 1
Part 4
Assembly 2
(keeps going 100's of parts and assemblies)

but when you double click an assembly
Part 1
Part 2
Part 3
Assembly 1
1.)Part 1
2.)Part 2
Part 4
Assembly 2

basically so all parts, assemblies, and sub-parts exist on the same list, It seems like it would be easy I just can't seem to figure out how.

 

[zwheeling]

in my previous reply the parts that belong to the assembly should also be indented.