Solved

Access - Form with Tab control - multi tabs w/ multi sub froms on each - Cannot Open any more DBs

Posted on 2014-01-19
17
798 Views
Last Modified: 2014-01-19
Hello experts.

As some know I've been working on a db that I have split Frontend-Backend.  My main input form has a tabcontrol.  I have multiple tab pages and on each of those I have multiple subforms (some with their own subform).

I just went to one of the Page's subforms and clicked a combobox and get "Cannot open any more databases". ??????????  First time I've hit this.

Couple of questions.

Is there a way, while I have the main form open and am switching between pages to have a textbox populated on the main page to display how many dbs are open so I can tell how many are actually open?

Since a many of my forms use DAO.recordset is there a way in the VB project to scan all forms to open forms with dao.recordset so i can make sure I've SET all dao's to closed after their processing.

If I haven't executed a form with DAO.recordset would that even be part of the problem??

Basically looking for input on where to start looking.
0
Comment
Question by:wlwebb
  • 8
  • 4
  • 3
  • +1
17 Comments
 

Author Comment

by:wlwebb
ID: 39791991
PS.... Is this problem a function of splitting the DB front-end and back-end.......

I'm using A2007
0
 
LVL 84
ID: 39792039
As far as I know Access manages this internally and there is no way to get this information, nor is there anything you can do about those connections (other than remove the item that is causing the connection, of course).

With that said - Access is really referring to open connections to the database, and yes, it can be exacerbated by splitting, since the use of local tables don't seem to bring about this error.

However, the root cause would very likely be here:
I have multiple tab pages and on each of those I have multiple subforms (some with their own subform).
Subforms create one or more connections. Combos and Listboxes do as well. Many advocate the use of a single subform control on a form, and you then fill that subform when the user needs to work with it. In your case, perhaps you could use the Tab Control's Change event to set the Source Object of your Subform Control.
0
 

Author Comment

by:wlwebb
ID: 39792044
Scott
I don't think I'm following/understanding
Subforms create one or more connections. Combos and Listboxes do as well. Many advocate the use of a single subform control on a form, and you then fill that subform when the user needs to work with it. In your case, perhaps you could use the Tab Control's Change event to set the Source Object of your Subform Control.

Are you saying instead of having everything on one tabbed form that I split into separate forms that open after one form's tab is completed? (presumably using OpenArgs to open the second form)

????

Or are you saying that each Tab Page's subform connections shouldn't be set until focus is set to that tab.  If that's the case then ultimately if they fill out all tabs wouldn't the links eventually reach that same limit?????
0
 
LVL 84
ID: 39792065
I'm saying to use a single Subform control, and set the Source Object of that subform control when the user click on the tab page. So if your form has 10 tab pages and 10 Subform controls, you'd change that instead to a form with 10 tab pages and 1 Subform control. In the Tab Control's Change event:

Sub MyTabControl_Change()
  Select Case MyTabControl
    Case 0 'first page
       Me.SubformControl.SourceObject="sfrmCustomer"
    Case 1 'second page
       Me.SubformControl.SourceObject="sfrmInvoice"
    Case else
       Me.SubformControl.SourceObject=""
  End Select
End Sub
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39792128
no points please.

I second Scott's recommendation.  Another possible recommendation would be to keep your multiple subform controls, but only populate them with an actual SourceObject when you click on that subform.  If you do this, then you would also need to clear the subform control on the previous tab to avoid getting to the point where you have too many of those connections going.  It might look like:
Private Sub myTabControl_Change

    Static intPrevTab as integer

    'This section would clear the subform controls SourceObject property 
    'for those subforms you don't use as frequently.
    Select Case intPrevTab
          Case 0, 3, 5
                 'dont do anything to these controls
          Case 1
                 me.SubformControlName#1.SourceObject = ""
          Case 2
                  me.SubformControlName#2.SourceObject = ""
                  me.SubformControlName#3.SourceObject = ""
          Case 4
                 me.SubformControlName#6.SourceObject = ""
    End Select

    intPrevTab = myTabControl  

    Select Case intPrevTab
          Case 1
                 if me.subformcontrolName#1.SourceObject = "" Then
                     me.SubformControlName#1.SourceObject = "sfrmCustomer"
                 endif
          Case 2
                  if me.subformControlName#2.SourceObject = "" Then
                       me.SubformControlName#2.SourceObject = "sfrmInvoices"
                       me.SubformControlName#3.SourceObject = "sfrmPayments"
                 EndIf
          Case 3
                 if me.subformcontrolName#1.SourceObject = "" Then
                     me.SubformControlName#1.SourceObject = "sfrmEmployees"
                 endif
          Case 4
                 if me.subformcontrolName#1.SourceObject = "" Then
                     me.SubformControlName#1.SourceObject = "sfrm4"
                 endif
    End Select

End Sub

Open in new window

If you use either Scott's or my code, you may also have to reestablish the link between the main form and the subform.  I say "may" because I've never encountered a problem with the subform control retaining these values when the SourceObject is set to "", but I have heard comments from others that say they have.  If that is the case, then every time you reset the SourceObject to a form name, add code similar to:

me.subformControlName.LinkMasterFields = "[ID]"
me.subformControlName.LinkChildFields = "[Emp_ID]"

In this example, the [ID] field is the primary key in my Employees table, which is the RecordSource on the main form.  The [Emp_ID] is the field I want to use to link in my [Employee_PhoneNumbers] table.
0
 

Author Comment

