Overdue action to change text

D

danlavish

I am creating a quality database for a company but i am having trouble
with vb. I need to add code to a text box on a form to automaticlly
calculate 5 days from the issue date and insert that date into my
overdue field. But after the five days i need it to change to text
overdue. I can also use a checkbox to automaticlly check after 5 days
(- weekends and holidays) any help would be great!
 
K

Ken Snell \(MVP\)

I assume that you are not storing the "5-days-overdue" value, but that you
just want to show that value on the form.

You can use two events to make this happen. The code in both events will be
essentially the same, but both events are needed because you want to show
the information when you're entering a new record or when you come back to
an old record.

Note: change my generic names to the real names.

First, use the first textbox's AfterUpdate event to show the value when you
first enter the issue date:

Private Sub NameOfIssueDateTextbox_AfterUpdate()
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
Me.NameOf5DayLateTextbox.Value = DateAdd("d", _
5, Me.NameOfIssueDateTextbox.Value)
Else
Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
End If
Else
Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
End Sub


Then, use the form's Current event to show the value when you already have
an issue date:

Private Sub Form_Current()
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
Me.NameOf5DayLateTextbox.Value = DateAdd("d", _
5, Me.NameOfIssueDateTextbox.Value)
Else
Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
End If
Else
Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
End Sub
 
A

Arvin Meyer [MVP]

First you will need an unbound textbox, and some code from my website:

http://www.datastrat.com/Code/GetBusinessDay.txt

Follow the instructions to build the holiday table and add the holidays. The
weekends will be taken care of by the code. Now assuming that your starting
date field is named EntryDate and your textboxes are named txtEntryDate and
txtDueDate, in the AfterUpdate event of txtEntryDate, add some code similar
to:

Private Sub txtEntryDate_AfterUpdate()

If Date >= GetBusinessDay(Me.txtEntryDate, 6) Then
Me.txtDueDate = "OVERDUE"
Else
Me.txtDueDate = GetBusinessDay(Me.txtEntryDate, 5)
End If

End Sub
 
K

Ken Snell \(MVP\)

I neglected to include the extra option of showing OVERDUE instead of the
date in my code:

Private Sub NameOfIssueDateTextbox_AfterUpdate()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
datDue = DateAdd("d", _
5, Me.NameOfIssueDateTextbox.Value)
If Date() > datDue Then
Me.NameOf5DayLateTextbox.Value = "OVERDUE"
Else
Me.NameOf5DayLateTextbox.Value = datDue
End If
Else
Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
End If
Else
Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
End Sub




Private Sub Form_Current()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
datDue = DateAdd("d", _
5, Me.NameOfIssueDateTextbox.Value)
If Date() > datDue Then
Me.NameOf5DayLateTextbox.Value = "OVERDUE"
Else
Me.NameOf5DayLateTextbox.Value = datDue
End If
Else
Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
End If
Else
Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
End Sub
 
V

vb_Dumb

I neglected to include the extra option of showing OVERDUE instead of the
date in my code:

Private Sub NameOfIssueDateTextbox_AfterUpdate()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
    If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
        datDue = DateAdd("d", _
            5, Me.NameOfIssueDateTextbox.Value)
        If Date() > datDue Then
            Me.NameOf5DayLateTextbox.Value = "OVERDUE"
        Else
            Me.NameOf5DayLateTextbox.Value = datDue
        End If
    Else
        Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
    End If
Else
    Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
End Sub

Private Sub Form_Current()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
    If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
        datDue = DateAdd("d", _
            5, Me.NameOfIssueDateTextbox.Value)
        If Date() > datDue Then
            Me.NameOf5DayLateTextbox.Value = "OVERDUE"
        Else
            Me.NameOf5DayLateTextbox.Value = datDue
        End If
    Else
        Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
    End If
Else
    Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
End Sub

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/












- Show quoted text -
Click to expand...

