How to copy a record from within a form

[LouD]

I have a database that keeps track of new computer serial numbers, as well as
the custom parts and the serial numbers of the parts that are added to the
computers. Sometimes the computers are returned and reused for a different
customer. When this occurs, I need to copy all of the previous serial
numbers from the old record to the new record and create a new report. How
do you copy all the data I need from the old record to the new record from my
form without opening up the table or without doing copy and paste for every
field? It will data from within the same database, but not necessarily
from the Last record, and most of the time I just need the serial numbers and
not the old customer information.
I'm not even sure this could be done. Thanks in advance for any help.

 

[Mike Painter]

I have a database that keeps track of new computer serial numbers, as
well as the custom parts and the serial numbers of the parts that are
added to the computers. Sometimes the computers are returned and
reused for a different customer. When this occurs, I need to copy
all of the previous serial numbers from the old record to the new
record and create a new report. How do you copy all the data I need
from the old record to the new record from my form without opening up
the table or without doing copy and paste for every field? It will
data from within the same database, but not necessarily from the Last
record, and most of the time I just need the serial numbers and not
the old customer information.
I'm not even sure this could be done. Thanks in advance for any help.

Customers have computers and computers have parts.
If yoou have three properly related tables: customers, computers, and parts
then all you have to do to assign the new cusatomer is to change the old
custID to the new one.

If you have a single record that contains all the information you will have
problems.

The Northwind Database has form, subform examples that deal with food but
should get the idea across.

 

[John Vinson]

I have a database that keeps track of new computer serial numbers, as well as
the custom parts and the serial numbers of the parts that are added to the
computers. Sometimes the computers are returned and reused for a different
customer. When this occurs, I need to copy all of the previous serial
numbers from the old record to the new record and create a new report. How
do you copy all the data I need from the old record to the new record from my
form without opening up the table or without doing copy and paste for every
field? It will data from within the same database, but not necessarily
from the Last record, and most of the time I just need the serial numbers and
not the old customer information.
I'm not even sure this could be done. Thanks in advance for any help.

Mike's questions are well take. What is the structure of your tables?

You say you don't want to "open the table". Well, you HAVE to open the
table, one way or another; data in an Access database is stored in
tables, and *ONLY* in tables. A Form is just a window, a tool to let
you deal with table data!

I suspect an Append query will be the most effective technique, but
I'd need to know more about your table structure to give any more
specific advice.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Mike Painter]

Thank you for you suggestion Mike Painter. One thing I forgot to
mention, is that according to the procedures that I must follow, I
have to maintain a device history. So I can't just change the name
of the customer and have an updated form. I have to create a whole
new report with the new information and retain the old data as well.

The following describes the structure of my tables.

I have a table with the customers names named tbl_customer
I have a table with the parts named tbl_ parts
The form, named frm_report, is compiled of a query between
tbl_customer and tbl_parts. The name of the query is qry_report.

The steps that I would take to create the new report with the used
equipment are:
1 - Create the report with the new customer information.
2 - Add the information for the new record which would be from taken
the old record.
What I normally do is either copy and paste between the new and old
record, or I print the old record and retype in the information.

I would like to avoid both of these approaches because copying and
pasting one field at a time between the old and new record is too
time consuming, and printing the old record and retyping the
information in could lead to incorrect data entry.

If your parts table has the form ItemID, serial number, description and
maybe date aquired etc and you consider the computer as just another part,
then you need a table containing RecordID, CustomerID , PartID and probably
a date, comment, etc. to tie them together. I would not do it that way.
If you just have two tables it is wrong, period and you will soon have
problems with it. Those problems will be far easier to manage in Excel.

You should also be aware that a report and the underlying data are two
different things.

 

[John Vinson]

One thing I forgot to mention,
is that according to the procedures that I must follow, I have to maintain a
device history. So I can't just change the name of the customer and have an
updated form. I have to create a whole new report with the new information
and retain the old data as well.

The following describes the structure of my tables.

I have a table with the customers names named tbl_customer
I have a table with the parts named tbl_ parts
The form, named frm_report, is compiled of a query between tbl_customer and
tbl_parts. The name of the query is qry_report.

The steps that I would take to create the new report with the used equipment
are:
1 - Create the report with the new customer information.
2 - Add the information for the new record which would be from taken the old
record.
What I normally do is either copy and paste between the new and old record,
or I print the old record and retype in the information.

I would like to avoid both of these approaches because copying and pasting
one field at a time between the old and new record is too time consuming, and
printing the old record and retyping the information in could lead to
incorrect data entry.

Mike's quite right, again. You're not taking full use of Access'
power! Neither copying and pasting the same data into a new record,
NOR retyping, is either necessary nor appropriate.

It seems you have a many to many relationship, or a couple of them.
Each part may (over time) be related to many customers; each customer
may deal with more than one part. Whenever you have such a
relationship, you need *a third table* which contains just the
information about which part is related to which customer; it should
NOT contain any information about parts, other than the PartID foreign
key, nor should it contain any information about customers other than
the CustomerID. Let's call it tblAssignment:

tblAssignment
PartID << link to tbl_Parts
CustomerID << link to tbl_Customer
AssignedDate
<any other info needed about this part/this customer>


You would enter the information for a part - once, and once only -
into tbl_Parts; and information about each customer (again, just once)
into tbl_Customer. When a given part is assigned to a new customer,
you would (using a Form with a Subform based on this new table) *add a
new record* to tblAssignment.

This allows you to link to the existing information about parts and
customers, without any need to copy or reenter it.

John W. Vinson[MVP]
(no longer chatting for now)

 

[LouD]

Mike's quite right, again. You're not taking full use of Access'
power! Neither copying and pasting the same data into a new record,
NOR retyping, is either necessary nor appropriate.

It seems you have a many to many relationship, or a couple of them.
Each part may (over time) be related to many customers; each customer
may deal with more than one part. Whenever you have such a
relationship, you need *a third table* which contains just the
information about which part is related to which customer; it should
NOT contain any information about parts, other than the PartID foreign
key, nor should it contain any information about customers other than
the CustomerID. Let's call it tblAssignment:

tblAssignment
PartID << link to tbl_Parts
CustomerID << link to tbl_Customer
AssignedDate
<any other info needed about this part/this customer>


You would enter the information for a part - once, and once only -
into tbl_Parts; and information about each customer (again, just once)
into tbl_Customer. When a given part is assigned to a new customer,
you would (using a Form with a Subform based on this new table) *add a
new record* to tblAssignment.

This allows you to link to the existing information about parts and
customers, without any need to copy or reenter it.

John W. Vinson[MVP]
(no longer chatting for now)

Thank you both for your suggestions, but I don't understand why only two
tables would lead to a problem. I also don't like using subforms because I
can't get the full power of searching with a subform. I must have the
ability to search every field on the form, or the database will not be of any
use to me. If you could explain more clearly why one table with the customer
and one table with parts is a problem, I would appreciate it.

 

[Mike Painter]

Thank you both for your suggestions, but I don't understand why only
two tables would lead to a problem. I also don't like using subforms
because I can't get the full power of searching with a subform. I
must have the ability to search every field on the form, or the
database will not be of any use to me. If you could explain more
clearly why one table with the customer and one table with parts is a
problem, I would appreciate it.

John's answers are quite clear. If you don't understand them then you should
sspend some time reading the help files which explain what a relational
database is and how they work.

I don't have a clue what you mean by not being able to get the full power of
searching with a subform.

 

[John Vinson]

If you could explain more clearly why one table with the customer
and one table with parts is a problem, I would appreciate it.

It is a problem because it does not reflect the reality of your
situation.

With two tables, you are stuck with a one to many relationship. Either
each customer can deal with zero, one, or more parts, and each part
belongs to one and only one customer; or, if you make the relationship
the other way, each customer can deal with one and only one part, and
each part can relate to zero, one, or several customers.

The reality of your situation appears to be that neither of these
relationships applies. Each customer can be involved with several
parts; a given part may be related to several customers. It's a many
to many relationship (as I understand your description); a two-table
linkage makes a many to many relationship impossible to model.

John W. Vinson[MVP]
(no longer chatting for now)

 

[LouD]

John's answers are quite clear. If you don't understand them then you should
sspend some time reading the help files which explain what a relational
database is and how they work.

I don't have a clue what you mean by not being able to get the full power of
searching with a subform.

Again, I thank you both for replying to my post and your suggestions. But I
just want you to understand that I have a very unique situation here. It's
my fault for not articulating it correctly. I have decided to not copy part
of a record to a new record within Access. The reason is that, sometimes I
don't even use all the parts for the new computer or sometimes I have to add
more parts. So I am forced to print the old record and manually type in the
serial numbers for the parts accordingly. For this reason, I feel creating a
"junction table" would not help. Thanks again for your suggestions. Also,
Mike let me elaborate more on what I meant about searching with subforms. I
have an older database at work, the main form is where the customers
information, work order, date and shipping info display. There is also a
subform on the main form, where the the computer info and parts info
displays. If I try to search within the subform using ctrl+f, with a serial
number that I know is in the database, Access returns no results.

 

[Mike Painter]

Again, I thank you both for replying to my post and your suggestions.
But I just want you to understand that I have a very unique situation
here. It's my fault for not articulating it correctly. I have
decided to not copy part of a record to a new record within Access.
The reason is that, sometimes I don't even use all the parts for the
new computer or sometimes I have to add more parts. So I am forced
to print the old record and manually type in the serial numbers for
the parts accordingly. For this reason, I feel creating a "junction
table" would not help. Thanks again for your suggestions. Also, Mike
let me elaborate more on what I meant about searching with subforms.
I have an older database at work, the main form is where the
customers information, work order, date and shipping info display.
There is also a subform on the main form, where the the computer info
and parts info displays. If I try to search within the subform using
ctrl+f, with a serial number that I know is in the database, Access
returns no results.

This is my last comment.
YOU ARE DOING IT WRONG.
If you did it as we suggested at the start you would NEVER have to enter ANY
part of any machine more than once. After that it's lookup tables and typing
one or two characters to get what you want.