Copy a record to another table?

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hello friends!!

I have two forms each with its own subform.

The first form consist of a main form with a subform for
PARTS COSTS,(a datasheet). So we have part id, part name,
etc etc for fields.

On this form we have a button that opens another form to
produce a list of parts based on a particular Company, or
part Id, or part name, etc etc.

This second form consist of a main form (with unbound
fields), that are used to search for a particular part.
We search by part no., name of company, and part name.

My question is this, once I select a particular part in
the datasheet,that is a record of one particular part,
how do I get this part added to the parts cost in the
first form? I have been using copy and paste over the
last few days, but I'm trying to automate it using a
command button.

For those who suggest copy & paste in VB, I am using
Access 2000, and...RunCommand is just not working! Access
keeps telling me "...Copy isn't available now".

Thank you

aj
 
Adam said:
Hello friends!!

I have two forms each with its own subform.

The first form consist of a main form with a subform for
PARTS COSTS,(a datasheet). So we have part id, part name,
etc etc for fields.

On this form we have a button that opens another form to
produce a list of parts based on a particular Company, or
part Id, or part name, etc etc.

This second form consist of a main form (with unbound
fields), that are used to search for a particular part.
We search by part no., name of company, and part name.

My question is this, once I select a particular part in
the datasheet,that is a record of one particular part,
how do I get this part added to the parts cost in the
first form? I have been using copy and paste over the
last few days, but I'm trying to automate it using a
command button.

For those who suggest copy & paste in VB, I am using
Access 2000, and...RunCommand is just not working! Access
keeps telling me "...Copy isn't available now".

Thank you

aj
Click to expand...


I think you need to re-look at your Table structure. In a relational
database, there is no reason to store the same data more than once in
different Tables, and every reason why you should not do so!

Forget Forms just for now, lets look at the Tables and how they relate
to each other; when that's right, the Forms should be easy-peasy!

You have parts which come from suppliers (your 'companies'), is that
right?

Presumably, a supplier may supply more than one of your parts? That is
a one to many relationship.

If a part can be supplied by more than one supplier as well, then you
have a many-to-many relationship to set up.

Come back to this thread and say which of these scenarii is correct:

1. One part can be supplied by more than one supplier.

2. One supplier can supply more than one part.

3. Both of the above.

hth

Hugh
 
Thank you Hugh

Response in Uppercase.
-----Original Message-----

####Deleted####



I think you need to re-look at your Table structure. In a relational
database, there is no reason to store the same data more than once in
different Tables, and every reason why you should not do
Click to expand...
so!

UH UH INCORRECT...IN THIS CASE THE FIRST FORM PROVIDES
INFORMATION ABOUT THE COST OF A PARTICULAR JOB. THE COST
OF THE SELECTED PART IS ADDED TO THE JOB. THE PART IS
SELECTED FROM THE PARTS LIST ON THE SECOND FORM.

OVER TIME THE PARTS LIST WILL BE UPDATED. OLD STOCK WILL
BE DELETED, NEW ONES WILL BE ADDED ETC ETC. BUT THE PART
SELECTED FOR THE JOB WILL BE ADDED TO THE JOB COST AND
WILL REMAIN PERMANENT.
Forget Forms just for now, lets look at the Tables and how they relate
to each other; when that's right, the Forms should be easy-peasy!


You have parts which come from suppliers (your 'companies'), is that
right?
YUP!

Presumably, a supplier may supply more than one of your parts? That is
a one to many relationship.

If a part can be supplied by more than one supplier as well, then you
have a many-to-many relationship to set up.

Come back to this thread and say which of these scenarii is correct:

1. One part can be supplied by more than one supplier.

2. One supplier can supply more than one part.

3. Both of the above.
Click to expand...


IT IS 3. MANY SUPPLIERS SUPPLY MANY PARTS, MANY SIMILAR.
SELECTION OF A PART WILL BE COST ISSUE. THEREFORE IT'S A
∞ ↔ ∞ RELATIONSHIP.

