Automatically add a record to another table

N

NoviceLois

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.
 
R

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
 
N

NoviceLois

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
 
N

NoviceLois

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?
 
M

Mike Painter

NoviceLois said:
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.
Click to expand...

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.
 
Top