Error 3048 "Cannot open any more databases"

M

miss031

I have seen this covered a few times, but I can't find a solution for me.

I have a form with an unbound subform. The user selects a record from a
search subformat the top, which sets that eord as the filter for the main
form, then hits a command button that binds the unbound form to a particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the "Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" & strWord & _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like ""*" &
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

' Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject = "subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject = "subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source objects?

I had domain aggregates in my recordsource queries, but I changed them to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
 
J

Jeanette Cunningham

Here are some things to check:
How many list boxes or combo boxes do you have on your form?
Do you have the same list boxes or combo boxes on each page of a tab
control?
How many subforms do you have on this form?
Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
If so, make your code set it/then to Nothing in the exit part of the sub or
function, this will work however the code exits the sub or function, whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

miss031 said:
I have seen this covered a few times, but I can't find a solution for me.

I have a form with an unbound subform. The user selects a record from a
search subformat the top, which sets that eord as the filter for the main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" & strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like ""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

' Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject = "subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed them to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
 
M

miss031

Jeanette Cunningham said:
Here are some things to check:
How many list boxes or combo boxes do you have on your form?
Click to expand...
None on the main form, one of the sourceobject forms has many, but the rest
have none. I don't think that's the issue, because I have this problem even
when I don't open the form that has lots of combos.
Do you have the same list boxes or combo boxes on each page of a tab
control?
Click to expand...
No, each form is different
How many subforms do you have on this form?
Click to expand...
The main form has 1 subform (the search form) and the other unbound subfom
that switches sourceobject. The (sub)form that is giving me problems has 2
subforms. I checked the recordsource and code on each of them to check that
there are no domain aggregates,and that there are no recordsets left open.
Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
Click to expand...
Not that I can see. I think there is one Set rs = , but it is set back to
nothing.

I noticed that if I search for a record one the search form, using the
posted search codee, and then search for another record, I get the message,
and the code breaks at:

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)


If so, make your code set it/then to Nothing in the exit part of the sub or
function, this will work however the code exits the sub or function, whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

miss031 said:
I have seen this covered a few times, but I can't find a solution for me.

I have a form with an unbound subform. The user selects a record from a
search subformat the top, which sets that eord as the filter for the main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" & strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like ""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

' Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject = "subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed them to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Here are some more things to check:

Domain aggregate functions in queries. Access runs the function for every
record, and this uses them up very quickly.

A complex query with nested queries, or many queries in a union query where
the queries are quite complex.

Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

A data structure where a table has a very large number of fields.

How many forms and reports do you normally have open before you hit the
error message?
Is there any one particular form or report that gives the error message?

Jeanette Cunningham

miss031 said:
Jeanette Cunningham said:
Here are some things to check:
How many list boxes or combo boxes do you have on your form?
Click to expand...
None on the main form, one of the sourceobject forms has many, but the
rest
have none. I don't think that's the issue, because I have this problem
even
when I don't open the form that has lots of combos.
Do you have the same list boxes or combo boxes on each page of a tab
control?
Click to expand...
No, each form is different
How many subforms do you have on this form?
Click to expand...
The main form has 1 subform (the search form) and the other unbound subfom
that switches sourceobject. The (sub)form that is giving me problems has 2
subforms. I checked the recordsource and code on each of them to check
that
there are no domain aggregates,and that there are no recordsets left open.
Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
Click to expand...
Not that I can see. I think there is one Set rs = , but it is set back to
nothing.

I noticed that if I search for a record one the search form, using the
posted search codee, and then search for another record, I get the
message,
and the code breaks at:

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)


If so, make your code set it/then to Nothing in the exit part of the sub
or
function, this will work however the code exits the sub or function,
whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

miss031 said:
I have seen this covered a few times, but I can't find a solution for
me.

I have a form with an unbound subform. The user selects a record from a
search subformat the top, which sets that eord as the filter for the
main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the
main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" &
strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like
""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

'
Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed them
to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

I suspect that it is the query your code builds to do the search that is
causing the problem.
What happens if you limit txt_search_box to 10 words as a test?

Maybe you need to re-design your search form so that user chooses from
combos instead of typing up to 33 different search terms into txt_search_box
..

Jeanette Cunningham


Jeanette Cunningham said:
Here are some more things to check:

Domain aggregate functions in queries. Access runs the function for every
record, and this uses them up very quickly.

A complex query with nested queries, or many queries in a union query
where the queries are quite complex.

Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

A data structure where a table has a very large number of fields.

How many forms and reports do you normally have open before you hit the
error message?
Is there any one particular form or report that gives the error message?

