在Access中从起始部件追溯BOM终项及层级的方法：数组还是递归？

Great question—let’s break this down step by step for your Access BOM scenario, focusing on practical, maintainable approaches rather than full copy-paste solutions.

一、从起始部件查找BOM终项：递归 vs 数组/迭代 #

Both approaches work, but the right choice depends on your BOM’s structure and depth:

1. 递归函数（直观优先） #

Recursion is perfect for hierarchical data like BOMs because it naturally mirrors the "go up one level until there’s no higher assembly" logic.

• How it works: Write a VBA function that takes a part number, looks up its Next Higher Assembly (NHA) in your BOM table, and calls itself with that NHA. Stop when the NHA is null/empty—this is your end item.
• Gotchas: Watch for circular references (e.g., Part A → Part B → Part A) to avoid infinite loops. Add a Collection or array to track visited parts and skip any you’ve already checked.
• Pros: Clean, easy to write and read for shallow-to-moderate BOM depths.

2. 数组/迭代方式（稳定性优先） #

If your BOM has extremely deep layers (Access VBA has a recursion depth limit, usually around 1000), iteration with an array or Collection is safer to avoid stack overflow.

• How it works: Start with your initial part in an array/collection. Loop through each item, look up its NHA, add the NHA to the collection, and repeat until no NHA exists. The last item in the collection is your end item.
• Pros: No recursion depth limits, easier to debug step-by-step, and naturally handles circular references by tracking visited parts.
• Recommendation: Use this if you’re unsure about your BOM’s maximum depth, or if you need to log the full path from start to end item.

二、构建部件追溯+独立表检查程序 #

Here’s a structured approach to build this workflow, with retention of the initial part number:

Core Steps #

• Capture initial input: Use an Access form text box (e.g., txtInputPart) to get the user’s starting part number. Store this in a variable (e.g., initialPart) and never overwrite it—you’ll need it for final reporting.
• Upward trace loop: Use either recursion or iteration to climb the BOM hierarchy. For each part you reach:
  1. Look up its Next Higher Assembly in your BOM table.
  2. Check if this NHA exists in your independent approval table (e.g., ApprovedAssemblies).
  3. Log the full trace path (initial part → ... → current NHA) for transparency.
• Handle outcomes:
  • If you find an NHA in the independent table, notify the user and link it back to the initial part.
  • If you reach the end item without finding a match, inform the user of that result.

Example VBA Snippet (Iteration Approach) #

Sub TraceAndCheckApprovedAssemblies()
    Dim initialPart As String
    Dim currentPart As String
    Dim isApprovedFound As Boolean
    Dim traceHistory As Collection
    
    ' Get initial part from form input
    initialPart = Nz(Forms!frmPartTracker!txtInputPart.Value, "")
    If initialPart = "" Then
        MsgBox "Please enter a valid part number."
        Exit Sub
    End If
    
    currentPart = initialPart
    Set traceHistory = New Collection
    traceHistory.Add currentPart ' Start with the initial part
    
    isApprovedFound = False
    
    Do While True
        ' Look up next higher assembly (sanitize input to avoid SQL issues)
        currentPart = Nz(DLookup("[NextHigherAssembly]", "BOMTable", _
            "[PartNumber] = '" & Replace(currentPart, "'", "''") & "'"), "")
        
        If currentPart = "" Then Exit Do ' Reached end of hierarchy
        
        traceHistory.Add currentPart
        
        ' Check if current NHA is in the approved table
        If DCount("*", "ApprovedAssemblies", _
            "[PartNumber] = '" & Replace(currentPart, "'", "''") & "'") > 0 Then
            isApprovedFound = True
            MsgBox "Found approved assembly: " & currentPart & vbCrLf & _
                   "Initial part: " & initialPart
            Exit Do ' Stop at first match; remove this line to find all matches
        End If
    Loop
    
    ' No approved assemblies found in trace
    If Not isApprovedFound Then
        MsgBox "No approved higher assemblies found for initial part: " & initialPart & _
               vbCrLf & "Full trace path: " & JoinCollection(traceHistory, " → ")
    End If
End Sub

' Helper function to convert collection to string for output
Function JoinCollection(col As Collection, delimiter As String) As String
    Dim item As Variant
    Dim result As String
    For Each item In col
        result = result & item & delimiter
    Next
    JoinCollection = Left(result, Len(result) - Len(delimiter))
End Function

Key Tips #

• Sanitize input: Use Replace(currentPart, "'", "''") to avoid SQL injection errors if parts can contain single quotes.
• Track history: Using a Collection or temporary table to log the trace path lets users see the full hierarchy, which is crucial for debugging and validation.
• Performance: For large BOMs, replace DLookup/DCount with DAO recordsets—they’re faster for repeated queries.

内容的提问来源于stack exchange，提问作者accessuser