function to change next action date after selection from combobox

[megatronixs]

Hi all,

I found a function to put a next action date into a date field. If in the combobox a status was changed to for example "on client side), then it will know that it has to count 60 days, but not counting the weekends.
The combobox is in a form called "frm_main_hrr" called cmb_status.
for now it will call a intput box and then the results will be put in a message box.

How can I adjust it to that when I make a selection in the combobox from status it will put the date in the field "next_action_date"

Option Compare Database
Function BusinessDays(stDay As Date, eDay As Integer) As Date
 Dim i As Integer
 Do While i < eDay
 If isWeekend(stDay) = True Then
 i = i + 0
 ElseIf isExclude(stDay) = True Then
 i = i + 0
 Else
 i = i + 1
 End If
 stDay = stDay + 1
 Loop
If isWeekend(stDay) = True Or isExclude(stDay) = True Then
 BusinessDays = BusinessDays(stDay, 1)
 Else
 BusinessDays = stDay
 End If
 End Function
Function isExclude(testDate As Date) As Boolean
 Dim excludeDates(1 To 11) As Date
 Dim intyear As Integer
 intyear = Format(testDate, "YYYY")
 Dim i As Integer
 'Holiday List
 '''''''''''''''''''''''''''''''''''''''''''''
 excludeDates(1) = CDate("1/1/" & intyear) 'New Years Day
 excludeDates(2) = CDate("1/20/" & intyear) '#1/20/2014# 'MLK Jr. Day
 excludeDates(3) = CDate("2/17/" & intyear) '#2/17/2014# 'Presidents' Day
 excludeDates(4) = CDate("5/26/" & intyear) '#5/26/2014# 'Memorial Day
 excludeDates(5) = CDate("7/4/" & intyear) '#7/4/2014# 'Independence Day
 excludeDates(6) = CDate("9/16/" & intyear) '#9/1/2014# 'Labor Day
 excludeDates(7) = CDate("10/13/" & intyear) '#10/13/2014# 'Columbus Day
 excludeDates(8) = CDate("11/1/" & intyear) '#11/11/2014# 'Veterans Day
 excludeDates(9) = CDate("11/27/" & intyear) '#11/27/2014# 'Thanksgiving
 excludeDates(10) = CDate("12/25/" & intyear) '#12/25/2014# 'Christmas
'Missing Government Date – Add Inauguration Day:
 'First January 20 following a Presidential election
For i = 1 To 10
 If testDate = excludeDates(i) Then
 isExclude = True
 Exit Function
 End If
 Next i
 isExclude = False
End Function
Function isWeekend(testDate As Date) As Boolean
 Select Case Weekday(testDate)
 Case vbSaturday, vbSunday
 isWeekend = True
 Case Else
 isWeekend = False
 End Select
 End Function
Function whatDay()
    Dim getDate As Date, getBusDays As Integer
    getDate = InputBox("Start Date")
    getBusDays = InputBox("Business Days")
 
    MsgBox BusinessDays(getDate, getBusDays)
End Function

code I have now on the combobox:
I hard coded the 60 days in it and the TodayDate as today's date.

Private Sub cmb_status_AfterUpdate()
Dim TodayDate As Date
Dim BusinessDays As Integer
TodayDate = Date
BusinessDayss = "60"
    getDate = TodayDate
    getBusDayss = BusinessDays
Me.Next_Action_Date = (BusinessDays(getDate, getBusDays))
End Sub

Greetings.

 

[Minty]

You need to store the number of days against each action. Then bring that into your combo box as a hidden column.

You can now refer to that number and pass it to your function. Assuming your combo was called cmbAction and that the actiondays were in the 3rd column you would refer to it using

Me.cmbAction.Column(2) as column numbering starts at (0)

 

[megatronixs]

Hi Minty,

I made it more or less work, but the days are not fine. The message box gives me a different date then the outcome in the date field for the next action date.
I believe it is a mess in the code.
Can you please have a look at the attached example database and let me know where I went wrong?

Greetings.

 

[megatronixs]

Hi,
Did you have a look at it?

Greetings.

 

[megatronixs]

I managed to solve it with a Select Case statement.
I need to hard code it, but it works nice.

Greetings.

 

[Minty]

I didn't see your original reply - sorry.

You don't appear to have bought in the status days to your combo as a hidden column, so you wouldn't be able to refer to it.

Maybe post up what you have done and we can take a look?

 

[megatronixs]

hi, I'm at home now, I will post what I did tomorrow morning.

Greetings.

 

[megatronixs]

Hi,

I solved it like this:

Function whatDay()
    Dim getDate As Date, getBusDays As Integer, NumberThis As String
    Dim Busy As String
  
    getDate = Date
   
    NumberThis = Me.cmb_status.Column(0)
    
    Select Case NumberThis
    Case "status 1"
    Busy = 1
    Case "status 2"
    Busy = 1
        Case "status 3"
    Busy = 10
        Case "status 4"
    Busy = 2
        Case "status 5"
    Busy = 28
        Case "status 6"
    Busy = 3
        Case "status 7"
    Busy = 3
        Case "status 8"
    Busy = 3
        Case "status 9"
    Busy = 30
            Case "status 10"
    Busy = 30
            Case "status 11"
    Busy = 5
            Case "status 12"
    Busy = 5
            Case "status 13"
    Busy = 5
            Case "status 14"
    Busy = 5
            Case "status 15"
    Busy = 5
            Case "status 16"
    Busy = 60
    End Select
    
    getBusDays = Busy
 
    DasDate = BusinessDays(getDate, getBusDays)
    Me.Next_Action_Date = DasDate
End Function

Greetings.

 

[Minty]

Yes - it the very hard way to do it. If you combobox had the busy days in it as the next column your entire code would simply be ;

 If Not IsNull(Me.cmb_status) Then
 
    DasDate = BusinessDays(Date(), Me.cmb_status.Column(1))
    Me.Next_Action_Date = DasDate

 End if

And would not need changing if any of your values change, or you add extra status codes.

 

[megatronixs]

hi,
I will try this one out, I missed it maybe because I was only having the last part in the line:

Me.cmb_status.Column(1))

Greetings.