Can I have 2 recordsets open at the same time?

[redrover]

I need to perform a yearly update of an employee table using information from
the transaction table. An update query will not allow me to do this. I
tried opening both tables as recordsets so I could calculate the value I need
from the transaction table and then update the employee table using this
calculated value. I'm getting a "Run-time error '91': Object Variable or
With block variable not set".

Here's my declaration code:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cnn2 As ADODB.Connection
Dim rst2 As ADODB.Recordset

Dim strFlex As String

Set cnn = CurrentProject.Connection
Set cnn2 = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "[tblTimeOff]", cnn, adOpenDynamic, adLockPessimistic
rst2.Open "[tblTeamMemberSummary]", cnn2, adOpenDynamic, adLockPessimistic

The error is pointing to the last statement.

Any ideas on how to fix this or if there is a better way to perform this
function will be greatly appreciated.

 

[Lance]

You need to insert a line of..

Set rst2 = New ADODB.Recordset

 

[Dirk Goldgar]

I need to perform a yearly update of an employee table using information
from
the transaction table. An update query will not allow me to do this. I
tried opening both tables as recordsets so I could calculate the value I
need
from the transaction table and then update the employee table using this
calculated value. I'm getting a "Run-time error '91': Object Variable or
With block variable not set".

Here's my declaration code:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cnn2 As ADODB.Connection
Dim rst2 As ADODB.Recordset

Dim strFlex As String

Set cnn = CurrentProject.Connection
Set cnn2 = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "[tblTimeOff]", cnn, adOpenDynamic, adLockPessimistic
rst2.Open "[tblTeamMemberSummary]", cnn2, adOpenDynamic,
adLockPessimistic

The error is pointing to the last statement.

Any ideas on how to fix this or if there is a better way to perform this
function will be greatly appreciated.

You never instantiated rst2. You need a line:

Set rst2 = New ADODB.Recordset

.... before you open it.

Why are you using two different connection objects? You don't need cnn2;
you can use cnn for both recordsets.

 

[redrover]

Thank you! Wasn't sure about the connection object issue.

I need to perform a yearly update of an employee table using information
from
the transaction table. An update query will not allow me to do this. I
tried opening both tables as recordsets so I could calculate the value I
need
from the transaction table and then update the employee table using this
calculated value. I'm getting a "Run-time error '91': Object Variable or
With block variable not set".

Here's my declaration code:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cnn2 As ADODB.Connection
Dim rst2 As ADODB.Recordset

Dim strFlex As String

Set cnn = CurrentProject.Connection
Set cnn2 = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "[tblTimeOff]", cnn, adOpenDynamic, adLockPessimistic
rst2.Open "[tblTeamMemberSummary]", cnn2, adOpenDynamic,
adLockPessimistic

The error is pointing to the last statement.

Any ideas on how to fix this or if there is a better way to perform this
function will be greatly appreciated.

You never instantiated rst2. You need a line:

Set rst2 = New ADODB.Recordset

... before you open it.

Why are you using two different connection objects? You don't need cnn2;
you can use cnn for both recordsets.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)