Jeanette Cunningham

miss031 said:
Jeanette Cunningham said:
Here are some things to check:
How many list boxes or combo boxes do you have on your form?
Click to expand...
None on the main form, one of the sourceobject forms has many, but the
rest
have none. I don't think that's the issue, because I have this problem
even
when I don't open the form that has lots of combos.
Do you have the same list boxes or combo boxes on each page of a tab
control?
Click to expand...
No, each form is different
How many subforms do you have on this form?
Click to expand...
The main form has 1 subform (the search form) and the other unbound
subfom
that switches sourceobject. The (sub)form that is giving me problems has
2
subforms. I checked the recordsource and code on each of them to check
that
there are no domain aggregates,and that there are no recordsets left
open.
Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
Click to expand...
Not that I can see. I think there is one Set rs = , but it is set back
to
nothing.

I noticed that if I search for a record one the search form, using the
posted search codee, and then search for another record, I get the
message,
and the code breaks at:

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)


If so, make your code set it/then to Nothing in the exit part of the sub
or
function, this will work however the code exits the sub or function,
whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

I have seen this covered a few times, but I can't find a solution for
me.

I have a form with an unbound subform. The user selects a record from
a
search subformat the top, which sets that eord as the filter for the
main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the
main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" &
strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord
&
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like
""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

' Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed them
to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
Click to expand...
Click to expand...
 
M

miss031

Interesting, I didn't realize that was a word limit; I thought it was to
limit the number of characters entered. I limited it to 2 words, and it
didn't make any difference.

Jeanette Cunningham said:
I suspect that it is the query your code builds to do the search that is
causing the problem.
What happens if you limit txt_search_box to 10 words as a test?

Maybe you need to re-design your search form so that user chooses from
combos instead of typing up to 33 different search terms into txt_search_box
..

Jeanette Cunningham


Jeanette Cunningham said:
Here are some more things to check:

Domain aggregate functions in queries. Access runs the function for every
record, and this uses them up very quickly.

A complex query with nested queries, or many queries in a union query
where the queries are quite complex.

Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.

Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.

A data structure where a table has a very large number of fields.

How many forms and reports do you normally have open before you hit the
error message?
Is there any one particular form or report that gives the error message?

Jeanette Cunningham

miss031 said:
:

Here are some things to check:
How many list boxes or combo boxes do you have on your form?
None on the main form, one of the sourceobject forms has many, but the
rest
have none. I don't think that's the issue, because I have this problem
even
when I don't open the form that has lots of combos.

Do you have the same list boxes or combo boxes on each page of a tab
control?
No, each form is different

How many subforms do you have on this form?
The main form has 1 subform (the search form) and the other unbound
subfom
that switches sourceobject. The (sub)form that is giving me problems has
2
subforms. I checked the recordsource and code on each of them to check
that
there are no domain aggregates,and that there are no recordsets left
open.

Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
Not that I can see. I think there is one Set rs = , but it is set back
to
nothing.

I noticed that if I search for a record one the search form, using the
posted search codee, and then search for another record, I get the
message,
and the code breaks at:

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)



If so, make your code set it/then to Nothing in the exit part of the sub
or
function, this will work however the code exits the sub or function,
whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

I have seen this covered a few times, but I can't find a solution for
me.

I have a form with an unbound subform. The user selects a record from
a
search subformat the top, which sets that eord as the filter for the
main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the
main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" &
strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord
&
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like
""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

' Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed them
to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
Click to expand...
Click to expand...
 
M

miss031

Jeanette Cunningham said:
Here are some more things to check:

Domain aggregate functions in queries. Access runs the function for every
record, and this uses them up very quickly.
Click to expand...
No, they have all been moved to the reports so they are only run for the
current record.
A complex query with nested queries, or many queries in a union query where
the queries are quite complex.
Click to expand...
No unions, and most all queries contain tables only. At most, they may have
one nested query.

Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.
Click to expand...
This is the only form open. I just wonder if I need to close something when
I switch sourceobjects in the subform.

There is a reference to RecordsetClone, but I commented it out and it didn't
help.

Private Sub Form_Current()

Dim rst As DAO.Recordset

If Me.RecordsetClone.RecordCount > 0 Then
Me.RecordsetClone.MoveLast
Me.txt_sum_records = Me.RecordsetClone.RecordCount
End If

Set rst = Nothing

End Sub


Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.
Click to expand...
Nothing like this anywhere.
A data structure where a table has a very large number of fields.
Click to expand...
All tables are normalized. The largest table has 4 fields.

How many forms and reports do you normally have open before you hit the
error message?
Is there any one particular form or report that gives the error message?

