Recursive method problems (1 Viewer)

Option Compare Database
Public globalItem As String

Option Explicit

'
'   Public VAssembly As String, VSubAssembly As String, Vquantity As Integer
'   Public Vu_m As String, Vunits As String, Vleveltotal As Integer
'   Public sql As String

    
Sub BOMMethod(rst As Recordset, strAssembly As String, Optional Total As Integer)

' delare variables
    Dim tempTable As String
    Dim strCriteria As String
    Dim bk As String
    Dim tempRst As Recordset
    Dim SubTotal As Integer
           

' Open tblTemp table to store new BOM details

    Set tempRst = CurrentDb.OpenRecordset("tblTemp", dbOpenDynaset)


strCriteria = BuildCriteria("Assembly", dbText, "'" & strAssembly & "'")

'do not accept a zero as a value

If Total = 0 Then
    Total = 1
ElseIf Total = Null Then
    Total = 1
End If


With rst
' find first record of dataset
    .FindFirst strCriteria
    
' traverse all records
    Do Until .NoMatch
        ' add record to temp table using recordsets
      ' commented out due to err3048 problems (cannot open more databases)
      
        With tempRst
                .AddNew
                !Assembly = rst![Assembly]
                !SubAssembly = rst![SubAssembly]
                !quantity = rst![quantity]
                !u_m = rst![u_m]
                !units = rst![units]
                !leveltotal = rst![quantity] * Total
                .Update
        End With

'     '************** using SQL instead of recordset to enter data into temp table  **********
'     ' Stack error problems

'             VAssembly = rst![Assembly]
'             VSubAssembly = rst![SubAssembly]
'             Vquantity = rst![quantity]
'             Vu_m = rst![u_m]
'             Vunits = rst![units]
'             Vleveltotal = rst![quantity] * Total
'             sql = "INSERT INTO tblTemp(Assembly,SubAssembly, quantity,u_m, units,leveltotal)" & _
'             "VALUES (" & "'" & VAssembly & "'," & "'" & VSubAssembly & "' ," & Vquantity & "," & "'" & Vu_m & "'" & "," & "'" & Vunits & "'" & "," & Vleveltotal & ")"
'
'       'Debug.Print sql
'       DoCmd.SetWarnings False
'
'       DoCmd.RunSQL sql
'
'       DoCmd.SetWarnings True
'
        
  SubTotal = rst![quantity] * Total
         
        
        
    ' save place in recordset
        bk = rst.Bookmark
       
    'Loops method to get a subassembly of a subassembly
        BOMMethod rst, rst!SubAssembly, SubTotal
        
              
        ' return to last place to continue search
        rst.Bookmark = bk
        ' finds next record
        .FindNext strCriteria
        
    Loop ' do until .no match
    tempRst.Close
Set tempRst = Nothing

End With ' .rst


End Sub

Option Compare Database
Option Explicit

Public globalItem As String
private m_dbs as dao.database

private property get dbs as dao.database
' local exposure for a single, always on reference to CurrentDB
  if m_dbs is nothing then set m_dbs = currentdb
  set dbs = m_dbs
end property