Autofill hours...

[ozzy68]

On my form I have the following:
txtStartDate
txtEndDate
cboStartTime
cboEndTime
txtDuration

I would like to have another text box that gets auto-filled with a number of hours based on the durations from the above listed information. For example, if txtStartDate=2/16/2012, txtEndDate=2/17/2012, cboStartTime=7:00 AM, and cboEndTime=3:00 PM...txtDuration=16.

Based on the above, there are two training days, each consists of eight hours of training, I want "txtDuration" to autofill the total training hours when I fill in the other boxes...

 

[jzwp22]

Do all of your training courses take the full 8 hours on each training day? If not, then you have to specify the start and end time of each day on which the training is held.

 

[ozzy68]

Thanks for the reply..no, the training start times and end times vary, I change them by choosing the times from a drop-down for both.

 

[jzwp22]

Technically speaking since each training event can have many start/end dates/times, that describes a one-to-many relationship which means that each start/end date should be records in a related table. BTW I would not separate the date from the time.


tblTraining
-pkTrainingID primary key, autonumber
-txtTraining

tblTrainingDays
-pkTrainDaysID primary key, autonumber
-fkTrainingID foreign key to tblTraining
-dteStart (date/time field with both date and time)
-dteEnd (date/time field with both date and time)


If we were to follow the rules of normalization strictly each date/time would be a record, more like this

tblTrainingDays
-pkTrainDaysID primary key, autonumber
-fkTrainingID foreign key to tblTraining
-dteTrain (date/time field with both date and time)
-EventName (start or end)

In terms of forms, you would have your main form based on tblTrain with a subform based on tblTrainingDays. You would then use a query to calculate the number of hours. You can then reference this on the main form using a Dlookup() function.

 

[ozzy68]

Got it, thanks!

 

[jzwp22]

Glad you got it worked out!