Jeanette Cunningham

miss031 said:
Jeanette Cunningham said:
Here are some things to check:
How many list boxes or combo boxes do you have on your form?
Click to expand...
None on the main form, one of the sourceobject forms has many, but the
rest
have none. I don't think that's the issue, because I have this problem
even
when I don't open the form that has lots of combos.
Do you have the same list boxes or combo boxes on each page of a tab
control?
Click to expand...
No, each form is different
How many subforms do you have on this form?
Click to expand...
The main form has 1 subform (the search form) and the other unbound subfom
that switches sourceobject. The (sub)form that is giving me problems has 2
subforms. I checked the recordsource and code on each of them to check
that
there are no domain aggregates,and that there are no recordsets left open.
Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
Click to expand...
Not that I can see. I think there is one Set rs = , but it is set back to
nothing.

I noticed that if I search for a record one the search form, using the
posted search codee, and then search for another record, I get the
message,
and the code breaks at:

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)


If so, make your code set it/then to Nothing in the exit part of the sub
or
function, this will work however the code exits the sub or function,
whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

I have seen this covered a few times, but I can't find a solution for
me.

I have a form with an unbound subform. The user selects a record from a
search subformat the top, which sets that eord as the filter for the
main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code for
searching and clicking the [cmd_use] is what sets the filter for the
main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" &
strWord
& _
"*"") OR ([contactcompany] Like ""*" & strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number] Like
""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

'
Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" & Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] = Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] = Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields = "contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields = "contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed them
to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Let's go back to the point in the code where the error pops up.
We will try to go step by step to eliminate possible causes.
Does the form fail to filter when you get to the line Me.Filter =
Left(strWhere, lngLen)?

What forms and subforms are open at the point where the error occurs?
I'm not sure where the code above runs - is it the main form or the subform?


In one of the posts it said the error happens at this point.
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)

put in these lines of code after the line: If lngLen > 0 Then

Debug.Print strWhere
run the form, go Ctl + G to open the immediate window
check the filter string to see if it looks OK
Create a new query that you can paste the filter string into and see if it
will run the query without errors.

If the filter runs in the main form, what happens if you do a test without
the subforms in the subform control?
If the filter runs in the subform, can you open the subform by itself and
try running the filter code- what happens with respect to the error?

If you could get the filter to run properly, what would you do after the
form has filterered?

Jeanette Cunningham


miss031 said:
Jeanette Cunningham said:
Here are some more things to check:

Domain aggregate functions in queries. Access runs the function for every
record, and this uses them up very quickly.
Click to expand...
No, they have all been moved to the reports so they are only run for the
current record.
A complex query with nested queries, or many queries in a union query
where
the queries are quite complex.
Click to expand...
No unions, and most all queries contain tables only. At most, they may
have
one nested query.

Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.
Click to expand...
This is the only form open. I just wonder if I need to close something
when
I switch sourceobjects in the subform.

There is a reference to RecordsetClone, but I commented it out and it
didn't
help.

Private Sub Form_Current()

Dim rst As DAO.Recordset

If Me.RecordsetClone.RecordCount > 0 Then
Me.RecordsetClone.MoveLast
Me.txt_sum_records = Me.RecordsetClone.RecordCount
End If

Set rst = Nothing

End Sub


Do you have code like Set db = CurrentDb() or
Set db = dbengine(0)(0)
Set db = WS.OpenDatabase ... or similar in a loop?
This uses them up fairly quickly.
Click to expand...
Nothing like this anywhere.
A data structure where a table has a very large number of fields.
Click to expand...
All tables are normalized. The largest table has 4 fields.

How many forms and reports do you normally have open before you hit the
error message?
Is there any one particular form or report that gives the error message?

Jeanette Cunningham

miss031 said:
:

Here are some things to check:
How many list boxes or combo boxes do you have on your form?
None on the main form, one of the sourceobject forms has many, but the
rest
have none. I don't think that's the issue, because I have this problem
even
when I don't open the form that has lots of combos.

Do you have the same list boxes or combo boxes on each page of a tab
control?
No, each form is different

How many subforms do you have on this form?
The main form has 1 subform (the search form) and the other unbound
subfom
that switches sourceobject. The (sub)form that is giving me problems
has 2
subforms. I checked the recordsource and code on each of them to check
that
there are no domain aggregates,and that there are no recordsets left
open.

Are you using CurrentDb? or dbEngine(0)(0)? or code like Set rs = ?
Not that I can see. I think there is one Set rs = , but it is set back
to
nothing.

I noticed that if I search for a record one the search form, using the
posted search codee, and then search for another record, I get the
message,
and the code breaks at:

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)



