Error: Cannot open any more databases (1 Viewer)

[cricketbird]

I am taking a large database (split front/back, 85 forms) and creating a script so that users can switch all of the button and label captions on each form to their local language.
The eventual script will use a table like this:

TableName	ControlName	LanguageCode	CaptionText
Form1	Button1	en	Print
Form1	Button1	de	Drucken

...to set the caption of each control. The script to update the controls using this table is working fine.

In order to populate this table in the first place, though (so we can send the captions out for translation), I have the code below that loops through each form and gathers the necessary table data about the controls from each form. The code itself works fine (debug.printing here, but eventually the intention is to write to a file). The problem is that about 20 forms in (out of 80+), I get the error "Cannot open any more databases".

1) Is there a way to loop through controls or gather the necessary data without OPENING each form?
or, alternatively,
2) Is there a way to "clear the database connections" after I close each form?

Thank you!

  Public Sub GetForms()
   On Error Resume Next
     Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject

    For Each obj In dbs.AllForms
        Debug.Print obj.Name
        If obj.IsLoaded = False Then DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
        DoEvents
        GetControls (obj.Name)
        DoCmd.Close acForm, "[" & obj.Name & "]"
    Next obj
End Sub

Sub GetControls(ByVal oForm As String)
    Dim oCtrl As Control
    Dim cOutputString As String
    For Each oCtrl In Forms(oForm).Controls
        Select Case oCtrl.ControlType
            Case acLabel: cOutputString = Forms(oForm).Name & vbTab & oCtrl.Name & vbTab & oCtrl.Caption
            Case acCommandButton: cOutputString = Forms(oForm).Name & vbTab & oCtrl.Name & vbTab & oCtrl.Caption
        End Select
            Debug.Print cOutputString
    Next
End Sub

 

[MarkK]

The problem is that this code...

DoCmd.Close acForm, "[" & obj.Name & "]"

...does not actually close the form, and you don't realize it, because they are all hidden.

In your DoCmd.OpenForm, command, try and open them such they are not hidden, and see if they are actually closing or not.

 

[cricketbird]

The problem is that this code...

DoCmd.Close acForm, "[" & obj.Name & "]"

...does not actually close the form, and you don't realize it, because they are all hidden.

In your DoCmd.OpenForm, command, try and open them such they are not hidden, and see if they are actually closing or not.

Thanks! You are correct! They are not closing!

When I type that same code in the immediate window (using a real form name, of course), it works. Why isn't it working in this function?

 

[MarkK]

This code fails to close Form1 on my machine....

DoCmd.Close acForm, "[Form1]"

I would remove the square brackets

 

[MajP]

There is no need for brackets since you are passing this as a string parameter to the function. As written that would assume the name of the form actually includes the square brackets.
docmd.openform "Form Name"
not
docmd.openform "[form Name]"

this fails

Public Sub Test()
  DoCmd.OpenForm "[frm bad name]"
  Debug.Print Forms("[frm bad name]").Name
  Debug.Print Forms!frm bad name.Name
End Sub

This works

Public Sub Test()
  DoCmd.OpenForm "frm bad name"
  Debug.Print Forms("frm bad name").Name
  Debug.Print Forms![frm bad name].Name
End Sub

 

[cricketbird]

Aha! That worked! Thank you!