AS A FURTHER NOTE, THE BASE FORM FOR THIS SYSTEM COMES
FROM THE TEMPLATES GALLERY AT MICROSOFT, AND IF YOU LOOK
AT THE SERVICE CALL MANAGEMENT DB YOU'LL SEE HOW THE
PARTS COST IS INCORPORATED IN THE COSTING USING A QUERY
BUT IT IS NOT WHAT I REQUIRE BECAUSE THE BREATH OF
INFORMATION IN THE FORM IS NARROW. I REALLY REQUIRE THE
ABILITY TO OPEN A COMPLETE PAGE WITH THE PARTS IN IT
OFFERING A WIDER RANGE OF INFOMATION. ONCE I ENTER THE
COMPANY NAME, AND/OR THE PART NO., AND/OR PART TYPE (IN
THE UNBOUND SEARCH BOXES WITH THE PARTS FORM BEING THE
SUBFORM), THE PARTS FORM WILL RETURN ALL TRUE RECORDS AND
THEN I SELECT THE PART I WANT, THEN I WANT TO ADD IT BACK
TO THE JOB COST PAGE JUST BY CLICKING A BUTTON.


THANKS AGAIN

aj
 
Responses in-line:
Thank you Hugh

Response in Uppercase.

so!

UH UH INCORRECT...IN THIS CASE THE FIRST FORM PROVIDES
INFORMATION ABOUT THE COST OF A PARTICULAR JOB. THE COST
OF THE SELECTED PART IS ADDED TO THE JOB. THE PART IS
SELECTED FROM THE PARTS LIST ON THE SECOND FORM.

OVER TIME THE PARTS LIST WILL BE UPDATED. OLD STOCK WILL
BE DELETED, NEW ONES WILL BE ADDED ETC ETC. BUT THE PART
SELECTED FOR THE JOB WILL BE ADDED TO THE JOB COST AND
WILL REMAIN PERMANENT.
Click to expand...


There is still no need to delete parts from the parts Table; just have
a yes/no Field to indicate that a part is 'deleted'. This can be far
less complicated than moving the parts to another table and still
maintaining different costs, present and historic.

If you have historic cost figures to retain for historic costing
records, it is just the cost that needs to be retained and this can be
related to the part in the parts Table. You can incorporate a date
element to this denoting the period of validity for a cost for a part.

IT IS 3. MANY SUPPLIERS SUPPLY MANY PARTS, MANY SIMILAR.
SELECTION OF A PART WILL BE COST ISSUE. THEREFORE IT'S A
∞ ↔ ∞ RELATIONSHIP.
Click to expand...


In the case of a many-to-many relationship between the parts Table and
the suppliers Table, you will need a third 'resolving' Table containing
two Fields. These will be the foreign keys from the parts and
companies Tables' primary key Fields. The relationship will be
one-to-many between each of the parts and companies tables and the
resolving table.

AS A FURTHER NOTE, THE BASE FORM FOR THIS SYSTEM COMES
FROM THE TEMPLATES GALLERY AT MICROSOFT, AND IF YOU LOOK
AT THE SERVICE CALL MANAGEMENT DB YOU'LL SEE HOW THE
PARTS COST IS INCORPORATED IN THE COSTING USING A QUERY
BUT IT IS NOT WHAT I REQUIRE BECAUSE THE BREATH OF
INFORMATION IN THE FORM IS NARROW. I REALLY REQUIRE THE
ABILITY TO OPEN A COMPLETE PAGE WITH THE PARTS IN IT
OFFERING A WIDER RANGE OF INFOMATION. ONCE I ENTER THE
COMPANY NAME, AND/OR THE PART NO., AND/OR PART TYPE (IN
THE UNBOUND SEARCH BOXES WITH THE PARTS FORM BEING THE
SUBFORM), THE PARTS FORM WILL RETURN ALL TRUE RECORDS AND
THEN I SELECT THE PART I WANT, THEN I WANT TO ADD IT BACK
TO THE JOB COST PAGE JUST BY CLICKING A BUTTON.
Click to expand...


