Opening and closing Recordsets

[Peter Bellamy]

In a Form, subform, subsubform situation is it acceptable to Set and Close the db and the recordsets on opening and closing the main form?

It just seemed to me to be a faster than closing them each record, then opening them again for the next.

 

[gemma-the-husky]

I dont understand this - why would you need to close anything? what are you trying to do?

if data changes, you can generally do either

me.refresh, (re-reads the displayed data without moving the rst cursor- I think describes the behavious) or
me.requery (re-reads the entire recordset, and repositions the rst cursor at the first item)

 

[Peter Bellamy]

Dave,
I am getting data from a recordset in the subsubform for use in a calculation on the form. I am also doing the same thing for another table in the subform.

At the moment I close the db and recordsets after getting the data.
This means that stepping through the records of the main form I am opening and closing them all, which is time consuming, and perhaps unecessary.

I asked the question because I was thinking of opening everything OnLoad of the main form, requering on its OnCurrent event, and then closing everything on its Close event, but I didn't know the scope of the opened db and recordsets.

Peter

 

[vbaInet]

I think I understand what Peter's doing there. He's using a DAO recordset object in the On Current event of the subsubform and setting the objects "free" after every operation.

I would be inclined to create a global variable (within the form module), set it on Open event and set them free on the Unload event.

 

[Peter Bellamy]

Thanks, I will try that and see if it speeds up the code.

peter

 

[vbaInet]

I don't think you will see much of a difference but it's worth giving it a go. Are you sure that's the root of your problem? What does the code do?

 

[Peter Bellamy]

It is the db I posted recently and the recordsets in question lookup some tables to obtain some connected data.

Dim StrRst As Recordset
Dim StrDb As Database
Dim StrSql As String

StrSql = "SELECT * FROM Equipment WHERE equip_index = " & Me.call_equip_sno
Set StrDb = CurrentDb()
Set StrRst = StrDb.OpenRecordset(StrSql)

If StrRst.RecordCount > 0 Then
    expiredate = StrRst("[equip_expire_date]")
    serial = StrRst("[equip_serialno]")
    equip = StrRst("[equip_name1]")
    
    StrSql = "SELECT * FROM Product WHERE product_key = " & equip
    Set StrRst = StrDb.OpenRecordset(StrSql)
    cust = StrRst("[product_customer]")
    callno = Me.call_no
End If

Do you think it would be faster to base the forms on queries that include the tables? In this example the Calls Record form: It is now just Calls, so add Equipment and Product?

 

[DCrake]

Try

Dim StrRst As DAO.Recordset
Dim StrDb As DAO.Database

Also you need to use

StrRst.MoveLast

Toget the correct StrRst.RecordCount

 

[vbaInet]

It is the db I posted recently and the recordsets in question lookup some tables to obtain some connected data.

Dim StrRst As Recordset
Dim StrDb As Database
Dim StrSql As String

StrSql = "SELECT * FROM Equipment WHERE equip_index = " & Me.call_equip_sno
Set StrDb = CurrentDb()
Set StrRst = StrDb.OpenRecordset(StrSql)

If StrRst.RecordCount > 0 Then
    expiredate = StrRst("[equip_expire_date]")
    serial = StrRst("[equip_serialno]")
    equip = StrRst("[equip_name1]")
    
    StrSql = "SELECT * FROM Product WHERE product_key = " & equip
    Set StrRst = StrDb.OpenRecordset(StrSql)
    cust = StrRst("[product_customer]")
    callno = Me.call_no
End If

Do you think it would be faster to base the forms on queries that include the tables? In this example the Calls Record form: It is now just Calls, so add Equipment and Product?

I would say for your first recordset create a query and point the criteria to the call_equip_sno textbox. Set a hidden combo box's row source to that query and perform a record count on the combo box(i.e. combo1.RecordCount). You would need to requery the combo box before performing the count.