Setting values in a table to another record

[rcoutts]

I have a table where each record is a Job Name and its associated data -- Job Number, Start Date, etc. E.g.:

JobNum JobName StartDate ...
------ ------- -------- ...
1123 Smith 1/1/2001 ...
1124 Jones 2/1/2001 ...
1125 Lascarro 3/1/2001 ...

I have a second table that is a Time Card. E.g.:

JobNum JobName WorkDate Hours
------ ------- -------- ------
1124 Jones 6/8/2001 11.5

The Time Card table references JobName in the Jobs table. What I'd like to do is when I select JobName from a combo box I want the JobNum field to be populated automatically. What is the best way to do this? I thought that writing a Module that fired everytime JobName changed that set JobNum was the way to go, but I couldn't figure out how to set JobNum to the correct value. The closest I got was getting the Record Number in the Jobs table, but I don't see how to get the JobNum associated with that record. Can someone clue me in?

Thanks!
Rich

 

[tlbroadbent]

Check thread181-129566. I answered a similar question there. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[MarsBar]

Here's how I would do it. First, get rid of TimeCard.JobName. It's needlessly cluttering up your database. All you need is TimeCard.JobNum. If you know that number, you can get the JobName by looking it up in the Jobs table. Create a form based on the TimeCard table.
Put a combo box on the form. On the Data tab, set the control source to JobNum. Row Source Type is Table/Query. Bound column is 1. Click Row Source, then the 3 dots to the right of it. In the Show Table screen, add the Jobs table. Click Close. Double-click JobNum to add it to the query. Then do the same for JobName. Make sure JobNum is the FIRST column. In the JobName column, click Sort and type Ascending. Close the query. Now on the Format tab for your combo box, set Column Count to 2. Set Column widths to 0";2". (Second number can be whatever you want; first number must be zero to keep the job number from showing.)

Now the user can use the combo box to select a job name, and the correct job number will get added to the TimeCard table behind the scene.

When you want to do timesheet reports, base them on a query that links the JobNum fields of Jobs and TimeSheets.