This is jumping ahead somewhat, until the Table structure and
relationships are defined.

THANKS AGAIN

aj
Click to expand...


hth

Hugh
 
I have two forms each with its own subform.
There is still no need to delete parts from the parts Table; just have
a yes/no Field to indicate that a part is 'deleted'. This can be far
less complicated than moving the parts to another table and still
maintaining different costs, present and historic.

If you have historic cost figures to retain for historic costing
records, it is just the cost that needs to be retained and this can be
related to the part in the parts Table. You can incorporate a date
element to this denoting the period of validity for a cost for a part.
Click to expand...

MY INTENTION IS TO ARCHIVE AND EXPORT JOB COSTS AT
APPROPRIATE DATES TO EXCEL.

I DON'T WANT A SINGLE TABLE WITH ALL PARTS HAVING MANY
JOBS RELYING ON THE ONE TABLE FOR PARTS INFORMATION.

IF DELETIONS REMAIN IN THE PARTS TABLE, THEN THE TABLE
WILL GET BIGGER, AND BIGGER, AND BIGGER AS NEW PARTS ARE
ADDED.

IF CHANGES TO THE PARTS, SAY COSTS, WERE TO OCCUR, THEN
THIS WOULD LEAD TO SEVERAL ROWS WITH IDENTICAL
INFORMATION ABOUT THE SAME PART WITH COST BEING THE ONLY
DIFFERENCE.

AND IF AN ERROR WERE TO OCCUR TO ONE PARTICULAR RECORD,
SAY MISTYPING THE PARTS COST, IT WOULD AFFECT ALL JOBS
THAT RELY ON THE ACCURACY OF THAT RECORD.

NO... I STILL BELIEVE THE BEST AND SAFEST THING IS TO
COPY THE PARTS RECORD 'ON THE SPOT' AND INCLUDE IT IN THE
JOB'S COSTING FORM.

I APPRECIATE THAT THIS IS DRIFTING AWAY FROM RELATIONAL
DB THEORY, BUT I AM JUST UNCOMFORTABLE ABOUT DOING IT
THAT WAY.

In the case of a many-to-many relationship between the parts Table and
the suppliers Table, you will need a third 'resolving' Table containing
two Fields. These will be the foreign keys from the parts and
companies Tables' primary key Fields. The relationship will be
one-to-many between each of the parts and companies tables and the
resolving table.
Click to expand...



THANKS AGAIN

aj
 
Responses in line:

MY INTENTION IS TO ARCHIVE AND EXPORT JOB COSTS AT
APPROPRIATE DATES TO EXCEL.
Click to expand...

Well, you can look at TransferSpreadsheet in Help for details on how to
do this.
I DON'T WANT A SINGLE TABLE WITH ALL PARTS HAVING MANY
JOBS RELYING ON THE ONE TABLE FOR PARTS INFORMATION.

IF DELETIONS REMAIN IN THE PARTS TABLE, THEN THE TABLE
WILL GET BIGGER, AND BIGGER, AND BIGGER AS NEW PARTS ARE
ADDED.
Click to expand...

So, what is the problem with that?
IF CHANGES TO THE PARTS, SAY COSTS, WERE TO OCCUR, THEN
THIS WOULD LEAD TO SEVERAL ROWS WITH IDENTICAL
INFORMATION ABOUT THE SAME PART WITH COST BEING THE ONLY
DIFFERENCE.
Click to expand...

No, the costs and their dates of validity only would be in a separate
Table with pointers to the relevant Parts/Jobs in the other Tables.
There need be no unnecessary data duplication. This is what relational
databases are about.
AND IF AN ERROR WERE TO OCCUR TO ONE PARTICULAR RECORD,
SAY MISTYPING THE PARTS COST, IT WOULD AFFECT ALL JOBS
THAT RELY ON THE ACCURACY OF THAT RECORD.
Click to expand...