hey thanks it works but is there an easy way to skip holidays and
weekends and my boss wonders if the background color can easily change
to rblue when its overdue.. thanks for the help
 
K

Ken Snell \(MVP\)

An "easy" way to skip weekends and holidays? Not without setting up a table,
similar to what Arvin Meyer suggested in his reply, and using that table to
calculate the "5-day" window. See if Arvin's solution will work for you;
post back if not.

As for changing the background color, change the codes to this:

Private Sub NameOfIssueDateTextbox_AfterUpdate()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
datDue = DateAdd("d", _
5, Me.NameOfIssueDateTextbox.Value)
If Date() > datDue Then
Me.NameOf5DayLateTextbox.Value = "OVERDUE"
Else
Me.NameOf5DayLateTextbox.Value = datDue
End If
Else
Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
End If
Else
Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
If Me.NameOf5DayLateTexbox.Value = "OVERDUE" Then _
Me.NameOf5DayLateTexbox.BackColor = vbBlue
Else
Me.NameOf5DayLateTexbox.BackColor = vbWhite
End If
End Sub




Private Sub Form_Current()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
datDue = DateAdd("d", _
5, Me.NameOfIssueDateTextbox.Value)
If Date() > datDue Then
Me.NameOf5DayLateTextbox.Value = "OVERDUE"
Else
Me.NameOf5DayLateTextbox.Value = datDue
End If
Else
Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
End If
Else
Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
If Me.NameOf5DayLateTexbox.Value = "OVERDUE" Then _
Me.NameOf5DayLateTexbox.BackColor = vbBlue
Else
Me.NameOf5DayLateTexbox.BackColor = vbWhite
End If
End Sub
 
V

vb_Dumb

An "easy" way to skip weekends and holidays? Not without setting up a table,
similar to what Arvin Meyer suggested in his reply, and using that table to
calculate the "5-day" window. See if Arvin's solution will work for you;
post back if not.

As for changing the background color, change the codes to this:

Private Sub NameOfIssueDateTextbox_AfterUpdate()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
    If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
        datDue = DateAdd("d", _
            5, Me.NameOfIssueDateTextbox.Value)
        If Date() > datDue Then
            Me.NameOf5DayLateTextbox.Value = "OVERDUE"
        Else
            Me.NameOf5DayLateTextbox.Value = datDue
        End If
    Else
        Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
    End If
Else
    Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
If  Me.NameOf5DayLateTexbox.Value = "OVERDUE" Then _
    Me.NameOf5DayLateTexbox.BackColor = vbBlue
Else
    Me.NameOf5DayLateTexbox.BackColor = vbWhite
End If
End Sub

Private Sub Form_Current()
Dim datDue As Date
If Len(Me.NameOfIssueDateTextbox.Value & "") > 0 Then
    If IsDate(Me.NameOfIssueDateTextbox.Value) = True Then
        datDue = DateAdd("d", _
            5, Me.NameOfIssueDateTextbox.Value)
        If Date() > datDue Then
            Me.NameOf5DayLateTextbox.Value = "OVERDUE"
        Else
            Me.NameOf5DayLateTextbox.Value = datDue
        End If
    Else
        Me.NameOf5DayLateTextbox.Value = "Invalid Issue Date"
    End If
Else
    Me.NameOf5DayLateTextbox.Value = "No Issue Date"
End If
If  Me.NameOf5DayLateTexbox.Value = "OVERDUE" Then _
    Me.NameOf5DayLateTexbox.BackColor = vbBlue
Else
    Me.NameOf5DayLateTexbox.BackColor = vbWhite
End If
End Sub
Click to expand...

Well I tried his code and it stops right at the beginning yours works
great would it be easier to just take out weekends if you could do
that i would give you all my fake poker money! Thanks for being
patient with a noob
 
A

Arvin Meyer [MVP]

Well I tried his code and it stops right at the beginning yours works
great would it be easier to just take out weekends if you could do
that i would give you all my fake poker money! Thanks for being
patient with a noob
 
V

vb_Dumb

Well I tried his code and it stops right at the beginning yours works
great would it be easier to just take out weekends if you could do
that i would give you all my fake poker money! Thanks for being
patient with a noob
--------------------------------------------------------
Did you download the GetBusinessDay code and put it in a Standard module?
Did you build tblHoliday? Without doing both, the code I posted in the
newsgroup will not compile and will stop on the first line. I know the code
works. It has been for more than 10 years now.
Click to expand...

Yes i put the code into a module named GetBusinessDay and made the
table for some reason its not working i dont know what i am doing
wrong now it stops on the afterupdate it says expected variable or
procedure not module heres the code


Private Sub Date_Rejected_AfterUpdate()


If Date >= GetBusinessDay(Me.Date_rejected, 6) Then
Me.Action_Overdue = "OVERDUE"
Me.Action_Overdue.BackColor = 16711680
Else
Me.Action_Overdue = GetBusinessDay(Me.Date_rejected, 5)
Me.Action_Overdue.BackColor = 16777215
End If


End Sub
 
C

Carl Rapson

The module shouldn't have the same name as the function it contains. Try
renaming the module to something else (don't rename the function, though)
and see if that solves the problem.

Carl Rapson

Well I tried his code and it stops right at the beginning yours works
great would it be easier to just take out weekends if you could do
that i would give you all my fake poker money! Thanks for being
patient with a noob
--------------------------------------------------------
Did you download the GetBusinessDay code and put it in a Standard module?
Did you build tblHoliday? Without doing both, the code I posted in the
newsgroup will not compile and will stop on the first line. I know the
code
works. It has been for more than 10 years now.
Click to expand...

Yes i put the code into a module named GetBusinessDay and made the
table for some reason its not working i dont know what i am doing
wrong now it stops on the afterupdate it says expected variable or
procedure not module heres the code


Private Sub Date_Rejected_AfterUpdate()


If Date >= GetBusinessDay(Me.Date_rejected, 6) Then
Me.Action_Overdue = "OVERDUE"
Me.Action_Overdue.BackColor = 16711680
Else
Me.Action_Overdue = GetBusinessDay(Me.Date_rejected, 5)
Me.Action_Overdue.BackColor = 16777215
End If


End Sub
 
V

vb_Dumb

The module shouldn't have the same name as the function it contains. Try
renaming the module to something else (don't rename the function, though)
and see if that solves the problem.

Carl Rapson
Click to expand...
Well i Changed the name of the module and now it stops on

Dim rst As DAO.Recordset
it says user defined type not defined...
any ideas?
 
D

Douglas J. Steele

While in the VB Editor, check Tools | References. Make sure that Microsoft
DAO 3.6 Object Library is one of the selected references at the top of the
list. If it isn't, scroll through the list of available references until you
find it and select it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The module shouldn't have the same name as the function it contains. Try
renaming the module to something else (don't rename the function, though)
and see if that solves the problem.

Carl Rapson
Click to expand...
Well i Changed the name of the module and now it stops on

Dim rst As DAO.Recordset
it says user defined type not defined...
any ideas?
 
V

vb_Dumb

While in the VB Editor, check Tools | References. Make sure that Microsoft
DAO 3.6 Object Library is one of the selected references at the top of the
list. If it isn't, scroll through the list of available references until you
find it and select it.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)





Well i Changed the name of the module and now it stops on

Dim rst As DAO.Recordset
it says user defined type not defined...
any ideas?
Click to expand...

That did it thank you so much you made me happy because i made the
boss happy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Excel due and overdue date colours 2
Update field daily 3
Continuous form problem 1
DataMacro problem after Splitting Access Database 2010 0
Nested IIF problem 2
Overdue item Report 4
Calculate days overdue for deliveries? 1
conversion of SQL in to VB. (access 2k) 1

Top