Using data from one table in another form?

[Craig Armitage]

Hi,

I have 2 tables.. one called procedures and one called appointments.

The appointments has the following fields

AppDate
AppStartTime (Declared as date/time with a short time format)
AppEndTime (Declared as date/time with a short time format)
AppDuration (Declared as date/time with a short time format)
AppNotes

and the Procedures table has..

PrcName
PrcDuration (same as above)

I also have a Form for Appointments with a dropdown combo box that lets me
choose a pre-defined procedure from the list.

What i would like is for the AppDuration to be automatically populated when
I choose a procedure from the combo box. And also for it to calculate the
EndTime (using Starttime + Duration) in the Endtime Field.

I havent a clue how i would do this and would love some help

Thanks!

 

[Steve McLeod]

To start, I would not define duration as date/time. Pick an interval that is
meaningful for your application such as hour, half hour, 15 minutes, or
whatever. Then define a long integer that holds a count of the intervals.
You can then use DateAdd(interval, number, date) to add and substract your
intervals. Get the details from the object browser in the VBA window. You
may have to multiply your interval count by a factor to match one of the
intervals in DateAdd. If you are using 15 minute intervals then multiply by
15 and use the DateAdd("n",Clng(interval*15),AppStartTime).

Use the onClick event for your combo box to do the calculation. A simple
DLookUp can get your duration. Most people here recommend a query instead as
being faster, but DLookup is faster to code. Calculate the end time and
equate the relevant form controls to the calculated values.

Use DateDiff to get a duration between two date/time values.