Auto-Fill Text box on form

[Richard]

I am using Windows XP and Access 2000.

My apologies for the length.

This database is used to deliver training.

I have 5 tables 'TrainingWaiting', 'TrainingSchedule', 'TrainingDuration',
'TrainingCourses', and 'TrainingTrainers'.

The field 'Course' in the table 'TrainingWaiting' holds the unique value of
the ID field in the table 'TrainingSchedule'. There is a one-to-many
relationship between the ID field in 'TrainingSchedule' and the field
'Course' in the table 'TrainingWaiting'.

I use this to enable combo boxes in forms and reports to auto-fill text
boxes with the other columns in the 'TrainingSchedule' table. For example one
form has a combo box, the control source is 'Course' and there is a SELECT
that queries the 'TrainingSchedule' table. This allows me to use
=TRACourse1.Column(5) for example to auto-fill a text box with the trainers
name form the 'TrainingSchedule' table.

The problem I have now is as follows.

The 'TrainingDuration' table has 4 fields, ID, Duration, StartTime, and
EndTime. The 'ID' field is linked to the 'Duration' field in the
'TrainingSchedule' table. Which works fine if you go into the actual Schedule
table.

What I want to be able to do is have the fields 'StartTime' and 'EndTime'
auto-fill in the various forms. The problem is the StartTime and EndTime
aren't actually in the Schedule table, they're in the Duration table.

I hope this makes sense. If not, I will try to explain further.

Thank you in advance for any help.

Richard

 

[John W. Vinson]

I am using Windows XP and Access 2000.

My apologies for the length.

This database is used to deliver training.

I have 5 tables 'TrainingWaiting', 'TrainingSchedule', 'TrainingDuration',
'TrainingCourses', and 'TrainingTrainers'.

The field 'Course' in the table 'TrainingWaiting' holds the unique value of
the ID field in the table 'TrainingSchedule'. There is a one-to-many
relationship between the ID field in 'TrainingSchedule' and the field
'Course' in the table 'TrainingWaiting'.

I use this to enable combo boxes in forms and reports to auto-fill text
boxes with the other columns in the 'TrainingSchedule' table. For example one
form has a combo box, the control source is 'Course' and there is a SELECT
that queries the 'TrainingSchedule' table. This allows me to use
=TRACourse1.Column(5) for example to auto-fill a text box with the trainers
name form the 'TrainingSchedule' table.

The problem I have now is as follows.

The 'TrainingDuration' table has 4 fields, ID, Duration, StartTime, and
EndTime. The 'ID' field is linked to the 'Duration' field in the
'TrainingSchedule' table. Which works fine if you go into the actual Schedule
table.

What I want to be able to do is have the fields 'StartTime' and 'EndTime'
auto-fill in the various forms. The problem is the StartTime and EndTime
aren't actually in the Schedule table, they're in the Duration table.

I hope this makes sense. If not, I will try to explain further.

Thank you in advance for any help.

Richard

What doesn't make sense is that you're storing data redundantly. If the
StartTime is stored in the TrainingDuration table, then it should not exist in
any OTHER table, unless you want to allow the StartTime to be inconsistant and
different between the two tables. Is that what you're trying to do? If so,
why? If not, what's the purpose of filling the form textboxes?

Do note that you can base the combo box on a query linking the course and the
TraningDuration tables, so that all of the fields in both tables are available
for viewing.

 

[golfinray]

You are going to need some sort of link between "Duration" and the "Schedule"
table then you can add a subform for start and end times.

 

[Richard]

Thanks for both of your replies.

Having the text boxes fill automatically is because the data is already in
the Schedule table, and so is just brought through from there. This displays
the data on a given form minimising mistakes and time spent on data input.

I have decided to include the StartTime and EndTime in the Schedule table,
so the Duration table isn't needed now.

Richard

 

[John W. Vinson]

I have decided to include the StartTime and EndTime in the Schedule table,
so the Duration table isn't needed now.

If the duration is the difference in time between StartTime and EndTime, it
should not be stored in any table; it can be calculated on the fly using the
DateDiff() function.