I am hoping I can get some guidance on some better "best practice" suggestions on how to handle this kind of code set. I understand that parameterized queries are a thing, but I am not quite there yet, but feel free to drop link to good tutorial on that if you must include that as part of your suggestion.
Here is my code that works well, but I am trying to optimize performance. I thought about dumping RecordSet
into an array but i am not sure how helpful that would be? I am pretty open to anything here as I would love to develop best practices in my learning structure.
I know this breaks a cardinal rule about not interacting with the worksheet directly unless needed, but I don't know a way around it with this particular scenario.
Private Sub CommandButton1_Click()
Dim fso As FileSystemObject: Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset: Dim strConnection As String
Dim i As Integer, fld As Object: Dim TotalRec As Long
Dim RecordNum As Long: Dim filelocation1 As String
Dim wBo As Workbook: Dim wsO As Worksheet
Dim answer As Integer: Dim myValue As Variant
Dim count As Long: Dim src As CodeModule
Dim dest As CodeModule: Dim QUV As Long
Dim IID As Long: Dim rCell As Range: Dim rRng As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
count = 1
myValue = InputBox("What was the last order number?")
RecordNum = myValue
Set wBo = ActiveWorkbook
With wBo
Set wsO = wBo.Sheets("Sheet1")
Sheets(1).Activate
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:PathtoFile" & "\" & ".accdb"
con.Open strConnection
rs.Open "SELECT MAX(ORDERNO) AS MaxAmtOrders FROM dbo_ITEMS", con
Sheets(1).Range("Z1").CopyFromRecordset rs
rs.Close
con.Close
TotalRec = wBo.Sheets("Sheet1").Range("Z1").Value
For y = myValue To TotalRec
If count >= TotalRec Then Exit For
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:PathtoFile" & "\" & ".accdb"
con.Open strConnection
Sheets(1).Activate
rs.Open "SELECT [30 on hand].Expr1 AS [Item#], " & _
"[30 on hand].IVDESC3 AS Brand, " & _
"[30 on hand].IVDESC1 AS Discription, " & _
"[30 on hand].IVALU, " & _
"[30 on hand].IVQTY001 AS [001 OH], " & _
"Sum(dbo_ITEMS.QUANTO) AS [Order QTY], " & _
"[30 on hand].IVAUX6 " & _
"FROM dbo_ITEMS INNER JOIN [30 on hand] ON dbo_ITEMS.ITEM = [30 on hand].Expr1 " & _
"WHERE ((dbo_ITEMS.ORDERNO) Between " & RecordNum & " And " & RecordNum + 25 & " AND ((dbo_ITEMS.ITEM_STATE)='cm')) " & _
"GROUP BY [30 on hand].Expr1, [30 on hand].IVDESC3, [30 on hand].IVDESC1, [30 on hand].IVALU, [30 on hand].IVQTY001, [30 on hand].IVAUX6 " & _
"ORDER BY [30 on hand].IVAUX6;", con
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(2, 0).Value = " "
If RecordNum + 25 < TotalRec Then
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 1).Value = "Order Numbers " & RecordNum & " - " & RecordNum + 25
ElseIf RecordNum >= TotalRec Then
Exit For
Else
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 1).Value = "Order Numbers " & RecordNum & " - " & TotalRec
End If
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 1).Interior.Color = RGB(153, 255, 255)
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(2, 0) = "Item#"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 1) = "Brand"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 2) = "Description"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 3) = "UPC"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 4) = "Store 001 OH"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 5) = "Order Quanity"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 6) = "Store 001 Loc"
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(0, 1).Activate
wBo.Sheets("Sheet1").Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(2, 0).CopyFromRecordset rs
rs.Close
con.Close
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:PathtoFile" & "\" & ".accdb""
con.Open strConnection
Sheets(5).Activate
rs.Open "SELECT [30 on hand].Expr1 AS [Item#], " & _
"[30 on hand].IVDESC3 AS Brand, " & _
"[30 on hand].IVDESC1 AS Discription, " & _
"[30 on hand].IVALU, " & _
"[30 on hand].IVQTY001 AS [001 OH], " & _
"Sum(dbo_ITEMS.QUANTO) AS [Order QTY], " & _
"[30 on hand].IVAUX6 " & _
"FROM dbo_ITEMS INNER JOIN [30 on hand] ON dbo_ITEMS.ITEM = [30 on hand].Expr1 " & _
"WHERE ((dbo_ITEMS.ORDERNO) Between " & RecordNum & " And " & RecordNum + 25 & " AND ((dbo_ITEMS.ITEM_STATE)='cm')) " & _
"GROUP BY [30 on hand].Expr1, [30 on hand].IVDESC3, [30 on hand].IVDESC1, [30 on hand].IVALU, [30 on hand].IVQTY001, [30 on hand].IVAUX6 " & _
"ORDER BY [30 on hand].IVAUX6;", con
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(2, 0).Value = " "
If RecordNum + 25 < TotalRec Then
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 1).Value = "Order Numbers " & RecordNum & " - " & RecordNum + 25
ElseIf RecordNum >= TotalRec Then
Exit For
Else
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 1).Value = "Order Numbers " & RecordNum & " - " & TotalRec
End If
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 1).Interior.Color = RGB(153, 255, 255)
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(2, 0) = "Item#"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 1) = "Brand"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 2) = "Description"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 3) = "UPC"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 4) = "Store 001 OH"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 5) = "Order Quanity"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 6) = "Store 001 Loc"
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(0, 1).Activate
wBo.Sheets("Sheet5").Cells(Sheet5.Rows.count, 1).End(xlUp).Offset(2, 0).CopyFromRecordset rs
rs.Close
con.Close
With wBo.Sheets("Sheet5").UsedRange.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Sheets(5).Columns("a").ColumnWidth = 6.57
Sheets(5).Columns("b").ColumnWidth = 28.57
Sheets(5).Columns("c").ColumnWidth = 42
Sheets(5).Columns("d").ColumnWidth = 10.29
Sheets(5).Columns("e").ColumnWidth = 11.57
Sheets(5).Columns("f").ColumnWidth = 12.71
Sheets(5).Columns("g").ColumnWidth = 11.71
With wBo.Sheets("Sheet5").PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
wBo.Sheets("Sheet5").PrintOut
count = count + 25
RecordNum = RecordNum + 25
If count >= TotalRec Then Exit For
If RecordNum >= TotalRec Then Exit For
RecordNum = RecordNum + 1
ActiveWorkbook.Sheets("Sheet5").UsedRange.ClearContents
With wBo.Sheets("Sheet5").PageSetup
.Orientation = xlPortrait
End With
Next y
Sheets(1).Columns("a").ColumnWidth = 6.57
Sheets(1).Columns("b").ColumnWidth = 28.57
Sheets(1).Columns("c").ColumnWidth = 42
Sheets(1).Columns("d").ColumnWidth = 10.29
Sheets(1).Columns("e").ColumnWidth = 11.57
Sheets(1).Columns("f").ColumnWidth = 12.71
Sheets(1).Columns("g").ColumnWidth = 11.71
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:PathtoFile" & "\" & ".accdb""
con.Open strConnection
rs.Open "SELECT [30 on hand].Expr1 AS [Item#], " & _
"[30 on hand].IVDESC3 AS Brand, " & _
"[30 on hand].IVDESC1 AS Discription, " & _
"[30 on hand].IVALU, " & _
"[30 on hand].IVQTY001 AS [001 OH], " & _
"[30 on hand].IVQTY000 AS [000 OH], " & _
"Sum(dbo_ITEMS.QUANTO) AS [Order QTY], " & _
"[30 on hand].IVAUX6, " & _
"[30 on hand].IVATTR " & _
"FROM dbo_ITEMS INNER JOIN [30 on hand] ON dbo_ITEMS.ITEM = [30 on hand].Expr1 " & _
"WHERE ((dbo_ITEMS.ORDERNO) Between " & myValue & " And " & TotalRec & " AND ((dbo_ITEMS.ITEM_STATE)='cm')) " & _
"GROUP BY [30 on hand].Expr1, [30 on hand].IVDESC3, [30 on hand].IVDESC1, [30 on hand].IVALU, [30 on hand].IVQTY001, [30 on hand].IVQTY000, [30 on hand].IVAUX6, [30 on hand].IVATTR " & _
"ORDER BY [30 on hand].IVAUX6;", con
i = 0
Sheets(2).Activate
ActiveWorkbook.Sheets("Sheet2").Range("a1").Select
For Each fld In rs.Fields
ActiveCell.Offset(0, i) = fld.Name
i = i + 1
Next fld
Sheets(2).Range("A2").CopyFromRecordset rs
Sheets(2).Columns("a").ColumnWidth = 6.57
Sheets(2).Columns("b").ColumnWidth = 28.57
Sheets(2).Columns("c").ColumnWidth = 42
Sheets(2).Columns("d").ColumnWidth = 10.29
Sheets(2).Columns("e").ColumnWidth = 11.57
Sheets(2).Columns("f").ColumnWidth = 12.71
Sheets(2).Columns("g").ColumnWidth = 11.71
Sheets(2).Columns("K").Formula = "=G1-E1"
rs.Close
con.Close
Set rRng = ActiveWorkbook.Sheets("Sheet2").Range("a2:a100")
For Each rCell In rRng.Cells
If rCell <> "" And rCell.Value <> 0 Then
IID = rCell
QUV = rCell.Offset(0, 6).Value
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:PathToFile" & "\" & ".accdb"
con.Open strConnection
rs.Open "UPDATE [30OnHand] SET [30OnHand].IVQTY001 = [30OnHand].IVQTY001 -" & QUV & " WHERE ((([30OnHand].IVNO)=" & IID & "));", con
con.Close
End If
Next rCell
ActiveWorkbook.SaveCopyAs ("C:PathToFile" & "\" & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".xlsm")
ActiveWorkbook.Close
End With
Sheets(1).Clear
Sheets(2).Clear
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub