Updating table with data not on form

[HighLightUK]

Hi,

I have a subform which builds up cost items for a job from a schedule of rates. This subform updates tblJobItems with the link to the main form, the various item ID's, the quantity and a mark-up figure.

tblJobItems
-----------
JobID
ItemID
Quantity
Markup

The problem I have is with the mark-up field.

I do not want the user to enter the mark-up figure on the subform as they have hundreds of these things to enter in a week.

As a solution, I have created a master mark-up in another table called tblCats. This is because there is only one mark-up figure per category of item, and all items fall into these various categories.

tblCats
-------
ItemCatID
ItemCatDesc
MarkupMaster

tblItems
--------
ItemID
ItemCatID
ItemDesc
ItemRate

Now while I can get the database to easily calculate the mark-up from the MarkupMaster field and apply it to the individual items, the problem is that the master mark-up changes occasionally, hence the Markup field in tblJobItems. I NEED to keep the history of the markup, so the jobs calculate correctly once the master changes.

Finally to what I want to do!

When the user selects or enters the ItemID from its combo, I want the database to lookup the ItemCatID for that ItemID and the mark-up required. Then I need it to write the master mark-up value found to the Markup field in tblJobItems.

Once this happens, I can easily get the database to maintain its correct pricing for historical jobs, while updating the master mark-up with the minimum of fuss.

Confused? I know I am!

Any ideas?

 

[HighLightUK]

If anyone can point me in the right direction it'll be appreciated, as I am completely lost.