Update field w/ string based on service due dates

[Ruben]

Hi,

I have a form that provides a listing of various instruments that are
serviced on a monthly to annual basis, with general info about the
instrument, last and next service dates, etc.. What I am trying to
do
is create an On Open event procedure that will identify all
instruments that are listed with a string of either "Past Due" or
"Schedule Vendor" based on whether each of their respective 'Due'
dates have already transpired or not from the current month.


The code I initially created for this was as follows:


Private Sub Form_Open()
Dim myDate, myDue


myDate = DateSerial(Year(me.ListDate), Month(Me.ListDate), 1)
myDue = Me.NextServiceDate


If Me.Due < mydate then
Me.Status="Past Due"
Elseif Me.Due >= mydate then
Me.Status="Schedule Vendor"
End if

End Sub

This of course only updates the first 'Status' field on the list but
not the rest. I know I probably need to code this under a Do...Loop
or For Each...Next statement, but I simply cannot figure out the
right way to do this.

Any help would really be appreciated.


Regards,
Ruben

 

[missinglinq via AccessMonster.com]

Is this a Single View Form or a Continuous Form, and do you want this to
happen in one fell swope, or is it adequet that it happens as you view each
record? If it's a Single View Form, placing your code in the Form_Current
event will do this for each record as you move to it. Doing it for a
continuous form will be more problematic.

 

[Ruben]

Is this a Single View Form or a Continuous Form, and do you want this to
happen in one fell swope, or is it adequet that it happens as you view each
record? If it's a Single View Form, placing your code in the Form_Current
event will do this for each record as you move to it. Doing it for a
continuous form will be more problematic.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1

This is a Continuous Form and I do in fact want to accomplish this all
in one fell swope.

Thanks,
Ruben

 

[missinglinq via AccessMonster.com]

Then I think your best option would be to run an update query when you open
your database.

 

[Ruben]

Then I think your best option would be to run an update query when you open
your database.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1

I will try this. Your kind assistance is much appreciated.

Regards,
Ruben