Calculate Due Date based on fields from another table

[cin324]

I have a form for entering safety training which includes Training Date and Expiration Date, this form is called "Scheduled Training".

The table containing training courses with their requirements and frequency is called "Course Requirements". Within this table I have a "Frequency" field (1, 2, 3, 4, 5, 6) and another field "FrequencyPeriod" (Year, Month, Initial, As Needed).

What I'd like for the Expiration Date on the form to do: Use [ScheduledTraining].[Training Date] and look at both the [CourseRequirements].[Frequency] & [CourseRequirements].[FrequencyPeriod] to populate the Expiration Date.

Thanks!

[RuralGuy]

You can use a DLookup() to retrieve the information you need from the "Course Requirements" table.

[cin324]

Could you help me with that code for the DLookup?

Thanks!

[RuralGuy]

Here's a link for the syntax: http://access.mvps.org/access/general/gen0018.htm

Let us know if you need further assistance.

[cin324]

I've looked at that syntax before - what i'm struggling with is telling the database what Training Course to look at in the Course Requirements and then looking at the Frequency and Frequency Period for that particular course.

In the Schedule Training I have a combo box that points to the Course Code in the Course Requirments table. The particular course that is selected in the combo box is what I need to look at to calculate the new Expiration Date.

The Schedule Training form has a Scheduled Date that has to be used for the calculation too. Scheduled Date plus the Frequency and Frequency Period to come up with the new Expiration Date.

These are the table.field names:

CourseRequirements.CourseCode
CourseRequirements.Frequency
CourseRequirements.FrequencyPeriod
ScheduleTraining.comboCourseCode
ScheduleTraining.ScheduledDate
ScheduleTraining.ExpirationDate


Thanks for your help!

[RuralGuy]

Does the Course Requirments RowSource of your ComboBox contain the values you need from that table?

[cin324]

Yes, the combobox is looking at the records from the CourseRequirments.CourseCode and then storing that data in the ScheduleTraining.CourseCode field.

[RuralGuy]

Have you defined a relationship between the CourseRequirments table and the ScheduleTraining table?

[cin324]

Yes there is a relationship between the two tables - it's the CourseCode

[RuralGuy]

Great! You can then create a query of the two tables that returns all of the fields from both tables that you need for your form and use it as the RowSource of your ComboBox. Then when a selection is made in the ComboBox, you can use the AfterUpdate event of the cbo to fill in all of the controls you want.

[cin324]

When I create the query, do I need to use an "If" expression to look at both the "Frequency" and "Frequency Period" fields for the Next Due Date?

Something like this:
If ([ScheduleTraining].[Frequency]=1 And [ScheduleTraining].[FrequencyPeriod]=Year, [ScheduleTraining].[CourseDate]+365)If ([ScheduleTraining].[Frequency]=1 And [ScheduleTraining].[FrequencyPeriod]=Month, [ScheduleTraining].[CourseDate]+30)

etc. until I cover all possible frequency scenarios?

[RuralGuy]

You could put a more readable Select Case statement in the AfterUpdate event to determine the selection made.

[cin324]

I really don't know what you mean by your comment. I'm not all that familiar with sql.......

[RuralGuy]

The RowSource of the ComboBox does not need any IIF clauses, just a Join to get all of the appropriate fields of the two tables. Once a selection is made in the ComboBox, you can calculate and populate the [Expiration Date] control on your form in the AfterUpdate event of the cbo.

[cin324]

I was thinking the IIf clause needs to go in the query for the NextDueDate.

Per your suggestion I created a query using the two tables that have the fields I need to look at to calculate the next due date:

CourseRequirements.CourseCode (this is the join field between the 2 tables)
CourseRequirements.Frequency
CourseRequirements.FrequencyPeriod
ScheduleTraining.CourseDate
ScheduleTraining.NextDueDate
I was thinking the NextDueDate in the query would need the IIF clause, otherwise how do I calculate the fields using the Frequency, Frequency Period and Course Date?

Frequency: 1, 2, 3, 4, 5, or 6
Frequency Period: Month, Quarter, Year, Initial, As Needed
Course Date: Date the course was held

Thanks