Auto Calc Date Based on Another Date Field 1

[frosty2000]

Hello,

I am tracking employee's benefits in an Access database and have designed a form for input. I would like to automatically calculate the Coverage Start Date based on +30 days from the employee's date of hire once the user selects the plan in which they are enrolled.

Below are a description of my fields:
MedicalPlan - The user selects the name of the medical plan in which the employee is enrolled
EmployeeMedicalCoverage - a checkbox that automatically is set to "Yes" if the user is enrolled in a plan
EmployeeMedicalCoverageStartDate - The effective date of benefits (30 days from latest date of hire)
LatestHireDate - The employees most recent hire date

I have successfully been able to get the checkboxes to work, but the EmployeeMedicalCoverageStartDate field is not populating. I am not getting an error - the field is just blank. Here's my code:

Private Sub MedicalPlan_AfterUpdate()

If [MedicalPlan] <> "Medical Declined" Then
[EmployeeMedicalCoverage] = True
[EmployeeMedicalCoverageStartDate] = DateAdd("d", 30, [LatestHireDate])
Else
[EmployeeMedicalCoverage] = False
[EmployeeMedicalCoverageStartDate] = [EmployeeMedicalCoverageStartDate]

End If
End Sub

Any ideas? Thanks!
frosty2000

 

[TheAceMan1]

How are ya frosty2000 . . . . .

According to your code [blue]MedicalPlan[/blue] has to equal [blue]"Medical Declined"[/blue] for the conditions you describe. The else part does nothing (equal to itself). So if [purple][EmployeeMedicalCoverageStartDate][/purple] is empty?

Does [blue]MedicalPlan[/blue] perhaps have an extra space?

You need ro do something specific in the [blue]else[/blue] part of the If statement or eliminate it!

See Ya! . . . . . .

 

[frosty2000]

Hey AceMan,

The options in my MedicalPlan combo box list out the actual medical plan names (i.e. Medical Plan Gold, Medical Plan Silver) as well as "Medical Declined". So, if someone is actually enrolled in a plan, I want it to check the "yes" box and also auto populate the EmployeeMedicalCoverageStartDate field. However, if "Medical Declined" is selected the check box is "no" and no date should be filled in for the start date.

What am I doing wrong? Also, this might be a silly question, but in my VB code, how do I refer to my fields? For instance, "EmployeeMedicalCoverageStartDate" is the name of my text box in the "Other" tab in "Properties", but my control source (from my table) is actually a field with spaces (i.e. "Employee Medical Coverage Start Date".

Again, I don't know much about VB and am new to all of this...any help would be appreciated!
frosty2000

 

[frosty2000]

Hey there....

Just wanted to let you know I got it all to work. Silly me...I think part of the reason it wasn't working on my test record was because there was no date input into the LatestHireDate field!

I ended up changing the fields so that the name of the boxes AND the control source had no spaces. I'm still not sure which one to use in my VB coding, but I figured if they were the same it wouldn't matter.

frosty2000

Here's my code:
If [MedicalPlan] <> "Medical Declined" Then
[EmployeeMedicalCoverage] = True
[EmployeeMedicalCoverageStartDate] = DateAdd("d", 30, [LatestHireDate])
Else
[EmployeeMedicalCoverage] = False

End If
End Sub

 

[TheAceMan1]

[blue]Good for you frosty2000![/blue]

See Ya! . . . . . .