If Then Code

R

renenger

Registered User.
Local time
Today, 10:07
Joined
Oct 25, 2002
Messages
117
In the code below, I am trying to autofill a field called WOSD (Work Order Start Date). It works for the Door Types or just the -4 option.

However, if the SpecialColor checkbox is checked, it needs to subtract 6 days from the LotDelDate. I must have something typed wrong.

I have been trying to get it to skip weekends as well with no luck.

Can anyone give me a hand?

Private Sub LotDelDate_AfterUpdate()

If Me.DoorStyle = "Eagle" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "H/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "F/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-22" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-23" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
Me.WOSD = Me.LotDelDate - 6
Else
Me.WOSD = Me.LotDelDate - 4

End If
End If
End If
End If
End If
End If
End If
End If

End Sub
 
Last edited:
DateAdd(interval, number, date)
Me.WOSD = DateAdd("d", -5, Me.LotDelDate)
 
Ok. This works on the lots where the delivery date (LOTDELDATE) goes back 4 days but it isn't skipping weekends on the ones that have to be subtracted 5 days.

For example,

Lot 53 has a regular door and should start being built 4 work days prior to actual delivery date of 9/15. The work order start date should be 9/9. This works.

Lot 54 has an Eagle door and should start being built 5 days prior to actual delivery date of 9/15. The work order start date should be 9/8. This is not working. It goes to 9/10.

Private Sub LotDelDate_AfterUpdate()
Dim intDays As Integer
Dim TempDate As String

If Me.DoorStyle = "Eagle" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "H/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "F/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-22" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-23" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
Me.WOSD = Me.LotDelDate - 6
Else
Me.WOSD = Me.LotDelDate - 4

TempDate = Weekday(WOSD, vbSunday)

If TempDate = vbSaturday Then
Me.WOSD = DateAdd("d", -2, WOSD)
End If

If TempDate = vbSunday Then
Me.WOSD = DateAdd("d", -2, WOSD)
End If

End If
End If
End If
End If
End If
End If
End If
End If

End Sub
 
It looks to me like "Special Doors" take 5 days unless they are a "Special Color" and then it takes 6 days. "Normal" doors take 4 days unless they are a "Special Color" and then they take 6 days. Correct what I said above and we will have the code.

What is Me.DoorStyle equal to when it is a "Normal" door? After you make the corrections we will fix the weekend problem also.
 
Yes, that's exactly it. The doorstyle is actuall a combo box with a list a choices. The ones listed specifically take longer to build the other values are our generic doors. Some examples are CC-21, CC-21, CC-1, Shaker, CC-23, etc.. There are a few more choices.

One other thing I need to incorporate is if the Status of the job which is located on the same form as special color, is "In Layout", "Ready for Production" or "In Mill" then we need to recalculate the work order start date everytime the delivery date is changed. If the status is anything other than those, we do not want the work order start date to change regardless of whether or not the delivery date is changed.

This is because those cabinets are already being built at that point and it doesn't matter if the delivery date changes or not, the start date won't change.

I really appreciate any help you can give me. They want this done ASAP and it's a little more complicated than I am used to. We really need this information.

Thanks again!!
 
This code should take care of the original problem:
Code: 
Private Sub LotDelDate_AfterUpdate()
Dim AddColor As Boolean

If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
   AddColor = True
Else
   AddColor = False
End If

Select Case Me.DoorStyle

Case "Eagle", "RP-9", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
   If AddColor Then
      Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
   Else
      Me.WOSD = DateAdd("d", -5, Me.LotDelDate)
   End If

Case Else
   If AddColor Then
      Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
   Else
      Me.WOSD = DateAdd("d", -4, Me.LotDelDate)
   End If

End Select

If Weekday(WOSD) = vbSunday Or Weekday(WOSD) = vbSaturday Then
   '-- It's the weekend - subtract 2 more days
   Me.WOSD = DateAdd("d", -2, WOSD)
End If

End Sub
I'm still thinking about the "In Layout" problem.
 
The final product:
Code: 
Private Sub LotDelDate_AfterUpdate()
Dim AddColor As Boolean

If Forms!FRMDELIVERY.Form.Status = "In Layout" Or _
   Forms!FRMDELIVERY.Form.Status = "Ready for Production" Or _
   Forms!FRMDELIVERY.Form.Status = "In Mill" Then
[COLOR=Red]'   '-- The process has started, too late to alter the Start Date!
'   Exit Sub
'Else
'-- I had my logic backwards  :eek: [/COLOR]
   If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
      AddColor = True
   Else
      AddColor = False
   End If
   
   Select Case Me.DoorStyle
   
   Case "Eagle", "RP-9", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
      If AddColor Then
         Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
      Else
         Me.WOSD = DateAdd("d", -5, Me.LotDelDate)
      End If
   
   Case Else
      If AddColor Then
         Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
      Else
         Me.WOSD = DateAdd("d", -4, Me.LotDelDate)
      End If
   End Select

   If Weekday(WOSD) = vbSunday Or Weekday(WOSD) = vbSaturday Then
      '-- It's the weekend - subtract 2 more days
      Me.WOSD = DateAdd("d", -2, WOSD)
   End If
