Calculate Due Date based on fields from another table

[RuralGuy]

I tend to write maintainable code rather than something to get the job done unless execution speed is a consideration. I may have to change it in a few years and forget what on earth I did. The two pieces of information are available in the RowSource of the ComboBox but the modification factor is not. That means you will need to "hard code" that mod factor so the clearer the process, the easier to change later. I would simply add code to the AfterUpdate event of the ComboBox that shows clearly what you are doing and can easily place the results in whatever control you want.

[cin324]

Sorry but I have to be blunt....

What is the CODE that I need to put in the AfterUpdate event of the Combobox?

[RuralGuy]

Can you write in words all of the possible permutations and calculations from which we can create the code needed?

[cin324]

These should be all the permutations:
1 Month (30 Days)
2 Month (60 Days)
3 Month (90 Days)
4 Month (120 Days)
5 Month (150 Days)
6 Month (180 Days)
1 Year (365 Days)
2 Year (730 Days)
3 Year (1095 Days)
4 Year (1460 Days)
5 Year (1825 Days)
6 Year (2190 Days)
Initial
As Needed

I don't know if the number of days are needed or not, just thought I'd put them on here in case.

Thanks!

[RuralGuy]

I was busy yesterday and didn't get back to this until today. The code should be real easy. I'll get to work on it.

[RuralGuy]

This should get you started. You may need to convert some of the Columns since I don't know the DataType. You will need to also specify the real column number instead of my dummy 99.

Private Sub cboCourseSelection_AfterUpdate()
   Dim CourseFrequency As Double
   Dim CourseTrainingDate As Date

   With cboCourseSelection
      CourseFrequency = .Column(99)       '-- Point to the Frequency column of the RowSource
      CourseTrainingDate = .Column(99)    '-- Point to the TrainingDate column of the RowSource
      Select Case .Column(99)             '-- Point to the FrequencyPeriod column of the RowSource
                                          '-- Zero based number so the 2nd column would be (1)
         Case "Month"
            Me.ExpirationDate = DateAdd("m", CourseFrequency, CourseTrainingDate)
         Case "Year"
            Me.ExpirationDate = DateAdd("yyyy", CourseFrequency, CourseTrainingDate)
         Case Else
            '-- do nothing
      End Select
   End With
End Sub

[cin324]

Sorry, I just saw your reply - over a month later...

Where you have (99) am I supposed to use the table field name?

Thanks

[RuralGuy]

A ComboBox has a zero based Column value for selecting the value in that column. Therefore Column(2) would return the value in the 3rd column. It is a number.

[cin324]

I can't seem to attach my database so I took a few "screen shots". Attached is a .pdf showing the field I want to "autofill".

Thanks again!

[RuralGuy]

We should establish that FIELDS are in tables and CONTROLS are on forms. CONTROLS can display FIELDS. What is the SQL for the RecordSource of the Form?

[cin324]

I'm using the ScheduleCourse Table for the form.....

[RuralGuy]

So how are you filling in those other controls?

[cin324]

I'm not sure which "other controls" you are refering to....

[RuralGuy]

FrequencyPeriod and Frequency.

[cin324]

Those two fields are in another table - I'm guessing I need to create a Query for my form - adding those two fields to it?

Then they will be controls on the form for use in the calculations?