Transfer Information b/w Records

[Guest]

Hi,

I am new to design and code for Access, so my question is probably pretty
simple to some...

Is it possible to take a majority of information that you enter on one
record (i.e. Company name, PO, Due Date, Ship Date, etc) and transfer it to
the next record by clicking a command button (I imagine this will require
some type of macro?) so that it appears on the next record without having to
enter the information again?

And if so, is it possible for someone to guide me thru this process or offer
a weblink on a how-to?

 

[Rick B]

Yes it is possible, but you should not do it.

If you are copying all that to another record, then you are trying to build
a spreadsheet, not a relational database.

Instead, you need a separate table where you create ONE record for each
company, including a company ID Number.

In your detail table, you only store that ID number.

In your reports, forms, and queries, you can include both tables and then
you will be able to print the company information for all the records, but
only store the company ID number in your detail table.

For more details, do a search and read up on "relational database
normalization".

Hope that helps.

 

[jahoobob via AccessMonster.com]

Try this:
http://www.mvps.org/access/forms/frm0012.htm

Yes it is possible, but you should not do it.

If you are copying all that to another record, then you are trying to build
a spreadsheet, not a relational database.

Instead, you need a separate table where you create ONE record for each
company, including a company ID Number.

In your detail table, you only store that ID number.

In your reports, forms, and queries, you can include both tables and then
you will be able to print the company information for all the records, but
only store the company ID number in your detail table.

For more details, do a search and read up on "relational database
normalization".

Hope that helps.

Hi,

[quoted text clipped - 12 lines]

offer
a weblink on a how-to?

 

[Guest]

Thanks for your reply... let me elaborate a bit and then tell me if you still
think the same way, because I get what you are saying, but I am not sure if I
was clear on what I am doing with the data entry form.

This database is for order entry... it currently has tables for orders,
vendors, customers, product information, etc. So when the data entry is
performed in the order entry table (via the order entry form), we are
entering a Purchase Order line item by line item and each line item is
therefore a new record (due to back orders and billing on products they must
be unique records), and thus have their own Record ID #.

My thought was if someone starts entering in information on a Purchase Order
that contains 20 line items, that is 20 times they must enter the same
"header" information again (Company [drop down combo box], PO#, ship date,
due date, etc.), but the product information will change with each record.
It seems tedious...

Even with this explanation, do you believe it is not a good idea to "push"
the basic "header" information, per se, forward or have a question box come
up on the screen asking the user if they would like a new record or maintain
current record information?

Hopefully I am expressing myself clearly... it's a lot to convey via email!
: )
--
Thanks for your help!
Shalen

Yes it is possible, but you should not do it.

If you are copying all that to another record, then you are trying to build
a spreadsheet, not a relational database.

Instead, you need a separate table where you create ONE record for each
company, including a company ID Number.

In your detail table, you only store that ID number.

In your reports, forms, and queries, you can include both tables and then
you will be able to print the company information for all the records, but
only store the company ID number in your detail table.

For more details, do a search and read up on "relational database
normalization".

Hope that helps.

 

[Rick B]

Your structure is not normalized.

Look at the "orders" in the sample Northwinds database.

You should have a table for each Purchase order where you enter the "header
information". This should include the Company ID Number, the PONumber, and
the Date. In a separate table, you should have all your Customers with
their ID and details. Another table should store all your products that you
sell. Another table should be the PO DETAILS table where you add one record
per line on the PO.

TbCustomers
CustomerID
CustomerName
CustomerAddress
etc.

TblProducts
PartNumber
PartName
PartCost
PartSell
Active Y/N
etc.

TblPOHeader
PONumber
CustomerID
PODate
ApprovedBy
etc.


TblPODetials
PONumber
PartNumber
Quantity
etc.


In this example, a PO with five products would have one entry in TblPOHeader
and five records in TblPODetails. The customer data would be stored once
and only once in TblCustomers. Each Product you sell will have one record
(and only one record) in TblProducts.

Hope that helps.



--
Rick B

