Object Invalid or no longer Set Runtime 3420

[bloise]

Hello,

I hope someone can help me with this problem. I have a form that has contains a subform. The user may add a record to the subform. The code for adding the record to the subform is...

Set ws = DBEngine.Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)
ws.BeginTrans
Set rs = db.OpenRecordset("tblClientSupport")
rs.AddNew
rs!ODBIDClient = Me!ODBID
rs!ODBIDSupport = Me!cboNewSupportPerson
rs!clspSupportType = Me!cboSupportType
rs.Update
ws.CommitTrans
Me![frmSubSupport].Requery
rs.Close
ws.Close
db.Close

This all works fine, however if the user trys to view another client via a command find button and drop down list
code is...

**** Me.RecordsetClone.FindFirst "[ODBID] = " & Me!cboSelect
Me.Bookmark = Me.RecordsetClone.Bookmark
Me!CmdFind.SetFocus
Me!lblSelect.Visible = False
Me!cboSelect.Visible = False
Me!cboSelect.Value = ""

The code with the *** is throwing the error. The ODBID is
the primary key for tblClients.

Hopefully someone can make some sense of this post.

Thanks
Mike

 

[SiJP]

You've closed down the recordset...

"rs.Close"

For your find button, you will neeed to open the recordset up again.

------------------------
Hit any User to continue

 

[bloise]

Thanks for the quick response. The recordset I close
pertains to the subform action. I am adding support persons to the Clients via table tblClientSupport. The main form is linked to tblClients with an datasource link to a query to tblClients.

I closed the recordset because I am finished with the statement of work pertaining to inserting a new record into
tblClients and I think the scope of the rs is just for add function.

Still a little confused, but thanks for the help..
Mike

 

[SiJP]

You can do edit, add, delete or simple record navigation and record retrieval with the recordsets..

If you have your form recordsource set as your query, and all you are trying to do is show the record based ont eh combo, then you could try a me.Filter ..

------------------------
Hit any User to continue

 

Well, I fixed the problem, but do not really understand why, which bothers me because i have not learned anything.

What i did was removed the workspace code...

Set ws = DBEngine.Workspaces(0)
ws.BeginTrans
ws.CommitTrans...

At least my customer will be happy now...
Mike