So the correction would only need to be done in the one root record
rather than in various different places, with the resulting
inconsistenties.
NO... I STILL BELIEVE THE BEST AND SAFEST THING IS TO
COPY THE PARTS RECORD 'ON THE SPOT' AND INCLUDE IT IN THE
JOB'S COSTING FORM.

I APPRECIATE THAT THIS IS DRIFTING AWAY FROM RELATIONAL
DB THEORY, BUT I AM JUST UNCOMFORTABLE ABOUT DOING IT
THAT WAY.
Click to expand...

I'm afraid I cannot help you down that path as it departs from
established and well proven practice and will be outside my field of
experience. You may get done what you want done but I believe you will
have an enormous maintenance and design headache.

Good luck.

Hugh
 
Hi,
-----Original Message-----



Well, you can look at TransferSpreadsheet in Help for details on how to
do this.
Umm...yes...ok


So, what is the problem with that?
Click to expand...

Well, wouldn't the Database get too big, and use up
system resources?
No, the costs and their dates of validity only would be in a separate
Table with pointers to the relevant Parts/Jobs in the other Tables.
There need be no unnecessary data duplication. This is what relational
databases are about.
Click to expand...

right ok...

So the correction would only need to be done in the one root record
rather than in various different places, with the resulting

ok...
Click to expand...

quite frankly I don't know what I'm talking about!

I'm afraid I cannot help you down that path as it departs from
established and well proven practice and will be outside my field of
experience. You may get done what you want done but I believe you will
have an enormous maintenance and design headache.

Good luck.

Hugh
Click to expand...


No more headaches thank you.

Now let's do it your way.

Just to get the right ideas rolling, we'll start with
simplicity.

There are three tables. A Jobs table, Parts table and a
Company table. The Jobs table relates to Parts, a many to
many relationship, and Parts relate to company, again a
many to many relationship.

I have resolving tables for the Jobs/Parts and the
Parts/Company relationship, making the relationships one
to many.

Where do I go from here?



Many thanks indeed

aj
 
Adam said:
Hi,


Well, wouldn't the Database get too big, and use up
system resources?
Click to expand...


Not really, this is quite a normal practice. One of my clients is a
mail-order company and the records have been handled in this way for
about five years so far. If a deletion or transfer is ever required, a
simple query based on the Yes/No Field will do the job with no hassle.

One benefit in this particular case is that historic statistics can
always be produced for any time periods they choose, without any need
to go to a different programme.

right ok...



quite frankly I don't know what I'm talking about!




No more headaches thank you.

Now let's do it your way.

Just to get the right ideas rolling, we'll start with
simplicity.

There are three tables. A Jobs table, Parts table and a
Company table. The Jobs table relates to Parts, a many to
many relationship, and Parts relate to company, again a
many to many relationship.

I have resolving tables for the Jobs/Parts and the
Parts/Company relationship, making the relationships one
to many.

Where do I go from here?



Many thanks indeed

aj
Click to expand...

The first thing to do is to ensure you have the Tables with the right
Fields in them and the right relationships set up.

I would suggest the following structure for your Tables:

Each of your three Tables has a Primary Key (PK) unique index Field
using Autonumber. All the other Fields in those Tables will basically
just be data attached to the unique index for each record.

You will need two 'resolving' Tables for the links between Jobs and
Parts and between Parts and Company. These two Tables will have just
two Fields each, of Number type Long Integer. They will be for the
Foreign Keys (FK) to the Tables being linked. The Fields will need to
be indexed to Allow Duplicates.

In setting the Relationships (Referential Integrity and Cascaded
Deletes) link each of these FK Fields to the PK of the two Tables being
linked. For example, to link the Jobs and Parts Tables, that
'resolving' Table (say, tblJobsParts) will have one Field linked to the
PK in the Jobs Table and the other linked to the PK in the Parts Table.

When you've done that, we'll move on to the basic Form/Subform
structure, with the Master/Child links that will make it all work
(hopefully!).

Hugh
 
Thanks Hugh, comments in line.

