Multiple data sources for subform control

[Maija]

I have figured out quite a bit about Access while
developing this form, but I'm not going to be able to
absorb Visual Basic fast enough to figure this one out on
my own.

I have created a form with a subform for data entry of
Daily Time Logs that log 100% of work activites in a day.

The form record contains information regarding the
datasheet, including the "Begin Time" of the workday.

A subform record is created for each individual activity
logged on the datasheet. The "Finish Time" of each
activity is entered for each subform record.

The form and subform are related by the "DtShtNmbr"
control, the autonumbered primary key of the form. The
subform also has an autonumbered primary key, "ActvtyNmbr"

Where it gets hard for me: the subform also has an
invisible "Start Time" control. This field needs to be
filled in automatically. The data source for the first
subform record is the form's "Begin Time" control. The
data source for all subsequent subform records is
the "Finish Time" control from the previous subform
record.

I know that "ActvtyNmbr" -1 is crucial to working this
out, but I'm not sure how to get sequential records to
take data from different sources, and I have no idea how
to write the code.

Help is greatly appreciated!

 

[John Vinson]

Where it gets hard for me: the subform also has an
invisible "Start Time" control. This field needs to be
filled in automatically. The data source for the first
subform record is the form's "Begin Time" control. The
data source for all subsequent subform records is
the "Finish Time" control from the previous subform
record.

One way to handle this situation is to put code in the AfterUpdate
event of the [Finish Time] control:

Private Sub Finish_Time_AfterUpdate()
Me![Start Time].Default = Chr(34) & Me![Finish Time] & Chr(34)
End Sub

On the mainform's Begin Time you'ld put similar code, but referencing
the subform control:

Me!subformname.Form![Start Time].Default = <see above>

 

[Marshall Barton]

I have figured out quite a bit about Access while
developing this form, but I'm not going to be able to
absorb Visual Basic fast enough to figure this one out on
my own.

I have created a form with a subform for data entry of
Daily Time Logs that log 100% of work activites in a day.

The form record contains information regarding the
datasheet, including the "Begin Time" of the workday.

A subform record is created for each individual activity
logged on the datasheet. The "Finish Time" of each
activity is entered for each subform record.

The form and subform are related by the "DtShtNmbr"
control, the autonumbered primary key of the form. The
subform also has an autonumbered primary key, "ActvtyNmbr"

Where it gets hard for me: the subform also has an
invisible "Start Time" control. This field needs to be
filled in automatically. The data source for the first
subform record is the form's "Begin Time" control. The
data source for all subsequent subform records is
the "Finish Time" control from the previous subform
record.

I know that "ActvtyNmbr" -1 is crucial to working this
out, but I'm not sure how to get sequential records to
take data from different sources, and I have no idea how
to write the code.

I think you can set the first record's Start Time by using
this code in the main form's Current event:

Me.subform.[Start Time].DefaultValue = Me.[Begin Time]

Then the other records can be set in the subform's Finish
Time control's AfterUpdate event:

Me.[Start Time].DefaultValue = Me.[Finish Time]

All that assumes that the data is entered in finish time
order and that they never go back to a previous entry and
edit the finish time before adding more new entries. If
that might happen, then you have to check for all kinds of
situations to determine the appropriate start time for the
new record's finish time.