Automatically add a record to another table

[Guest]

When I add a record to the JobMaster, I would like to have Access
automatically add the JobNumber to the ProjectProgress table as well.
JobNumber is a common field to both files and is the primary key on both
files (so no duplicates).

I am using Access 2003.

Thank you in advance for your thoughtful assistance.

 

[Rick B]

I did this in a database of mine by setting up my relationships properly as
one-to-one and including both tables in the query (upon which my form is
based). I included the "ProjectProgress" table's JobNumber field in the
form. I had code that said after my user entered the JobMaster.JobNumber,
place that same number in my ProjectProgress.JobNumber field.

I don't have that database handy, but the basic code was...


Sub... On Change...

ProjectProgress.JobNumber = JobMaster.JobNumber

End Sub



Hope that helps.
Rick B

 

[Joseph Meehan]

When I add a record to the JobMaster, I would like to have Access
automatically add the JobNumber to the ProjectProgress table as well.
JobNumber is a common field to both files and is the primary key on
both files (so no duplicates).

I am using Access 2003.

Thank you in advance for your thoughtful assistance.

Rich gave you a good answer, but I wonder why you have two tables? I
sounds like it should be one.

 

[Guest]

uh...I don't compose code unless it is a copy of someone else's so if you or
someone else has actual code, I'd surely appreciate it.

I liked your idea of creating a query to base my form on so will proceed
with that.

Thanks so much for the help, Rick!

Novice to Access,
Lois

 

[Guest]

I suppose I could easily enough add the 4 other fields from the
ProjectProgress table to the JobMaster. I don't have a good reason why I did
not incorporate the 2 except that I was trying to practice good database
building and there are already a bunch of fields in the JobMaster.

I really am not clear on the whole concept of how these decisions are
arrived at. Care to elaborate for a beginner?

 

[Joseph Meehan]

I suppose I could easily enough add the 4 other fields from the
ProjectProgress table to the JobMaster. I don't have a good reason
why I did not incorporate the 2 except that I was trying to practice
good database building and there are already a bunch of fields in the
JobMaster.

I really am not clear on the whole concept of how these decisions are
arrived at. Care to elaborate for a beginner?

You can have over 200 fields in a table. The theory of "normalization"
tells you when to split a table. In short you split a table when you have
many of the same sets of data repeated over and over.

For example. If you have a database of students and teachers in a
school, you could have a teacher field along with the teachers address,
phone number etc repeated for each student in their class. It would be
better to have a student table with just students and their information,
like name address etc, and a teacher list with their name address etc. The
two tables would be linked by teacher number or name (I suggest number) so
you can get a list of all the students a teacher teaches or a list of all
the students along with their teachers, or a maybe a list of students with a
list of teaches living in their zip code.

Access help file has some information on normalizing. Keep in mind
that normalizing is both a science and an art. It is at the heart of a
related database. It is very important to learn and understand the concept.

Have fun. That is what learning is all about. When you quit learning
you quit having fun and when that happens you are dead.

 

[Mike Painter]

When I add a record to the JobMaster, I would like to have Access
automatically add the JobNumber to the ProjectProgress table as well.
JobNumber is a common field to both files and is the primary key on
both files (so no duplicates).

I am using Access 2003.

Thank you in advance for your thoughtful assistance.

A job master in my mind is a table that holds master records for a job.
This would be a description, start date, estimated end date,etc.

It sould like the progress records would mark the progress for a particular
job.
These would be related to the master record in a many to one format.
Each progress record would have it's own unique key and contain, as a
foreign key, the ID of the master record.
A form holding the master with a subform holding the progress records would
fill this automatically.