Complex Query and Comparison

[renenger]

I have a scheduling database that my company uses to manage our customers deliveries. We would like to make this database more intelligent by adding some logic and I am not quite sure how to proceed.

On the form frmDelivery, the user inputs all the header information for the delivery, in the subform subfrmLotInfo, they input the houses that will be delivered, the delivery date, and some other miscellaneous information.

When the user inputs the delivery date, the system should count back a number of days based on other criteria and set a build date (when we should start building the cabinets, typically 5 days before the delivery date). Then I need the database to run a query against this date and determine how many boxes we have scheduled to be built already on that day, if it exceed a certain number than I need to it to search an available date close to that date that has not exceeded our production capacity and schedule the lot for that date.

This needs to happen as well if the user changes the delivery date at a later time. Our customers change delivery dates constantly. This will help us schedule our manufacturing plant better and reduce the possiblity of scheduling more than we can actually accomodate.

I have a table tblPrdCapacity that holds how many boxes we can build on a given day. It hold Date, PrdCap (number of boxes). In the subfrmLotInfo, the field BoxesBuilt tells how many boxes will be built for that Lot.

Any info would be greatly appreciated!! I have been tasked to accomplish this as soon as possible.

Thank you!!!

 

[FoFa]

Logically it would follow something like this: (IMO)
{Event after delivery date entered}
' BuildAdj is the number days back you need to schedule the build (minimum)
Me.BuildDate = CalcbuildDate(Me.DeliverDate, BuildAdj)

Function CalcBuildDate(DlvryDate as date, buildAdj as integer) as Date
dim WorkDate as date
CalcBuildDate = NULL
While isnull(CalcBuildDate)
WorkDate = DateAdd("D",BuildAdj,DlvryDate) ' get first shot at build date
If WorkDate {is not a working day, like Sat or Sun} then
BuildAdj = BuildAdj - 1
goto LoopIt
End If
{get number of boxes building on WorkDate}
{get number can build in a day}
If #building < #CanBuild then
CalcBuildDate = WorkDate
Else
BuildAdj = BuildAdj - 1
end if
LoopIt:
Loop
End Function

 

[renenger]

Complex Query and Comaprison

Ok. I created a qry called qrySchedulingPrdCapacity. Right now this pulls everything that has a Work Order Start Date (WOSD), Number of Boxes Planned (SumofBoxesBuilt), and Production Capacity for that date (PrdCap).

I have some other requirements as well. If the Lot Delivery Date (LotDelDate) gets changed after it was input, if the STATUS is IN MILL, then it should not change the WOSD. Otherwise, it should adjust the work order start date back so many days, not to include weekends. Also, we determine number of days back like this: If it is a special color (SC), then we need to go back 6 days, if it has a Raised Panel door it has to go back 5 days, and if it is a regular order than we go back 4 days.

After it determines the WOSD, it should look at the qrySchedulingPrdCapacity and see if we are over the limit for that day. If so, it needs to move to the next available day.

 

[renenger]

Here is some code that I got started.

Private Sub Del__Date_AfterUpdate()
TempDate = Weekday(txtDuedateNormal, vbSunday)

If Forms!frmDelivery.Form.[SpecialColor] = Yes Then
Me.WOSD = Me.LotDelDate - 6
Else
If Me.DoorStyle = "AR-756" Then
Me.WOSD = Me.LotDelDate - 5
Else
Me.WOSD = Me.LotDelDate - 4

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

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

End If
End If
End Sub

However, if the main form status field (frmDelivery) is IN MILL, I don't want the WOSD to update. Also, the first line of my code Forms!frmDelivery.Form.SpecialColor = Yes does not work. It doesn't subtract 6 days from my date.

Can anyone give me a hand?