Adding records in subform

fgaiga

Registered User.
Local time
Today, 01:44
Joined
Aug 25, 2005
Messages
21
Hi,

I am a bit stuck now and I want to validate several points with you.

First let me explain the context: I am writing a timesheet application and I've got a main form + a sub-form.
In the mainform, I select the engineer, the contract and the week to input.
My mainform calculates the first and the last day of the selected week and modifies the recordsource property of my sub-form to select the records with the right engineer + contract nbr + range of first and last of week. This means that I can only have maximum 7 records for the seven days of the week because the application records only a global time per engineer/contract/per day.

Here it becomes tricky and this is the most difficult part of the application (I hope); what I want to do is to help the user having to enter the times.

so I need to retrieve the records but if they don't exist, I
want to create new records with engineerId, ContractID, day of the week and let the nbr of hours to zero. This in fact will help the user because he will have only to complete the nbr of hours. Iw ant obviously to present those entries in the right order; I mean in ascending ordre based on the day of the week.

Eventually, when the user has completed the week, what I want to do is to update the existing records, insert the new records where the number of hours is > 0 and discards rhe records where the number of hours is = 0.

I know this is a bit tricky to understand but if you could give the main ideas to implement it would be very grateful.


Regards.

Fabiano Gaiga.
 
To normalize the timesheets.

SELECT Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, Timesheets.Timedate AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[1] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[1])<>0))
UNION Select Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, [Timedate] +1 AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[2] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[2])<>0))
UNION Select Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, [Timedate] +2 AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[3] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[3])<>0))
UNION Select Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, [Timedate] +3 AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[4] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[4])<>0))
UNION Select Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, [Timedate] +4 AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[5] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[5])<>0))
UNION Select Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, [Timedate] +5 AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[6] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[6])<>0))
UNION Select Timesheets.Timeid, Timesheets.TimeContid, Timesheets.TimePosid, Timesheets.FCCtimeid, Timesheets.Timeshift, [Timedate] +6 AS Work_date, Timesheets.WOnumber, Timesheets.Timeuserid, Timesheets.[7] AS Work_Hours
FROM Timesheets
WHERE (((Timesheets.[7])<>0));

Now see the design of timesheets module
 

Attachments

  • 1.JPG
    1.JPG
    44.4 KB · Views: 204
  • 2.zip
    2.zip
    53.2 KB · Views: 199
Last edited:
This is the best approach I 've seen so far.

This looks great !!
 

Users who are viewing this thread

Back
Top Bottom