If so, make your code set it/then to Nothing in the exit part of the
sub
or
function, this will work however the code exits the sub or function,
whether
via an error or normally.

Here is an example for Current Db:

Private Sub SubName
Dim db as DAO.Database

Set db = CurrentDb()

'rest of your code here

SubExit:
Set db = Nothin

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub


Here is an example for a recordset

Private Sub SubName
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = .....'your statement here
'rest of your code here

SubExit:
Set rs = Nothing
Set db = Nothing

SubErr:
Msgbox Err.Description
Resume SubExit
End Sub

Jeanette Cunningham

I have seen this covered a few times, but I can't find a solution for
me.

I have a form with an unbound subform. The user selects a record
from a
search subformat the top, which sets that eord as the filter for the
main
form, then hits a command button that binds the unbound form to a
particuar
form, filtered by the main form record. This all works good, and
fast
enough, but when I try to print a report from one subform, I get the
"Cannot
open any more databases" message.

The search form is based on a query, and I use the following code
for
searching and clicking the [cmd_use] is what sets the filter for the
main
form:

___________________________________

Private Sub cmd_search_Click()

Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txt_search_box) Then 'Show all if blank.
Me.Filter = 0
Me.FilterOn = True
'If Me.FilterOn Then
' Me.FilterOn = False
'Else

'End If
Else
varKeywords = Split(Me.txt_search_box, " ")
If UBound(varKeywords) >= 33 Then '99 max ORs.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([lastname] Like ""*" &
strWord
& _
"*"") OR ([contactcompany] Like ""*" &
strWord &
"*"") OR ([FirstOfphone_number_] Like ""*" & strWord & "*"") OR
([bidder_number] Like ""*" & strWord & "*"") OR ([seller_number]
Like
""*"
&
strWord & "*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If

'
Me.Parent![frm_cont_bid_number_subf]![cbo_bid_number_ID].SetFocus

End Sub

Private Sub cmd_use_Click()
On Error GoTo Err_cmd_use_Click

Forms("add_new_all").Filter = "contact_ID =" &
Me.contactID_current
Forms("add_new_all").FilterOn = True
Me.Parent![frame_choose_sub].SetFocus
'Forms![add_new_all].subf_main_blank.Form.Requery

Me.Filter = "contact_ID = 0"
Me.txt_sum_records = Null

Exit_cmd_use_Click:
Exit Sub

Err_cmd_use_Click:
MsgBox Err.Description
Resume Exit_cmd_use_Click

End Sub

_____________________________________

The code that binds the unbound subform is as follows:

______________________________________

Private Sub frame_choose_sub_AfterUpdate()
On Error GoTo Err_frame_choose_sub_AfterUpdate

Dim strMsg As String
Dim strWhere As String 'added
strWhere = "contact_ID = " & Forms![add_new_all].contact_ID

If frame_choose_sub = 1 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_contact_info"
Forms![add_new_all].subf_main_blank.LinkMasterFields =
"contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields =
"contact_ID"
Forms![add_new_all].subf_main_blank.Form![txt_seller_number] =
Null
Forms![add_new_all].subf_main_blank.Form![txt_bidder_number] =
Null
'Forms![add_new_all].subf_main_blank.Form![Dummy] = 1
ElseIf frame_choose_sub = 2 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_cashiering"
Forms![add_new_all].subf_main_blank.LinkMasterFields =
"contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields =
"contact_ID"
ElseIf frame_choose_sub = 3 Then
Forms![add_new_all].subf_main_blank.Visible = True
Forms![add_new_all].subf_main_blank.SourceObject =
"subf_main_payouts"
Forms![add_new_all].subf_main_blank.LinkMasterFields =
"contact_ID"
Forms![add_new_all].subf_main_blank.LinkChildFields =
"contact_ID"

End If

Exit_frame_choose_sub_AfterUpdate:
Exit Sub

Err_frame_choose_sub_AfterUpdate:
MsgBox Err.Description
Resume Exit_frame_choose_sub_AfterUpdate

End Sub

_________________________________

Do I need to close the recordset each time I switch subform source
objects?

I had domain aggregates in my recordsource queries, but I changed
them
to
calculate on the forms and reports instead, correct?

Let me know what else you need to know. Please help, I'm lost!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

search priority 3
show msg if subfrm is not null 3
search form w/ no duplicates 7
subform - Dcount and filter 1
Allen Brownes ECount() 5
Error on modified Allenbrowne SearchForm 6
Modified Allenbrowne Search Form displays error 2
Filtering a multiple item form 1

Top