vba code causing "Out of Stack Space"

[Mark-BES]

Hi, I am trying to integrate the excellent search facility (search multiple fields example) posted on this site by OldSoftBoss http://www.access-programmers.co.uk/forums/showthread.php?t=123219&highlight=advanced+search

When I re-create it in my db, it all works ok until I double click on the record and it runs the vba: SearchList_DblClick.

My "FrmProducts" form opens to the correct record, but I get an error mssg:
"Out of Stack Space" and the db has to be force closed in task manager.

The vba being used:

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.OpenForm "FrmProducts"

Set rst = Forms!FrmProducts.Recordset.Clone

rst.FindFirst "[ProductID] = " & Me.SearchList
Forms!FrmProducts.Bookmark = rst.Bookmark

DoCmd.Close acForm, Me.Name

Exit_SearchList_DblClick:
Exit Sub

Err_SearchList_DblClick:
MsgBox Err.Description
Resume Exit_SearchList_DblClick
End Sub

Any advise is very welcome as I am not great with vba (learning slowly). Thanks.

 

[Oldsoftboss]

A search on Google recommended creating a new Db and importing all objects. It said the database may be becoming corrupt. Possible caused by many design changes over time.

http://www.theofficeexperts.com/forum/showthread.php?t=7723

Dave

 

[Mark-BES]

Thanks fr the reply Dave. Unfortunately the suggestion did not work.

I tried troubleshooting with my very limited knowledge. By commenting out the following:

Set rst = Forms!FrmProducts.Recordset.Clone
rst.FindFirst "[ProductID] = " & Me.SearchList

The error did not occur - Ok, the the code did not do its correct task obviously but this may point to the part of the code causing the problem?

 

[RuralGuy]

This line:
Set rst = Forms!FrmProducts.Recordset.Clone
should really be:
Set rst = Forms!FrmProducts.Recordsetclone
since you are using DAO.
RecordSet.Clone is an ADO method.

 

[Mark-BES]

Thanks RuralGuy but thats not it either. I think I have found it though!

On my form FrmProducts, I have a subform called "FrmProducts Subform" This appeared to flicker when the error occured. I created a copy or the original as a test and deleted the subform in question. It then works Ok!

I presume I need to add some more vba to handle the subform? See attached.

 

[Mark-BES]

I don't know whats happened but it now seems to be working! Weird!!

Thanks for all your help Oldsoftboss and RuralGuy!!

 

[RuralGuy]

I hate it when problems just go away on their own. They're just as likely to come back on their own as well. Keep your fingers crossed and BackUp...BackUp...BackUp.

 

[Oldsoftboss]

I don't know whats happened but it now seems to be working! Weird!!

Thanks for all your help Oldsoftboss and RuralGuy!!

Was it a full moon?

Dave

 

[DreamGenius]

I'll tack this onto the end of this thread because the suggestion which lead to the solution to my particular problem came from here.

In attempting to make my database compatible with Access 2007, I made:

Public Function CurrentUser()
   CurrentUser = Environ("Username")
End Function

I then did a global replace on the entire code base to swap all instances of Environ("Username") with the new CurrentUser(), including in the function I'd just written - doh!

Yes, it's the height of stupidity, but it was easily done and thankfully didn't take too long to find.