End If

End Sub
This code assumes that at one point it was *not* too late to alter the Start Date.
 
Last edited:
Reneger,
I have an alternative you might want to look at. You can use the following public function to subtract x number of days from a date that excludes weekends and holidays.

Code: 
Public Function MinusWorkdays(dteStart As Date, intnNumDays As Integer) As Date
Dim dteCurrDate As Date
Dim i As Integer

dteCurrDate = dteStart
MinusWorkdays = dteStart
i = 1
Do While i < intNumDays
     If Weekday(dteCurrDate, vbMonday) <= 5 AND IsNull(DLookup("[Holiday]","tblHolidays", [HolDate] = #" & dteCurrDate & "#") Then
          i = i + 1
     End If
     dteCurrDate = dteCurrDate - 1
Loop
MinusWorkdays = dteCurrDate
Exit_MinusWorkDays:
End Function

I would then modify Rural's code as follows:

Code: 
Private Sub LotDelDate_AfterUpdate()
Dim AddColor As Boolean
[COLOR=Red]Dim intNumDays As Integer[/COLOR]

If Forms!FRMDELIVERY.Form.Status = "In Layout" Or _
   Forms!FRMDELIVERY.Form.Status = "Ready for Production" Or _
   Forms!FRMDELIVERY.Form.Status = "In Mill" Then
'   '-- The process has started, too late to alter the Start Date!
'   Exit Sub
'Else
'-- I had my logic backwards  :eek: 
   If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
      AddColor = True
   Else
      AddColor = False
   End If
   
   Select Case Me.DoorStyle
   
   Case "Eagle", "RP-9", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
      If AddColor Then
         [COLOR=Red]IntNumDays= 6[/COLOR]
      Else
         [COLOR=Red]intNumDays= 5[/COLOR]
      End If
   
   Case Else
      If AddColor Then
         [COLOR=Red]IntNumDays= 6[/COLOR]
      Else
         [COLOR=Red]IntNumDays= 4[/COLOR]
      End If
   End Select

[COLOR=Red]Me.WOSD = MinusWorkDays(Me.LotDelDate, intNumDays)[/COLOR]
End If

End Sub
 
Thank you all so much. The only issue I am having is it's not recoginizing Special Color being selected. It's a checkbox. Do I need to requery? You all have helped me out a great deal!!! I don't know how to thank you!!

Private Sub LotDelDate_AfterUpdate()
Dim AddColor As Boolean

If Forms!FRMDELIVERY.Form.Status = "In MILL" Or _
Forms!FRMDELIVERY.Form.Status = "Sander" Or _
Forms!FRMDELIVERY.Form.Status = "UV" Or _
Forms!FRMDELIVERY.Form.Status = "In Paint" Or _
Forms!FRMDELIVERY.Form.Status = "In Bank" Or _
Forms!FRMDELIVERY.Form.Status = "On Floor" Or _
Forms!FRMDELIVERY.Form.Status = "In Assembly" Then
' '-- The process has started, too late to alter the Start Date!
Exit Sub
Else
'-- I had my logic backwards :eek:
If Forms!FRMDELIVERY.Form.SpecialColor = YES Then
AddColor = True
Else
AddColor = False
End If

Select Case Me.DoorStyle

Case "Eagle", "RP-9", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
If AddColor Then
Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
Else
Me.WOSD = DateAdd("d", -5, Me.LotDelDate)
End If

Case Else
If AddColor Then
Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
Else
Me.WOSD = DateAdd("d", -4, Me.LotDelDate)
End If
End Select

If Weekday(WOSD) = vbSunday Or Weekday(WOSD) = vbSaturday Then
'-- It's the weekend - subtract 2 more days
Me.WOSD = DateAdd("d", -2, WOSD)
End If
End If


End Sub
 
You can just change this:
Code: 
If Forms!FRMDELIVERY.Form.SpecialColor = YES Then
AddColor = True
Else
AddColor = False
End If
To:
Code: 
AddColor = Forms!FRMDELIVERY.Form.SpecialColor
 
Ok. I still can't get it to recognize the special color field. So I changed the input field to a combo box. I tried to add a Select Case statement to address special color.

This is the only thing that doesn't work. This is the original input form. I have another form that is identical that is for updating. It's called subfrmEditLotInfo on the frmSpecificDelivery. The only difference is when you enter the LotDelDate field is checks to see if there is a date already there. If there is then it asks you if you are sure you want to change the date, if so then it takes you to a date change form to enter the new date and then updates the LotDelDate field with the new date and transfers focus to the Shipped field. So I entered this code in the LotDelDate_Exit event.

Private Sub LotDelDate_AfterUpdate()
Dim AddColor As Boolean

If Forms!FRMDELIVERY.Form.Status = "In MILL" Or _
Forms!FRMDELIVERY.Form.Status = "Sander" Or _
Forms!FRMDELIVERY.Form.Status = "UV" Or _
Forms!FRMDELIVERY.Form.Status = "In Paint" Or _
Forms!FRMDELIVERY.Form.Status = "In Bank" Or _
Forms!FRMDELIVERY.Form.Status = "On Floor" Or _
Forms!FRMDELIVERY.Form.Status = "In Assembly" Then
' '-- The process has started, too late to alter the Start Date!
Exit Sub
Else
Select Case Me.PreFin

Case "010", "025", "027", "029", "030", "BR06", "BR11", "BR17", "BR28", "WH06", "WH17"
AddColor = True
Case Else
AddColor = False

Select Case Me.DoorStyle

Case "Eagle", "RP-9", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
If AddColor Then
Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
Else
Me.WOSD = DateAdd("d", -5, Me.LotDelDate)
End If

Case Else
If AddColor Then
Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
Else
Me.WOSD = DateAdd("d", -4, Me.LotDelDate)
End If
End Select

If Weekday(WOSD) = vbSunday Or Weekday(WOSD) = vbSaturday Then
'-- It's the weekend - subtract 2 more days
Me.WOSD = DateAdd("d", -2, WOSD)
End If

End Select
End If

End Sub
 
You are a moving target and thrashing around for a solution. We need to stay on one problem until it is solved. I vote we go back to my finished solution and fix the last problem. What do you say?
 
Ok. I was just trying to figure out a workaround on the special color on my own. I am changing the code back to your final product. The only issue on that form is it still does not recognize special color.

Thanks so much for your help.
 
I didn't mean to discourage you from trying solutions on your own, quite the opposite. It is just that from here the problem kept moving and that made it difficult to solve.

I've modified my solution and put in some diagnostig code:
Code: 
Private Sub LotDelDate_AfterUpdate()
Dim AddColor As Boolean

If Forms!FRMDELIVERY.Form.Status = "In Layout" Or _
   Forms!FRMDELIVERY.Form.Status = "Ready for Production" Or _
   Forms!FRMDELIVERY.Form.Status = "In Mill" Then
[COLOR=Red]   '-- We can modify the Start Date
   AddColor = Not (Forms!FRMDELIVERY.Form.SpecialColor = 0)

'-- Test code
MsgBox "AddColor = " & AddColor[/COLOR]
   
   Select Case Me.DoorStyle
   
   Case "Eagle", "RP-9", "H/E", "F/E", "RP-9", "RP-22", "RP-23"
      If AddColor Then
         Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
      Else
         Me.WOSD = DateAdd("d", -5, Me.LotDelDate)
      End If
   
   Case Else
      If AddColor Then
         Me.WOSD = DateAdd("d", -6, Me.LotDelDate)
      Else
         Me.WOSD = DateAdd("d", -4, Me.LotDelDate)
      End If
   End Select

   If Weekday(WOSD) = vbSunday Or Weekday(WOSD) = vbSaturday Then
      '-- It's the weekend - subtract 2 more days
      Me.WOSD = DateAdd("d", -2, WOSD)
   End If
End If

End Sub
Let's see what that does!
 
The message box comes up as AddColor = True. I put in a date of 9/15 so the start date should be 9/7.
 
Toward the end of the code change the following:
Code: 
If Weekday(WOSD) = vbSunday Or Weekday(WOSD) = vbSaturday Then
  '-- It's the weekend - subtract 2 more days
  Me.WOSD = DateAdd("d", -2, WOSD)
End If
To:
Code: 
If Weekday(WOSD, vbMonday) > 5 Then
  '-- It's the weekend - subtract 2 more days
  Me.WOSD = DateAdd("d", -2, WOSD)
Else
  MsgBox WOSD & " is not on a weekend!"
End If
 
The message box pops up with 9/9 is not a weekend. If it is a special color, and the Delivery Date is 9/15 it should go back 6 days not counting weekends if they occur. So 6 working days from there should be 9/7.
 
You must log in or register to reply here.

Similar threads

T
Solved Filter form after update with code
Replies
9
Views
490
arnelgp
arnelgp
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
223
Mike Krailo
Mike Krailo
B
Set a pie chart with values from a listbox access vba
Replies
3
Views
270
b24 sofwtares
B
B
  • Question Question
Loop without Do stumped
Replies
6
Views
200
MajP
MajP
A
Filter Records
Replies
1
Views
228
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom