Look up a field name by it's value then changing a corresponding field value

[DrMann]

I am creating a form that will allow an employee to track the amount of time spent on a specific job. I may be over thinking it because I’ve thoroughly confused myself. Here’s what I have:
First, this table is in use by other individuals so I cannot modify the fields but I can change the field values. I say this because I know the solution would be much easier if the design was different.
A table (Orders) containing information for multiple Jobs (primary key field - Job Number). Each Job has up to six sub-jobs (field - Job 1, Job 2, etc.). Each sub-job has the amount of processing time (field – Job 1 Hours, Job 2 Hours, etc.).
I want to create a form where the employee can enter the sub-job number with the start/stop time. The database would then search the table for the record and update the appropriate hour’s field.
Ex.
Sub-Job – 12345.3
Start Time – 2:15PM
Stop Time – 4:15PM

Sub-Job “12345.3” is in the “Job 2” field of Job Number “12345” so “Job 2 Hours” field would get updated to add 2.00 to the existing value.
The code would first have to locate which field the sub-job was in then update the corresponding hours field.



Any help would be appreciated.

[ItsMe]

To be honest, I did not read your post very thoroughly. Perhaps you already answered my question. But, what is it that you are trying to do? Are employees trying to log some sort of event? are the employees out in the field or are they all in an office with desktop computers?

[DrMann]

The employees are on site at desktop pcs. The database is on the company server that everyone logs into. They are tracking their billable hours.
As I said in the original post, I know there are better ways but this is already in place so I'm just adding this functionality.

[ItsMe]

It seems the table structure is not normalized and I guess you understand this. That aside, giving access to multiple users should not be an issue.

Perhaps you can test this theory by creating a separate application to UPDATE the existing records. In your new app, link the table to your app using the import wizard. When linking, use UNC for the path and not mapped drives. Then, create a form and bind it to the linked table or an updateable query.

Once you have your form built, you can distribute your app to the Users.

By using a split database you will decrease the chance of two users locking a record at the same time.