Creating a table based on form criteria

[epicmove]

Hi Guys.
Just a couple of questions that I hope someone can help with.

I have just started building a new IT asset management system. Each asset can be a different equipment type.
In tblAssetMain the equipment type field is a lookup to tblequiptype. The user can then select PC, UPS, Printer etc.
The next field in tblAssetMain is Equipment_Type_Specific. This is a lookup field to tbl_Equip_Type_Specific (the child table of tblEquipType). If the User selects PC in the first combo second combo needs to display the related equip_type_specific details i.e. Tower, Laptop, Desktop.

I know this is case of "cascading combo boxes". However, the examples I have seen seem to be for querying/searching data, not filtering the lookup reference to then enter the selection into a master table.

Secondly,

Each equip_type_specific record i.e. Laptop must have a specification table. This is so that for a laptop the spec table would ask the user for RAM, HDD, PSU,CPU details and the spec table for a Printer would ask RAM, Number of print tray etc.

What I would like is a form with tick boxes for every possible spec field. The user can then click the required boxes i.e. HDD, FDD and enter the table name in a text box. Finally clicking a command button will create a new table with name textbox.value and fields HDD and FDD. It will also need to copy the table name back into the Equip_Type_Specific record.

Any help would be much appreciated.

Thanks
Ben

 

[epicmove]

Not sure I explained it correctly...

In a way to store the data efficiently, I did not want to have one specification table as you would have to create a field for every possible specification detail. For example, the specification record for a printer would have blank spaces for HDD, FDD, CDRW, CPU etc.

So, I need a separate specification table for each Equipment Type Specific. In this case the table for the printer would have fields for RAM, Number of Print Trays. On the entry form, the specification would be in a subform and the control source of the subform would change to the correct specification depending on equipment type.

Rather than create every specification table now I would like a function where:
The Database Administrator enters a new Equipment Type: PC
He then enters a new Equipment Type Specific: Laptop
He then needs to create a Specification Table. The fields of this data depends on what Specification Details he wants recorded for a laptop. Therefore having a unbound form with a check box for each possible specification means that the Admin user could tick RAM, HDD, CPU, PSU and enter a table name. Pressing a button would then create the specification table based on his requirements.

Hope that makes sense?

 

[epicmove]

So the make table function.....??

I wouldnt say that I was exactly new to Access, nor to the concept of normalisation.
Much better to have the Equipment_Type_Specific_ID in the actual spec record as it would make it easier to link (thanks).

All I want is a function to create the tables, the idea being that the admin user will not have access to the structure of the database to create these tables.
If he creates a new Equipment Type (PC) then the specific type (Laptop) he then needs to create the spec table for the PC > Laptop. Rather than me having to create this manually I would like a form where the admin user can essentially select what field he wants in the spec table (RAM, CPU) and then click a command button to automatically create a table.

I have seen the VB code to create a table with various fields/data types. Surelly it is possible to have a number of "create field" procedures that execute if the relevant check box on the form = "true"?

Thanks (sorry for dragging this on)