Results 1 to 2 of 2
  1. #1
    TDinDC is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Location
    Hyattsville MD
    Posts
    3

    Creating a Linked List to Update Indexing

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I noticed how many views of your thread with zero replies. Two things I'll suggest: post code within code tags (# on forum toolbar) and 2) treat us like we're 4 years old when describing your issue. For me, it's a bit too vague. I understand the hardware part, but not the moving stuff around part.

    To be honest, most of the time I see a lot of code posted like that, I won't even read it. Saying that might irk some people but that's my prerogative given the low pay for suffering through it.

    It may be too late to edit your post to fix the code, so you can try waiting for an answer, or you can mark this one as solved an start over.
    I wish you good luck in solving your issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-24-2018, 03:51 PM
  2. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  3. Replies: 0
    Last Post: 07-15-2015, 02:22 AM
  4. Update a record on a linked Sharepoint list
    By PoolHallJunkie in forum Programming
    Replies: 2
    Last Post: 09-24-2014, 07:25 PM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums