Here is the code:
Private Sub cmdExit_Click()
Unload Me
frmMain.Show
End Sub
Private Sub cmdGenerate_Click()
Dim rs As New Recordset
Dim rsstore As New Recordset
Dim rslot As New Recordset
Dim rslotid As New Recordset
Dim rssales As New Recordset
Dim lotno As String
Dim amt As Integer
Dim siteID As String
Screen.MousePointer = vbHourglass
'PBarSplitData.Value = 1
startdate = (DateDiff("d", "1/1/1753", DTStartDate) + 639906) 'convert month start to julian
enddate = (DateDiff("d", "1/1/1753", DTEndDate) + 639906) 'convert month end to julian date
startdt = Format(DTStartDate, "yyyy-mm-dd"

'find fiscal month start date
enddt = Format(DTEndDate, "yyyy-mm-dd"

'find fiscal month end date
mnth = Month(DTStartDate) 'find month number for the fiscal month
cmd.ActiveConnection = cnnFO
cmd.CommandText = "SELECT SplitCategoryName FROM plt_SplitDataCategory WHERE Active = 1"
rs.Open cmd
'checks there are categories set up to work with
If rs.EOF Or rs.BOF Then
MsgBox "There are no categories set up to generate the Split Data!", vbExclamation
Exit Sub
End If
rs.Close
'clear out TEMP table so only working with data we need for this months calculations
cmd.CommandText = "DELETE FROM plt_SplitDataTEMP"
cmd.Execute
'check to see the month number doesn't already exist in the splitdata table
'there can not be duplicate month numbers therefore the table will only hold _
data for one year
cmd.CommandText = "SELECT * FROM plt_SplitData WHERE MonthNum = " & mnth & _
" and Startdate is not null"
rs.Open cmd, , adOpenStatic
If Not rs.EOF Or Not rs.BOF Then
cmd.CommandText = "DELETE FROM plt_SplitData WHERE MonthNum = " & mnth & _
" and Startdate is not null"
cmd.Execute
End If
rs.Close
'call stored procedure that calculates sales total
cmd.CommandText = "sp_generatesplits"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 1000
cmd.Parameters.Append cmd.CreateParameter("startdate", adVarChar, adParamInput, 10)
cmd.Parameters("startdate"

.Value = startdate
cmd.Parameters.Append cmd.CreateParameter("enddate", adVarChar, adParamInput, 10)
cmd.Parameters("enddate"

.Value = enddate
cmd.Execute
cmd.Parameters.Refresh
UpdateColumns 'change null values to 0
Add_DCGrouping_Sales 'add up sales total for all locations for each DC
MsgBox "Split Data Complete."
End Sub
Private Sub DTStartDate_Change()
DTStartDate = CDate(MonthStart(DTStartDate))
DTEndDate = CDate(MonthEnd(DTStartDate))
End Sub
Private Sub DTEndDate_Change()
DTStartDate = MonthStart(DTEndDate)
DTEndDate = MonthEnd(DTEndDate)
End Sub
Private Sub Form_Load()
modglobal.Center Me 'center the form
DTStartDate = MonthStart(Now - 16)
DTEndDate = MonthEnd(Now - 16)
End Sub
Private Sub UpdateColumns()
'Set the month num in the table - causing errors when trying to pass it to the sp
startdt = DTStartDate
enddt = DTEndDate
mnth = Month(DTStartDate + 15)
cmd1.ActiveConnection = cnnFO
cmd1.CommandType = 8
'added the month num in the SP
'cmd.CommandText = "UPDATE plt_SplitData SET MonthNum = " & mnth & _
" WHERE Startdate = '" & startdt & "' and" & _
" EndDate = '" & enddt & "'"
'cmd.Execute
'If there are no sales the value will be null and when a null sales value is added _
to another number (in generate.asp) it makes the value of the 23 numbers null. _
This code converts all null values to 0 to avoid this problem
cmd1.CommandText = "UPDATE plt_SplitData SET GeneralActual = 0 WHERE GeneralActual " & _
"is null and monthnum = " & mnth
cmd1.Execute
cmd1.CommandText = "UPDATE plt_SplitData SET PaintActual = 0 WHERE PaintActual " & _
"is null and monthnum = " & mnth
cmd1.Execute
cmd1.CommandText = "UPDATE plt_SplitData SET FoodActual = 0 WHERE FoodActual " & _
"is null and monthnum = " & mnth
cmd1.Execute
cmd1.CommandText = "UPDATE plt_SplitData SET FurnitureActual = 0 WHERE FurnitureActual " & _
"is null and monthnum = " & mnth
cmd1.Execute
End Sub
Private Sub Add_DCGrouping_Sales()
'get all the locations in each DC and add up their totals
'insert totals into the splitdata table for each DC
'need this PLTM FO program
Dim rs As New Recordset
Dim rs1 As New Recordset
Dim rs2 As New Recordset
Dim rs3 As New Recordset
startdt = DTStartDate
enddt = DTEndDate
mnth = Month(DTStartDate + 15)
cmd2.ActiveConnection = conn
cmd2.CommandType = 8
cmd2.CommandText = "SELECT DCGroupingID,DCGrouping FROM DCGrouping"
rs.Open cmd2, , adOpenStatic
If Not rs.EOF Or Not rs.BOF Then
While Not rs.EOF
dcid = rs!DCGroupingID
dc = rs!dcgrouping
cmd2.ActiveConnection = conn
cmd2.CommandText = "SELECT StoreID,StoreNo FROM Store WHERE DCGroupingID = " & dcid
rs1.Open cmd2, , adOpenStatic
If Not rs1.EOF Or Not rs1.BOF Then
cmd2.ActiveConnection = cnnFO
While Not rs1.EOF
storeid = rs1!storeid
cmd2.CommandText = "SELECT generalActual,paintActual,foodActual," & _
"furnitureActual FROM plt_SplitData WHERE " & _
"locationID = " & storeid & " And monthnum = " & mnth
rs2.Open cmd2, , adOpenStatic
If Not rs2.EOF Or Not rs2.BOF Then
While Not rs2.EOF
gentotal = gentotal + rs2!generalActual
painttotal = painttotal + rs2!paintActual
foodtotal = foodtotal + rs2!foodActual
furntotal = furntotal + rs2!furnitureActual
rs2.MoveNext
Wend
End If
rs2.Close
rs1.MoveNext
Wend
cmd2.ActiveConnection = conn
dc = Mid(dc, 1, 3)
cmd2.CommandText = "SELECT StoreID FROM Store WHERE storeno = '" & dc & "'"
rs3.Open cmd2, , adOpenStatic
If Not rs3.EOF Or Not rs3.BOF Then
locID = rs3!storeid
cmd2.ActiveConnection = cnnFO
cmd2.CommandText = "UPDATE plt_SplitData SET generalactual = '" & gentotal & _
"',generaladjusted = '" & gentotal & "',paintactual = '" & _
painttotal & "',paintadjusted = '" & painttotal & _
"',foodactual = '" & foodtotal & "',foodadjusted = '" & _
foodtotal & "',furnitureactual = '" & furntotal & _
"',furnitureadjusted = '" & furntotal & "' WHERE " & _
"StartDate = '" & startdt & "' and enddate = '" & _
enddt & "' and locationID = " & locID
cmd2.Execute
End If
rs3.Close
End If
rs1.Close
gentotal = 0
painttotal = 0
foodtotal = 0
furntotal = 0
rs.MoveNext
Wend
End If
rs.Close
End Sub