Populate field in one table with data from field in another table

[OfficeMiner]

Access 2003, Windows XP sp2

Problem:
tblJob has a "Cost Center" field. tblInvoice has a "Charge To" field.

I want the tblInvoice field "Charge To" to automatically populate with the
information in the "Cost Center" field in the tblJob.

These tables are related by a mutual primary key but neither of these fields
are related and neither field is a primary field.

Is there an easy way to make the tblInvoice "Charge To" field populate
automatically with the information in the corresponding record entered in the
tblJob, "Cost Center" field?

I don't think the update query is what I am looking for nor is lookup as I
need the record directly associated with both tables by Job#(primary key).

I am hoping this is easy but I can't find an answer. Thanks for any
assistance.

 

[OfficeMiner]

Actually the data in the tblInvoice "might" be different from the default
charge code in tblJob. This would be rare but it will happen.

I do not have the fields related. Do I have to? I am new to database design
so the error could be mine. I did create an update query and it did work but
does not work like I need it to.

For the ease of input, I simply want the tblInvoice to automatically display
whatever charge code is entered in the tblJob. If it needs to be changed, I
will allow it to be changed. I guess I am looking to set a "Default Value"
and that value would be whatever value was is entered in the tblJob.Charge
Code field. In other words, the invoice table would automatically direct the
charge according to what was on the Job table; but it will need to allow for
the times when this is not correct and a manual entry will have to be made.
(Like in a country table, default to USA but allow changes).

Thanks for any help. I am usually pretty good at searching for answers. I am
either asking the wrong question or Access can not do what I want it to.

 

[OfficeMiner]

Terrific, thanks for your patience.

tblJob
Fields:
Job Number (Primary Key) one-to-many with same field in tblInvoice
Primary Firm - Business hired to perform work
Cost Center - (the department which will pay for the work_usually)
Date Started - date job was started
Desc of Work - work to be performed
etc. etc. etc.

tblInvoice
Job Number (Primary Key) (many-to-one with same field in tblJob)
Primary Firm - Business hired to perform work (Primary Key)
Invoice Number - number of invoice (Primary Key)
Charge To - this field is the same as "Cost Center" in tblJob
Amount - charges
etc. etc.

Simply stated: We have one Job with many invoices. There are 20 tables in
database but these two will report and track the costs invoiced to each job.
When a department requires work and commits to a job then it is entered and
given a unique Job Number which is used to relate to most of the tables for
different information. What they want is for the Invoice table to
automatically populate to bill the department who requested the work. That is
the fields (Cost Center and Charge To from both these tables). The unique Job
Number is what ties the information together. I have to be able to allow for
the possibility that this Cost Center/Charge To could be different depending
on the circumstances and allow for an override of the populated information.

I have not related these particular fields??? In the tblJob the Cost Center
can only appear once and is unique. Of course there will be many invoices for
the job and most of them will be Charge To this same number.

Again, thank you for your patience. Any suggestions appreciated.

 

[Ellsun]

What would be the Visual Basic language you would use for this event
procedure? I want to create a record of history of field so I would like it
copied to a table every time is updated. I am using the "AfterUpdate" event
from the event tab in properties. But I guess I'm not telling the program
what to do properly. Can you e-mail me a sample event procedure to
(e-mail address removed)?