Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-06-2007, 06:23 AM   #1
TimW
Newly Registered User
 
Join Date: Feb 2007
Location: Crowland, Peterborough UK
Posts: 91
Thanks: 3
Thanked 11 Times in 10 Posts
TimW is on a distinguished road
Recursive method problems

Hi, I wonder if anyone can help with my little problem. I have a recursive method that has worked until i started getting Error 3048 - Cannot open anymore databases. I tried to rework the code to use docmd.runSQL but then i have a stack error. I have commented out this code.
I think the error may be because i have set the recordset in my method everytime the method is called. Is there anyway that this can be overcome? How can i see what recordsets are open and need closing? And can this be done outside the recursive query?
This method is called by two different but similar forms - it works for one but not the other. ??
The recursive method is necessary because i am dealing with Bill Of materials and i do not know how many levels to go down without doing this.
I have copied the code below

Thanks in advance

Tim

Code:
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

TimW is offline   Reply With Quote
Old 02-06-2007, 06:46 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
I'd try this first...
Code:
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
Then I'd replace every reference to "CurrentDB." with "dbs."
Then I'd replace every "DoCmd.RunSQL " with "dbs.execute "

What I think happens in the recursive loop is that every reference to CurrentDB allocates stack space for a new DAO.Database variable, and I suspect this also happens with DoCmd.RunSQL which also must require a Database object to work with. My proposed code allows each unresolved iteration of the recursive loop to reference the same DAO.Database object without having to create a new one (or more) for each loop.
MarkK is offline   Reply With Quote
Old 02-06-2007, 07:13 AM   #3
TimW
Newly Registered User
 
Join Date: Feb 2007
Location: Crowland, Peterborough UK
Posts: 91
Thanks: 3
Thanked 11 Times in 10 Posts
TimW is on a distinguished road
Recursive method problems

lagbolt, thanks for your prompt reply. I implemented your solution and now i get an error saying i have to many tables open!
I tried runing the method on a different set of data and it worked

So, I deleted all the data from the table and re- imported it. And now it works! (At least for the moment)

Thanks again for your help

Tim W

TimW is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access 2003 queries on Vista Steve Lang Windows 19 12-02-2009 09:23 AM
How to display a message while generating report.. vsap Forms 1 06-07-2005 07:25 PM
problems with Find method in ADO Lol Owen Modules & VBA 2 03-15-2005 11:28 PM
Problems mixing numeric fields and text fields in Query by Form search method Sharky II Forms 4 01-17-2004 03:50 PM
openRecordset method problems AndyP Modules & VBA 2 01-16-2003 12:26 AM




All times are GMT -8. The time now is 11:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World