Data of many records of 1 form to populate another form via Cmd bu

Hi,
I am working on a database in which I need to be able to use a cmd button to
populate a new form but I need to be able to add many records from the
initial form in order to do so.

for example:
for lack of a better example I will just make one up that will mock my real
life database. I have candles of varying degrees of sizes, aromas, colors,
manufacturers, etc. So each candle has its specified number such as CDL-1248.
If you open the main form that the data entry occurs to record this data it
is filled with data relevant to the specified candle. If I am creating a
requesition to fulfill an order I want to be able to look at the candle
record, click a command button that dumps the necessary information about the
candle onto the requesition. I need to be able to find another candle in the
records and add it to to the same requisition. I am really not sure how to go
about this. Any help is greatly appreciated.

thank you.

On Thu, 29 Oct 2009 11:13:03 -0700, Aso wrote:


Hi,
I am working on a database in which I need to be able to use a cmd button to
populate a new form but I need to be able to add many records from the
initial form in order to do so.

STOP.

Data is NOT STORED IN FORMS.

Forms are just windows, tools to let you work with data stored in a Table. You
can easily have two Forms both looking at the same table; you can also have
two Tables storing different data (each with its own form, or with a Form and
Subform to view data from both tables together, etc.).

If you're storing the same data in two different tables you're on the wrong
track! Redundancy is A Bad Thing, because redundancy is redundant!

for example:
for lack of a better example I will just make one up that will mock my real
life database. I have candles of varying degrees of sizes, aromas, colors,
manufacturers, etc. So each candle has its specified number such as CDL-1248.
If you open the main form that the data entry occurs to record this data it
is filled with data relevant to the specified candle. If I am creating a
requesition to fulfill an order I want to be able to look at the candle
record, click a command button that dumps the necessary information about the
candle onto the requesition. I need to be able to find another candle in the
records and add it to to the same requisition. I am really not sure how to go
about this. Any help is greatly appreciated.

thank you.

This sounds like a classic Many to Many relationship: each Candle can be
involved in many Requistions, and each Requisition can involve many Candles.

The proper way to do this in a relational database is with three tables:

Candles
CandleID primary key, unique ID such as CDL-1248
Description
other info about the candle as a thing in itself

Requesitions
ReqID primary key, perhaps an autonumber
RequisitionDate
CustomerID link to a table of customers
other info about the requisition as a whole, not about any individual
candles

RequisitionDetails
ReqID link to Requisitions
CandleID link to Candles
info about THIS candle in THIS order, e.g. quantity


You do not need to, nor should you, copy any information about the candle
itself into the Requisitions table or the RequisitionDetails table. Access is
a relational database - use it relationally! Store the information about the
candle once, in the candles table, and then use Queries to *combine* the data
from the Candles table with data from the RequistionDetails table with data
from the Requisitions table, dynamically, by joining the tables; on a printed
Report based on this query you could pull the candle description from the
Candles table, the requisition date from the Requisitions table, the quantity
ordered from the RequisitionDetails table, and so on.
--

John W. Vinson [MVP]

You keep asking the same question in the very same way and have been getting
the same kind of answer.
Do you not understand the responses or can you not find them?
--
Build a little, test a little.


"Aso" wrote:


Hi,
I am working on a database in which I need to be able to use a cmd button to
populate a new form but I need to be able to add many records from the
initial form in order to do so.

for example:
for lack of a better example I will just make one up that will mock my real
life database. I have candles of varying degrees of sizes, aromas, colors,
manufacturers, etc. So each candle has its specified number such as CDL-1248.
If you open the main form that the data entry occurs to record this data it
is filled with data relevant to the specified candle. If I am creating a
requesition to fulfill an order I want to be able to look at the candle
record, click a command button that dumps the necessary information about the
candle onto the requesition. I need to be able to find another candle in the
records and add it to to the same requisition. I am really not sure how to go
about this. Any help is greatly appreciated.

thank you.