Update field w/ string based on 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

 

[ruralguy via AccessMonster.com]

Without knowing more about your table, I suspect everything can be done in a
query and eliminate the OnOpen code. Have you looked into that?

 

[Ruben]

Without knowing more about your table, I suspect everything can be done in a
query and eliminate the OnOpen code. Have you looked into that?
















--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1- Hide quoted text -

- Show quoted text -

I have not looked into making it all work from within a query. A
select query is what I am using as my underlying source for the
calculated date fields. However, as records are updated and service
dates change, I don't know that the 'Status' field from within a query
can automatically update accordingly.

I am certainly open to any ideas.

Can you exlplain what you mean by "Please post back to this forum so
all may benefit". I am new to this group and don't quite yet know the
ins and outs of how it all works.

Regards,
Ruben