"After Insert" Data Macro to populate field with data from another table based on other field in new entry (1 Viewer)

[dlambert]

Hello,
I have the following tables:

• tblJobs
  • JobID
  • ClientName
  • (more)
• tblWorkMaterials
  • JobID
  • ClientName
  • (more)

The table WorkMaterials list the materials used in each project so each entry has an associated JobID (from tblJobs)

I know it is not a standard way to do things as it would be a bit of data duplication, but it would make my life easier for other parts of the database (filtering etc) if i also have a field with tblWorkMaterials with the ClientName on it, which must match the client name associated with the JobID selected earlier, (and must be inputted automatically of course).

I tried to do it with calculated fields and other methods without success.
Then i thought to do it with an "After Insert" Data Macro on the tblWorkMaterials:

The above does not work as as the "Value" field does not specifically link with the selected JobID in the previous field (but i have no idea how to link it...).
Anyone have any suggestions of how i can get this Data Macro to do what i want? (or another way to accomplish the same thing?)


Thankyou for any assistance.

 

[Micron]

it would make my life easier for other parts of the database (filtering etc) if i also have a field with tblWorkMaterials with the ClientName on it,

I find that ironic. It would make it easier, yet here you are trying to do something you shouldn't and are having difficulty as a result. Why can you not get the jobID and anything you need (e.g. 'ClientName') by joining JobID to JobID in a query?
EDIT - also seems to me that ClientName will be in some kind of customer/client table (where it belongs) as well, which means even more duplication. I imagine the answer to my question is that you're working in tables (which you should not).

 

[arnelgp]

remove the ClientName from both tables.
you need to create a Separate table for the Client:

tblClient (table)
ClientID (PK, autonumber)
ClientName (text)
...
...

tblJobs (table)
JobID (PK)
ClientID
StartDate
EndDate
Status

also separate the tblMaterials, this is common to all Jobs.

tblMaterials(table)
Ident (PK)
MaterialDescription

the missing link, 4th table is the junction table between tblJobs and tblMaterials (Bill of Materials (BOM), to complete the Job).

tblBOM
JobID (FK to tblJobs)
Ident (FK to tblMaterials)
UOM
RequiredQty


(optional Manpower List).