I have created an Access (2019) application to catalog small parts in my workshop, indexing them by Storage Box#, Column# and Row# within each box. The issue is that I added and catalogued parts as I encountered them so that they are not stored within the boxes in a useful manner, indexed by the hardwaretype, family, and specific type of the parts (these are nails, screws, nuts, bolts, washer and misc pieces.) (Example: HardwareType = "Screws"; Family = "Machine Screws"; SpecificType = "1/4-20 x 1-1/2" Pan head, Phillips drive, Zinc plated Machine Screw")
While the original storage routine did automatically add the three indexing parameters as I catalogued and stored each part (so that I could physically store it in the next available drawer location {Box/Col/Row}), the resulting locations need to be changed to reflect a sort by HardwareType, Family, and Specific Type.
I have written the sorting routine to order the list in a more useful fashion so that I can move the individual drawers to new locations.
But to do these moves efficiently requires that I link them so that after the first item, the drawer that it replaces must be moved to its new location, and the one that replaces to the next location, and so forth.
I have written code to produce a linked list, which is supposed to give me the order of doing the drawer moves. The code, for some unknown reason, leaves some number of the ReOrder indexes at zero. It leaves the first 30 at zero and starts with ReOrder = 31.
Before delving into the attached code, is there an existing routine to do this?
Here is the code:
Sub OldSortToNewSort()
Dim db As DAO.Database, rsSH As DAO.Recordset, rsON As DAO.Recordset, rsBox As DAO.Recordset
Dim i As Integer, B As Integer, NumBoxes As Integer, C As Integer, CMax As Integer, R As Integer, RMax As Integer
Dim SQL As String, SQLB As String, Boxes() As Variant, NumItems As Integer, ID As Long
Set db = CurrentDb
Rem SQLB GIVES THE SIZES OF EACH BOX THAT CONTAINS THINGS WE'RE GOING TO RE-SORT (i.e. HardwareParts)
Rem USE THE TABLE tblSetupNewBox TO BUILD A Variant ARRAY WE CAN USE TO GUIDE INDEXING THE NEW SORTED LIST OF ITEMS
SQLB = "SELECT * FROM tblSetupNewBox WHERE (((tblSetupNewBox.UsedFor) = " & Chr$(34) & "HardwareParts" & Chr$(34) & ")) ORDER BY tblSetupNewBox.BoxNum"
Set rsBox = db.OpenRecordset(SQLB, dbOpenDynaset)
rsBox.MoveLast ' GET THE NUMBER OF BOXES WE'RE INDEXING
NumBoxes = rsBox.RecordCount
ReDim Boxes(1 To NumBoxes, 1 To 3)
rsBox.MoveFirst ' GO BACK TO THE TOP OF THE LIST
i = 1 ' USE AN INTEGER INDEX BECAUSE SOME BOX NUMBERS AREN'T GOING TO BE RE-SORTED
Do While Not rsBox.EOF ' BUILD THE ARRAY. EACH BOX HAS A SPECIFIC ARRANGEMENT OF DRAWERS INTO COLUMNS AND ROWS
Boxes(i, 1) = rsBox("BoxNum")
Boxes(i, 2) = rsBox("MaxColumns")
Boxes(i, 3) = rsBox("MaxRows")
i = i + 1
rsBox.MoveNext
Loop
Rem CREATE THE BEGINNING OF THE RE-SORTED LIST BY COPYING THE OLD LIST OF INDICES, BOX, COLUMN AND ROW INTO THE TABLE tblOldSortToNewSort
Call ClearTable("tblOldSortToNewSort") ' START ANEW CREATING THIS TABLE EACH TIME WE RUN THIS PROCEDURE
Rem tblSortedHardware USES A PREDEFINED ORDER TO SORT THE FAMILIES, SUBFAMILIES, AND SPECIFIC TYPES
Set rsSH = db.OpenRecordset("tblSortedHardware", dbOpenDynaset) ' THIS GIVES THE FAMILIES WE WILL BE INTERESTED IN SORTING
Set rsON = db.OpenRecordset("tblOldSortToNewSort", dbOpenDynaset) ' HERE IS WHERE WE WILL COPY THE OLD INDEX LIST
Do While Not rsSH.EOF ' THIS LOOP ADDS ID AND OLD {BOX, COL, ROW} INDEX DATA TO THE TABLE LINKING OLD TO NEW INDEXES
rsON.AddNew
rsON("ID") = rsSH("ID") ' THE ID OF THE SPECIFIC PIECE OF HARDWARE
rsON("Box_Old") = rsSH("Box") ' ITS INDEX AT THE TIME IT WAS FIRST CATALOGUED (BOX, COL ROW)
rsON("Col_Old") = rsSH("Col")
rsON("Row_Old") = rsSH("Row")
rsON.Update
rsSH.MoveNext
Loop
Rem GET THE BOX SIZE DATA TO GUIDE THROUGH THE RE-SORTING
i = 1
B = Boxes(i, 1) ' BOX(i) BOX NUMBER
CMax = Boxes(i, 2) ' MAX COLUMNS FOR BOX(i)
RMax = Boxes(i, 3) ' MAX ROWS FOR BOX(i)
C = 1 ' COL COUNTER
R = 1 ' ROW COUNTER
rsSH.MoveFirst
Do While Not rsSH.EOF
rsON.FindFirst "ID = " & rsSH("ID") ' MATCH THE IDs OF THE TABLE tblOldSortToNewSort WE JUST BUILT TO THE ONES FROM THE SORTED LIST, tblSortedHardware
If Not rsON.NoMatch Then
rsON.Edit
rsON("Box_New") = B ' USE THE COUNTERS, B, C, R TO COPY THE INDEXES AS SEEN BY THE ORDER OF THE SORTED HARDWARE
rsON("Col_New") = C
rsON("Row_New") = R
rsON.Update
R = R + 1 ' DO HOUSEKEEPING TO ENSURE THAT THE INDEXES FIT THE DIMENSIONS OF THE BOX
If R > RMax Then
C = C + 1
R = 1
If C > CMax Then
i = i + 1
B = Boxes(i, 1)
CMax = Boxes(i, 2)
RMax = Boxes(i, 3)
C = 1
R = 1
End If
End If
End If
rsSH.MoveNext
Loop
NumItems = rsSH.RecordCount ' THIS GIVES THE TOTAL NUMBER OF ITEMS TO PROCESS
Rem WE WANT TO CREATE A LINKED LIST THAT TELLS US THE ORDER OF DOING THE RE-SORT. WE TAKE THE FIRST ITEM OF THE NEW LIST AND LOOK FOR WHERE IT IS IN THE OLD LIST
B = 1 ' START WITH THE FIRST ITEM ON THE NEW LIST (SORTED by BOX, ROW, COL)
R = 1
C = 1
For i = 1 To NumItems
rsON.FindFirst "Box_Old = " & B & " AND Row_Old = " & R & " AND Col_Old = " & C ' FIND WHERE IT IS IN THE OLD LIST
If Not rsON.NoMatch Then
B = rsON("Box_New") ' GET THE NEW BOX, COL, ROW INDEX AT THE RECORD OCCUPIED BY THE OLD INDEX
R = rsON("Row_New")
C = rsON("Col_New")
rsON.Edit
rsON("ReOrder") = i ' WRITE THE NEW INDEX TO THE OLD LIST'S ENTRY
rsON.Update
End If
Next i
End Sub