Creating runtime tables

[Brian Martin]

My database has two tables. One is a library of mechanical parts (Parts) and the other is blank(List). I have a form that displays all the parts from the parts table and the user then clicks on the parts they require and they are inserted into the blank table to create a parts list. The problem is that I want to be able to then save a copy of this table under a different name and then clear the list table ready for the next parts list to be created. I want to save a new table for each parts list that is created and then be able to reference them in a list box to be opened again. Any ideas on how to go about this would be very welcome.

 

[BLeslie88]

Well...

Why do you not creat an index for each list ( 2nd table but at least you only need 2 tables) and keep them all your lists in one table...?

A user / client could reference this list by ID or prompt them for a meaningful description for each index....

The proper way to do this would have the user / client create a list name first. Then on a seperate from add/modify/delete lists. At which time they can select a list, you could list all the current parts on a label or text box... And have them add/remove items from the list at which time you add the index with the item and related data.

This would be my approach. This way one would only have to delete a list name, which would clear the items.

I think this approach is more mannangeable...

Let me know what you think or if you need any sample code...

Regards,

 

[Brian Martin]

That seems quite a good way

Thanks for the reply. That seems like a good way to do it. Another way that I have been trying to do it is to create a button that activates a create table query where the name of the table is a parameter that can be entered by the user in a dialog box and the data from the List table is inserted into it. Any ideas on the correct way to do this. I haven't been able to get this to work yet. I'll have a go at your idea aswell and let you know how I get on.
Thanks for the help
Brian

 

[The_Doc_Man]

Although your multiple-table method would work, it is a bit more complex than you need or want.

I would approach it this way...

Table: Parts
contains whatever your parts table ever contained.

Table: Assembly
contains an assembly number and descriptive information. The assembly number could be autonumbered.

Table: PartsList
contains an assembly number, a part number (and possible a quantity for that part?)

Now your form creates assemblies. When you create a new assembly, your PartsList SUBFORM is linked to the assembly number. Since this is a new assembly, it has no matching parts in the parts list.

When you click on the part from the Parts table, all you need to do is write that part number, the assembly number (which is also on your form), and that optional quantity.

This approach means you only have one table for the partslists that you want to build. It is qualified by an assembly number, so you can still call up any assembly at any time. You still can create new, empty assemblies and populate them. But now you can also write a single report that tells you the contents of every assembly you ever put together. You can also do frequency analysis of "most popular parts" for all assemblies by doing counts for every part number. You can do the backwards list of "what assemblies use this part?" from a single table.

Doing it with multiple tables won't allow you to do that easily. Trust me, this method gives you tons more flexibility and functionality.