Pull multiple records from one table to another?

  • Thread starter Thread starter Spagmess
  • Start date Start date
S

Spagmess

I'm trying to create a database that will handle concert power distribution
systems. The systems are modular and mixed/matched for each show. I have
several different modules to choose from, each with different numbers of
circuits and different voltages, etc.

I have a table (ModuleCircuits) storing all the circuit information for each
type of module with the fields:
ModCircuitID
ModID (linked to Modules table)
ModCircuitNumber
ModCircVoltage
ModCircAmperage
etc...

When I go to put together modules for a show, I want to pull information
from the Modules table to populate another table (ShowCircuits). For example,
I want to use 10 of type 'Module 1' on a show. Module 1 has 6 circuits, so in
my ShowCircuits table, I end up with 60 records with CircuitNumber 1 thru 60,
and all the Voltage and Amperage fields for each so it can properly relate to
the rest of my database. Up until now, I've had to manually populate the
ShowCircuits table.

So the ModuleCircuits table is not related to the rest of the database in
any meaningful way, it just contains the information needed to populate
another table. I'm not sure if this is the right approach, just the approach
that made sense in my head.

My plan is to have a form with a subform where I can select one module at a
time and click a button to add new records to ShowCircuits based on the
information in the circuit records for that module. I just don't know the
commands to make that happen.

I'm fairly new to VBA, so the solution is a complete mystery to me. Any help
you can offer would be much appreciated. Thanks for reading my long-winded
explanation.
 
I'm trying to create a database that will handle concert power distribution
systems. The systems are modular and mixed/matched for each show. I have
several different modules to choose from, each with different numbers of
circuits and different voltages, etc.

I have a table (ModuleCircuits) storing all the circuit information for each
type of module with the fields:
ModCircuitID
ModID (linked to Modules table)
ModCircuitNumber
ModCircVoltage
ModCircAmperage
etc...

When I go to put together modules for a show, I want to pull information
from the Modules table to populate another table (ShowCircuits). For example,
I want to use 10 of type 'Module 1' on a show. Module 1 has 6 circuits, so in
my ShowCircuits table, I end up with 60 records with CircuitNumber 1 thru 60,
and all the Voltage and Amperage fields for each so it can properly relate to
the rest of my database. Up until now, I've had to manually populate the
ShowCircuits table.
Click to expand...

Rather than creating a new table, or using any VBA code at all, I'd suggest a
different, relational-database suggestion.

It appears that you have a "many to many" relationship between Modules and
Shows. Each Module may appear in many Shows, each Show will have many Modules.
A simple junction table:

ShowModules
ShowID <link to a table of shows>
ModuleID
Quantity

will let you very quickly specify which modules appear in a new show, using a
form based on Shows and a subform based on ShowModules. A Query joining
ShowModules to Modules and thence to ModuleCircuits will let you very quickly
create a "virtual table" - really a three-table query - containing all of the
information you need. There is no need or benefit to manually or even
programmatically creating a new ShowCircuits table.
 
Wow. Sometimes the most elegant and simple solution is right in front of your
face. I'm thinking too hard about this! Thanks so much.
 
Back
Top