by:wlwebb
ID: 39792141
Ok..... So I think what you're saying is....

Right now I have 7 Pages on my tab control.  So that when the main form loads the pages of the tab control are there but really there's nothing on them until you click the tab control....

So nothing on the non visible or non current tab page is "loaded" until that tab page is selected.

Is that correct.?????

If so, do I have to change the Onload event of any of the Subforms currently on those tab pages?????
0
 

Author Comment

by:wlwebb
ID: 39792147
Fyed...
I hadn't refreshed my page so I didn't know you had posted before my comment at 8:42 above.  I'll read and "attempt" to understand.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39792155
So nothing on the non visible or non current tab page is "loaded" until that tab page is selected.

My last company we had a programmer that did that. He ran into the same issue. And Tab1 was a read-only "landing-page".

dao.recordset so i can make sure I've SET all dao's to closed after their processing.

Until you do a "SET dao.recordset = Nothing" the object still exists. I usually make that one of the last steps in the function/sub.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:wlwebb
ID: 39792165
Fyed....

So I don't screw this up....going to attempt......

My main form name is [tfrm_records]
on that form in the Detail I have a tab control named [tabctlRecords]

On the tab control I have 7 pages numbered sequentially Page Index 0-6 with their "Name" being PageX where "x" is 1 to 7

So if I am going to do lets say Tab Control Page Index 1 which is Page Name Page2

That Tab Control has only 1 subform.  That Subform name is
[frmSys_LandTracts]


I "THINK" my code would be:
Private Sub myTabControl_Change
    Static intPrevTab as integer
Select Case intPrevTab
Case 1
                me.[tabctlRecords].[Page1].SourceObject = ""
End Select

Select Case intPrevTab
          Case 1
                 if me.[tabctlRecords].[Page1].SourceObject = "" Then
                     me.[tabctlRecords].[Page1].SourceObject = "" Then "frmSys_LandTracts"
                 endif
End Select

Open in new window

Does that seem right?
0
 

Author Comment

by:wlwebb
ID: 39792169
Jim P

I assumed that Set DAO is always preceeded by {recordsetname}=nothing....

Is that correct?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39792215
Usually my code is something like this:

Public Function Export_Tilde

Dim SQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim I As Integer
'Blah
    
    SQL = "select * " & _
        "From BLAH"

Set DB = CurrentDb()                              'Use the current database
Set RS = DB.OpenRecordset(SQL )      'actually open the recordset

If RS.EOF = False Then
    RS.MoveFirst
Else
    MsgBox "No Data", vbExclamation, "Exiting Function"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If

'do whatever

Set RS = Nothing
Set DB = Nothing

End Function

Open in new window

That gets rid of the objects in memory. And note that if you did something like
Dim DB As DAO.Database

Dim RS1 As DAO.Recordset
Dim RS2 As DAO.Recordset

Set DB = CurrentDb()                              'Use the current database
Set RS1 = DB.OpenRecordset("Table1")      'actually open the recordset

Set DB = CurrentDb()                              'Use the current database
Set RS2 = DB.OpenRecordset("Table2")      'actually open the recordset

Open in new window


You would have four objects open, not just three.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39792363
@wlwebb

Ref your reply:

Not quite right.  In the change event, you need to refer to the tab controls value (the default) property to determine what actions to take.  But you don't need to refer to the tab control or the page # to reference the subforms on those tabs.  Those subforms, although they only display on a particular page of the tab control, belong to the form, not the tab.

So, instead of what you posted, you would use something like:
Private Sub tabctlRecords_Change

    Static intPrevTab as integer

    Select Case intPrevTab
        Case 1
            me.subformControlName.SourceObject = ""
    End Select

    Select Case tabctlRecords.Value
          Case 1
                 if me.subformControlName.SourceObject = "" Then
                     me.subformControlName.SourceObject = "frmSys_LandTracts"
                 endif
    End Select

    intPrevTab = me.tabctlRecords.Value

End Sub

Open in new window

Personally, I would copy your main form and work with a copy of it.

Note above that I have used "subformControlName" as a reference to the subform control.  This is the name of the subform control that is sitting on your main form.  It might be "frm_Sys_LandTracts", but if you are using a control naming convention, you might have given it another name, like "sub_LandTracts".  In either case, when you are refering to the subform in code, like this, you need to use the "NAME" of the control, which may not be the name of the subform that is on that control.
0
 

Author Comment

by:wlwebb
ID: 39792519
Fyed.....
OK so in the pre open state in my example I would want to set my SourceObject name of Page Index (1) {tab page name - Page2}to blank

Correct?
0
 

Author Closing Comment

by:wlwebb
ID: 39792655
Thank you Fyed......

Hasn't completely fixed my problem but eliminated it on the first record that I enter....

I am going to follow up on this question with a new one that will go to a new question step that the answer to this one has raised for me.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39792781
Yes.

At least for those subforms that you rarely use.  The other issues with setting the SourceObject of all your subforms to their actual subform is that when you load your application, all of those subforms have to load, which can take a while and might sour your customers on your application.

By only loading them when they are needed, you increase the time to make the shift between tabs a little, but it is usually not as significant as if you to load multiple subforms when the form is loaded.
0
 
LVL 84
ID: 39793014
The accepted comment is very similar to my first comment, so I'm curious as to the point assignment ...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39793052
Me too, since in my first post, I explicitly said:

"no points please."

because I was piling on Scott's comment.  Please request assistance and reassign points to Scott,
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now