Thanks for your reply... let me elaborate a bit and then tell me if you
still
think the same way, because I get what you are saying, but I am not sure
if I
was clear on what I am doing with the data entry form.

This database is for order entry... it currently has tables for orders,
vendors, customers, product information, etc. So when the data entry is
performed in the order entry table (via the order entry form), we are
entering a Purchase Order line item by line item and each line item is
therefore a new record (due to back orders and billing on products they
must
be unique records), and thus have their own Record ID #.

My thought was if someone starts entering in information on a Purchase
Order
that contains 20 line items, that is 20 times they must enter the same
"header" information again (Company [drop down combo box], PO#, ship date,
due date, etc.), but the product information will change with each record.
It seems tedious...

Even with this explanation, do you believe it is not a good idea to "push"
the basic "header" information, per se, forward or have a question box
come
up on the screen asking the user if they would like a new record or
maintain
current record information?

Hopefully I am expressing myself clearly... it's a lot to convey via
email!
: )

 

[Rick B]

Note that if you wanted to get really fancy, you could also have a table for
"contacts" that is related to the "Customers" table. This could include one
(or many) contact people and their direct extensions and data. This table
could be used as a drop-down in your POHeader table for the "Approved By"
field.



--
Rick B

Your structure is not normalized.

Look at the "orders" in the sample Northwinds database.

You should have a table for each Purchase order where you enter the
"header information". This should include the Company ID Number, the
PONumber, and the Date. In a separate table, you should have all your
Customers with their ID and details. Another table should store all your
products that you sell. Another table should be the PO DETAILS table
where you add one record per line on the PO.

TbCustomers
CustomerID
CustomerName
CustomerAddress
etc.

TblProducts
PartNumber
PartName
PartCost
PartSell
Active Y/N
etc.

TblPOHeader
PONumber
CustomerID
PODate
ApprovedBy
etc.


TblPODetials
PONumber
PartNumber
Quantity
etc.


In this example, a PO with five products would have one entry in
TblPOHeader and five records in TblPODetails. The customer data would be
stored once and only once in TblCustomers. Each Product you sell will
have one record (and only one record) in TblProducts.

Hope that helps.



--
Rick B

Thanks for your reply... let me elaborate a bit and then tell me if you
still
think the same way, because I get what you are saying, but I am not sure
if I
was clear on what I am doing with the data entry form.

This database is for order entry... it currently has tables for orders,
vendors, customers, product information, etc. So when the data entry
is
performed in the order entry table (via the order entry form), we are
entering a Purchase Order line item by line item and each line item is
therefore a new record (due to back orders and billing on products they
must
be unique records), and thus have their own Record ID #.

My thought was if someone starts entering in information on a Purchase
Order
that contains 20 line items, that is 20 times they must enter the same
"header" information again (Company [drop down combo box], PO#, ship
date,
due date, etc.), but the product information will change with each
record.
It seems tedious...

Even with this explanation, do you believe it is not a good idea to
"push"
the basic "header" information, per se, forward or have a question box
come
up on the screen asking the user if they would like a new record or
maintain
current record information?

Hopefully I am expressing myself clearly... it's a lot to convey via
email!
: )
--
Thanks for your help!
Shalen

Yes it is possible, but you should not do it.

If you are copying all that to another record, then you are trying to
build
a spreadsheet, not a relational database.

Instead, you need a separate table where you create ONE record for each
company, including a company ID Number.

In your detail table, you only store that ID number.

In your reports, forms, and queries, you can include both tables and
then
you will be able to print the company information for all the records,
but
only store the company ID number in your detail table.

For more details, do a search and read up on "relational database
normalization".

Hope that helps.

--
Rick B



Hi,

I am new to design and code for Access, so my question is probably
pretty
simple to some...

Is it possible to take a majority of information that you enter on one
record (i.e. Company name, PO, Due Date, Ship Date, etc) and transfer
it
to
the next record by clicking a command button (I imagine this will
require
some type of macro?) so that it appears on the next record without
having
to
enter the information again?

And if so, is it possible for someone to guide me thru this process or
offer
a weblink on a how-to?