Updating Table Record Using VB

[accnewbie]

I'm creating a small payroll program. Currently I have the following tables (among others):
Employee Data - Employee Name, Number, Address, etc.
Employee Hours - Employee Number, Work Date, Hours, [Filing Status (to be filled in using VB code from Employee Tax Status table]
Employee Tax Status - Employee Number, Valid As Of Date, Filing Status

After the user inputs data in the three fields of the Employee Hours file, I want to execute some VB code to figure out which Filing Status should be added to the record.

I'm new to this and have bought some MS Access & VB manuals. I'm looking for help in how to create the code to check for the latest validity date in the Employee Tax Status table for the Employee Number, pick up the associated Filing Status, and put it in the Employee Hours field. Any help on the code would be appreciated.

Thanks,
Ed

 

[Mudface]

Why store the same value in two tables? If the value is in another table, just use a query to pull the two things together.

 

[accnewbie]

The filing status has a validity date and doesn't have an exact match to the date worked. For example an employee may have had an original filing status of single effective 01/01/03 and changed it to married on 06/30/03. For work performed 12/01/03 I want to pick up the status with the latest validity date (06/30/03) and add this to the hours worked table (not hours worked field as mentioned in my original message).

If you can show me how a query can do this instead of VB that would be great as well.

Thanks,
Ed