Looping through subform

[renenger]

I have a form with an UPDATE button. There is a subfrm on this form that lists all my units. I want the update button to loop through all the records on the subfrm and update the WOSD based on the information below. Is this possible?

Private Sub cmdUpdateWOSD_Click()

Call UpdateWOSDChange

End Sub


Private Sub UpdateWOSDChange()

Dim AddColor As Boolean
Dim intNumDays As Integer

Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.PreFin

Case "BR17", "BR28", "WH06"
AddColor = True
Case Else
AddColor = False
End Select

Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.DrStyle

Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If

Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 4
End If
End Select

Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.WOSD = MinusWorkdays(Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.Deldate, intNumDays)


End Sub

 

[renenger]

Subform looping

Thanks Pat. I will search for the Recordset clone posts. I am storing the WOSD and WODD in a temp table that will be over written. I have a report that I need to be able to run that summarizes the number of units and boxes per WOSD. That let's us know how booked our manufacturing plant is.

What I need to happen is everytime a delivery report or this manufacturing report is run, I need the WOSD and DD to be calculated for this table and then detailed and summarized in the reports.

 

[renenger]

Query and Function

Ok. I have reviewed what you posted and some other posts on this forum and realized that the best way to do this is to call a function in a query to calculate the WOSD. However, I am not having much luck with this. The WOSD is coming back as 12:00 am or 12/30/99.

Can someone review this code and let me know what is wrong with it?

Public Function CreateWOSDDate(ByVal Prefin As String, ByVal DrStyle As String, ByVal DelDate As Date) As Date

Dim AddColor As Boolean
Dim intNumDays As Integer
Dim WOSD As Date

Select Case Prefin

Case "BR17", "BR28", "WH06"
AddColor = True
Case Else
AddColor = False
End Select

Select Case DrStyle

Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If

Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 4
End If
End Select

WOSD = MinusWorkdays(DelDate, intNumDays)


End Function



SELECT tblFrontierUnits.Deldate, tblMainFrontierUnits.ProjectID, tblMainFrontierUnits.Phase, tblMainFrontierUnits.Unit, tblMainFrontierUnits.Tract, tblMainFrontierUnits.Release, tblMainFrontierUnits.UnitPlan, tblFrontierUnits.UnitOpt, Sum(tblFrontierUnits.Boxes) AS SumOfBoxes, tblFrontierUnits.Species, tblFrontierUnits.DrStyle, tblFrontierUnits.PreFin, CreateWOSDDate([Prefin],[DrStyle],[DelDate]) AS WOSD, tblFrontierUnits.WODD, Format([WOSD],"mm/dd/yy") AS WOSDDate
FROM tblMainFrontierUnits LEFT JOIN tblFrontierUnits ON (tblMainFrontierUnits.UnitPlan = tblFrontierUnits.UnitPlan) AND (tblMainFrontierUnits.Release = tblFrontierUnits.Release) AND (tblMainFrontierUnits.Tract = tblFrontierUnits.Tract) AND (tblMainFrontierUnits.Unit = tblFrontierUnits.Unit) AND (tblMainFrontierUnits.Phase = tblFrontierUnits.Phase) AND (tblMainFrontierUnits.ProjectID = tblFrontierUnits.ProjectID)
GROUP BY tblFrontierUnits.Deldate, tblMainFrontierUnits.ProjectID, tblMainFrontierUnits.Phase, tblMainFrontierUnits.Unit, tblMainFrontierUnits.Tract, tblMainFrontierUnits.Release, tblMainFrontierUnits.UnitPlan, tblFrontierUnits.UnitOpt, tblFrontierUnits.Species, tblFrontierUnits.DrStyle, tblFrontierUnits.PreFin, CreateWOSDDate([Prefin],[DrStyle],[DelDate]), tblFrontierUnits.WODD, Format([WOSD],"mm/dd/yy")
HAVING (((tblFrontierUnits.Deldate) Between [Enter Starting Date:] And [Enter Ending Date:]));