###stuff deleted###
The first thing to do is to ensure you have the Tables with the right
Fields in them and the right relationships set up.

I would suggest the following structure for your Tables:

Each of your three Tables has a Primary Key (PK) unique index Field
using Autonumber. All the other Fields in those Tables will basically
just be data attached to the unique index for each record.

You will need two 'resolving' Tables for the links between Jobs and
Parts and between Parts and Company. These two Tables will have just
two Fields each, of Number type Long Integer. They will be for the
Foreign Keys (FK) to the Tables being linked. The Fields will need to
be indexed to Allow Duplicates.
Click to expand...

There are two 'resolving' tables each with two fields. Ok
if I call these fields Index1 & Index2?
In setting the Relationships (Referential Integrity and Cascaded
Deletes) link each of these FK Fields to the PK of the two Tables being
linked. For example, to link the Jobs and Parts Tables, that
'resolving' Table (say, tblJobsParts) will have one Field linked to the
PK in the Jobs Table and the other linked to the PK in
Click to expand...
the Parts Table.

So tblJobsParts field Index1 will link to the Index field
in the Jobs table and Index2 links to the Index field in
the Parts table.

And tblPartsCompany field Index1 will link to Index field
in Parts table, and Index2 will link to the Index field
in Company table.
When you've done that, we'll move on to the basic Form/Subform
structure, with the Master/Child links that will make it all work
(hopefully!).
Click to expand...

Having set the relationships, is a query required to
bring these tables together in the form?
Hugh
.
Click to expand...


Thank again

aj
 
Adam said:
Thanks Hugh, comments in line.

###stuff deleted###


There are two 'resolving' tables each with two fields. Ok
if I call these fields Index1 & Index2?
Click to expand...

Call them whatever is meaningful to you. Personally, I would not find
Index1 and Index 2 very meaningful in six months time! I would tend to
use a name that included the name of the Resolving Table and the name
of the Primary Key Field in its linked main Table and my Resolving
Table name would include the names of the two main Tables it resloves
but that's just my preference.
the Parts Table.

So tblJobsParts field Index1 will link to the Index field
in the Jobs table and Index2 links to the Index field in
the Parts table.

And tblPartsCompany field Index1 will link to Index field
in Parts table, and Index2 will link to the Index field
in Company table.
Click to expand...

See what I mean about the naming?!
Having set the relationships, is a query required to
bring these tables together in the form?
Click to expand...

Yes a Query will be needed that has Fields from both main Tables as
required by your forms.
 
-----Original Message-----


Call them whatever is meaningful to you. Personally, I would not find
Index1 and Index 2 very meaningful in six months time! I would tend to
use a name that included the name of the Resolving Table and the name
of the Primary Key Field in its linked main Table and my Resolving
Table name would include the names of the two main Tables it resloves
but that's just my preference.


See what I mean about the naming?!
Click to expand...

Ok. Names have been changed.
Yes a Query will be needed that has Fields from both main Tables as
required by your forms.
Click to expand...

So... it looks like that a major reason for being unable
to update tables (in my earlier models) is due to having
too many tables in my queries and that there were
no 'resolving tables' to eliminate many-to-many
relationships...

And this query is now being used as the basis to extract
information about part's costs from the part's table for
a particular job. So, if I open a Job form, and I select
say, Job 1, then select a part in the subform, the parts
costs,(through the query) will return the relevant costs
for that part. So...no need to copy and paste anymore!!

Looking back at the original question, the aim was to
select a relevant part applicable to a job. When examing
a suitable solution, Microsoft's web site offered sample
templates and the one that was applicable to my situation
was the Service Call Management template. Inside this, I
found that a part would be selected for a Workorder from
the Parts table and added to the WorkorderParts table. So
we have a record for the part in the Parts table, and a
record in the WorkorderParts table. Isn't this having
more than one instance of the record of the same kind?

Having got this far, is this as far as I need to go or is
there more to do?

Thanks again Hugh.

aj
 
Back
Top