Update contents of text field based on calculated dates

[Ruben]

Hi,

I have a continuous 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, all
in one shot.

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

[Tom van Stiphout]

On 19 May 2007 20:14:31 -0700, Ruben <ru*********@gmail.comwrote:

You can do what you're suggesting: see RecordsetClone in the Help
file.
I would rather run an Update query against the underlying table, and
then refresh the form.

-Tom.

>Hi,

I have a continuous 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, all
in one shot.

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

[Ruben]

On May 19, 8:50 pm, Tom van Stiphout <[e-mail]:

On 19 May 2007 20:14:31 -0700, Ruben <[e-mail]:

You can do what you're suggesting: see RecordsetClone in the Help
file.
I would rather run an Update query against the underlying table, and
then refresh the form.

-Tom.

Hi,

I have a continuous 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, all
in one shot.

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- Hide quoted text -

- Show quoted text -

Thanks for your help Tom, this is an option I hadn't even considered
and appears to work quite well! For my own information, if wanted to
achieve the same thing using VB code, what script structure could I
use?

-Ruben

[Tom van Stiphout]

On 19 May 2007 23:35:37 -0700, Ruben <ru*********@gmail.comwrote:

Dim myDate, myDue
with me.Recordsetclone
.MoveFirst
while not .eof

myDate = DateSerial(Year(me.ListDate), Month(Me.ListDate), 1)
myDue = Me.NextServiceDate
If Me.Due < mydate then
!Status="Past Due"
Elseif Me.Due >= mydate then
!Status="Schedule Vendor"
End if

.MoveNext
wend
end with

-Tom.

>On May 19, 8:50 pm, Tom van Stiphout <[e-mail]:

>On 19 May 2007 20:14:31 -0700, Ruben <[e-mail]:

You can do what you're suggesting: see RecordsetClone in the Help
file.
I would rather run an Update query against the underlying table, and
then refresh the form.

-Tom.

>Hi,

>I have a continuous 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, all
in one shot.

>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- Hide quoted text -

- Show quoted text -

Thanks for your help Tom, this is an option I hadn't even considered
and appears to work quite well! For my own information, if wanted to
achieve the same thing using VB code, what script structure could I
use?

-Ruben

[Ruben]

On May 20, 9:54 am, Tom van Stiphout <[e-mail]:

On 19 May 2007 23:35:37 -0700, Ruben <[e-mail]:

Dim myDate, myDue
with me.Recordsetclone
.MoveFirst
while not .eof

myDate = DateSerial(Year(me.ListDate), Month(Me.ListDate), 1)
myDue = Me.NextServiceDate
If Me.Due < mydate then
!Status="Past Due"
Elseif Me.Due >= mydate then
!Status="Schedule Vendor"
End if

.MoveNext
wend
end with

-Tom.

On May 19, 8:50 pm, Tom van Stiphout <[e-mail]:

On 19 May 2007 20:14:31 -0700, Ruben <[e-mail]:

You can do what you're suggesting: see RecordsetClone in the Help
file.
I would rather run an Update query against the underlying table, and
then refresh the form.

-Tom.

Hi,

I have a continuous 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, all
in one shot.

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- Hide quoted text -

- Show quoted text -

Thanks for your help Tom, this is an option I hadn't even considered
and appears to work quite well! For my own information, if wanted to
achieve the same thing using VB code, what script structure could I
use?

-Ruben- Hide quoted text -

- Show quoted text -

Tom,

I could not get the line of code you proposed to work. I corrected
'Me.Due' to 'myDue' and made sure everything else was okay but it
still did not work. Even so, I will follow your advise and use an
Update query instead.

Again, your time, effort and assistance on this matter was very much
appreciated.

Regards,
Ruben