Increment Access Date Field

I want to increment an access form date field every 90 days based on another date field -- the starting point. Another exchange expert wrote the code below however when I add a new record the form is in an endless loop and locks the database. I'm looking for a solution to the  loop problem. I have attached the databses - from the main menu select Add/Chng Veterans ALL to access the form VASH1.

Private Sub Form_Current()
Me.TreatmentDate = DateAdd("d", 90, Me.AdmitDate)
Do
     Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
   
Loop Until Date <= Me.TreatmentDate

End Sub


Private Sub Form_Load()
If Not IsNull(Me.TreatmentDate) Then
  If Date > Me.TreatmentDate Then
    Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
  End If
Else
'MsgBox "TreatMent Date is " & Nz(Me.TreatmentDate, #1/1/1900#)
Me.TreatmentDate = DateAdd("d", 90, Me.AdmitDate)
Do
     Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
   
Loop Until Date <= Me.TreatmentDate

End If
End Sub

VASH-V1-4.accdb
shieldscoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
0
shieldscoAuthor Commented:
After further review I also noticed that once I move from the current record the treatmentdate field data is incorrect - it appears that it is adding 180 days to the date instead of 90 days
0
Gustav BrockCIOCommented:
Try this:

Private Sub Form_Current()
  If IsNull(Me.TreatmentDate) Then
    Me.TreatmentDate = DateAdd("d", 90, Me.AdmitDate)
  End If
  While DateDiff("d", Me.TreatmentDate, Date) >= 0
    Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
  Wend  
End Sub

The Sub Form_Load() should not be needed but you may need an AfterUpdate event for AdmitDate if this can be Null and can be filled out by the user.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shieldscoAuthor Commented:
Good job
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.