Calculation on 2 fields and a table

[Seajay]

I have a data entry form with a few fields and 2 drop downs.
The fields are as follows:

Box #
Contents
Content Date - Date Field
Department - Lookup from Dept Table
Doc Type - Lookup from DocType Table, only recs matching Dept.
Destroy Date - Date Field

The first drop down pulls data from a table and the second drop selections are pre-populated depending on the selection in the first drop down.

The Doc Type table would look something like this:
| ID | Deptartment | DocType | DaystoKeep |

What I'm trying to do is populate the Destroy Date field with the "Content Date" + "Daystokeep", located in the same recordset as the selected doc type. I'm trying to calculate this on the lost focus event of Doc Type. That way the user can update the date if need be. Any ideas?

Thanks -
Seajay

 

[rube]

Is the value of the DaysToKeep going to vary?

Private Sub DaysToKeep_AfterUpdate()
Me.KillDate = Me.DocType + Me.DaysToKeep
End Sub

 

[Seajay]

Yes, the value of days to keep will vary.

Based on what you gave me, the logic of the sub would be:

Private Sub DocType_AfterUpdate()
Me.DestroyDate = Me.DateofContent + [DocType]![RetentionPeriod] where [DocType]![ID]= me.DocType
End Sub

It's basically saying the destroy date with be the date of content plus the value in the DocType table where the ID on the form matches the one in the table. Unfortunately I can't get the code / SQL right.

As always, thanks!
- Seajay

 

[jfgambit]

Me.DestroyDate = Me.ContentDate + Me.DocType.Column(3)

HTH

 

[Seajay]

Thanks Gambit...
It's gotten me closer and I see where you're going, however I can't get it to read the 3rd column of DocType.

Private Sub DestroyDate_GotFocus()
Me.DestroyDate = Me.DateofContent + Me.DocType.Column(3)
End Sub

I'm able to replace DocType with a number (ie: 365) and it works , when I add the .column(3), the field remains blank. Any suggestions? As a side note, I'm using Access 97.

Thanks -
Seajay

 

[jfgambit]

What fields are in the drop down Doc Type combo? I assumed you pulled the following:

| ID | Deptartment | DocType | DaystoKeep |

If not, you need to add the DaystoKeep field to the combobox, You can then set the column accordingly. Remeber that the first column in a combobox is position 0, so if you had:

| ID | Deptartment | DaystoKeep | then Days to keep would be in column 2.

HTH

 

[Seajay]

Thanks!!!