Can I have two recordsets open at once? Code works through one tim

  • Thread starter Thread starter ThriftyFinanceGirl
  • Start date Start date
T

ThriftyFinanceGirl

I have a procedure where I need two recordsets... I have Dimensioned two
recordsets... rst and rst2. But at the end of the loop rst one has "lost"
itself....
 
ThriftyFinanceGirl said:
I have a procedure where I need two recordsets... I have Dimensioned two
recordsets... rst and rst2. But at the end of the loop rst one has "lost"
itself....
Click to expand...


You must have made a mistake in your code. You can certainly have two, or
three, or fifty recordsets open at once.
 
ThriftyFinanceGirl said:
I have a procedure where I need two recordsets... I have Dimensioned two
recordsets... rst and rst2. But at the end of the loop rst one has "lost"
itself....
Click to expand...

Post your code. You can have multiple recordsets open with no issues. I
do this all the time with ADO code. Pretty sure DAO can do it too.

Matt
 
Well that's what I thought, but it stops... (at the rst.movenext) Can you
see it?

Set rst = Forms!frmWorkWithTax.RecordsetClone

Do While Not rst.EOF ' Iterate through each element.
dblBusUnit = Forms!frmWorkWithTax!BusUnit
intTaxID = rst!TaxID
varExFrRecon = rst!ExcludeFromRecon
'check the length of TaxID
If Len(Nz(intTaxID, "")) <> 0 Then
strWhere3 = "WHERE ([LocationObjects]![TaxID] = " &
intTaxID & ")"
strWhere4 = "WHERE ([TaxRatesBases]![TaxID] = " &
intTaxID & ")"
End If

'if the tax is marked "exclude from Recon" skip all this below
If varExFrRecon = False Then
If DCount("Taxable", "LocationObjects", "TaxID = " &
intTaxID) < 1 Then

'there are no LocationObjects for this tax
DoCmd.OpenForm "qryCopyLocObjGROUP", , , "[taxid] =
" & CopyTaxID & ""
Set rst2 = Forms!qryCopyLocObjGROUP.RecordsetClone

Do While Not rst2.EOF ' Iterate through each
element.
intObjID = Forms!qryCopyLocObjGROUP.ObjectAcctNo
TaxYN = Forms!qryCopyLocObjGROUP.Taxable

If IsNull(Forms!qryCopyLocObjGROUP.StartTaxM) Then
intStartM = 0
intStartY = 0
'create the copied record with the new Tax Rate ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit, "
& intTaxID & " AS taxid, " & _
"" & intObjID & " AS objectacctno, " &
TaxYN & " AS taxable;")

Else
intStartM = Forms!qryCopyLocObjGROUP.StartTaxM
intStartY = Forms!qryCopyLocObjGROUP.StartTaxY
'create the copied record with the new Tax Rate ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, starttaxm, starttaxy, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit, "
& intTaxID & " AS taxid, " & intStartM & " AS " & _
"starttaxm, " & intStartY & " AS
starttaxy, " & intObjID & " AS objectacctno, " & TaxYN & " AS taxable;")

End If
rst2.MoveNext
If (rst2.EOF) = False Then
Forms!qryCopyLocObjGROUP.Bookmark = rst2.Bookmark
End If
Loop
rst2.Close
DoCmd.Close acForm, "qryCopyLocObjGROUP"

Else
'there are LocationObjects for this tax
'check the next record
End If
End If
rst.MoveNext
If (rst.EOF) = False Then
Forms!frmWorkWithTax.Bookmark = rst.Bookmark
End If

Loop
rst.Close
 
ThriftyFinanceGirl said:
Well that's what I thought, but it stops... (at the rst.movenext) Can you
see it?

Set rst = Forms!frmWorkWithTax.RecordsetClone

Do While Not rst.EOF ' Iterate through each element.
dblBusUnit = Forms!frmWorkWithTax!BusUnit
intTaxID = rst!TaxID
varExFrRecon = rst!ExcludeFromRecon
'check the length of TaxID
If Len(Nz(intTaxID, "")) <> 0 Then
strWhere3 = "WHERE ([LocationObjects]![TaxID] = " &
intTaxID & ")"
strWhere4 = "WHERE ([TaxRatesBases]![TaxID] = " &
intTaxID & ")"
End If

'if the tax is marked "exclude from Recon" skip all this
below
If varExFrRecon = False Then
If DCount("Taxable", "LocationObjects", "TaxID = " &
intTaxID) < 1 Then

'there are no LocationObjects for this tax
DoCmd.OpenForm "qryCopyLocObjGROUP", , , "[taxid] =
" & CopyTaxID & ""
Set rst2 = Forms!qryCopyLocObjGROUP.RecordsetClone

Do While Not rst2.EOF ' Iterate through each
element.
intObjID = Forms!qryCopyLocObjGROUP.ObjectAcctNo
TaxYN = Forms!qryCopyLocObjGROUP.Taxable

If IsNull(Forms!qryCopyLocObjGROUP.StartTaxM) Then
intStartM = 0
intStartY = 0
'create the copied record with the new Tax Rate
ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit,
"
& intTaxID & " AS taxid, " & _
"" & intObjID & " AS objectacctno, " &
TaxYN & " AS taxable;")

Else
intStartM = Forms!qryCopyLocObjGROUP.StartTaxM
intStartY = Forms!qryCopyLocObjGROUP.StartTaxY
'create the copied record with the new Tax Rate
ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, starttaxm, starttaxy, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit,
"
& intTaxID & " AS taxid, " & intStartM & " AS " & _
"starttaxm, " & intStartY & " AS
starttaxy, " & intObjID & " AS objectacctno, " & TaxYN & " AS taxable;")

End If
rst2.MoveNext
If (rst2.EOF) = False Then
Forms!qryCopyLocObjGROUP.Bookmark =
rst2.Bookmark
End If
Loop
rst2.Close
DoCmd.Close acForm, "qryCopyLocObjGROUP"

Else
'there are LocationObjects for this tax
'check the next record
End If
End If
rst.MoveNext
If (rst.EOF) = False Then
Forms!frmWorkWithTax.Bookmark = rst.Bookmark
End If

Loop
rst.Close
Click to expand...


What do you mean "it stops"? Is there an error raise? If so, what is it?

Looking at your code, it seems to me you that you are probably going about
this the wrong way. You seem to be using the forms' recordsetclones
primarily to navigate sequentially through the forms, and you're opening and
closing form "qryCopyLocObjGROUP" over and over again. Is there something
on the forms that you can't get by opening a query alone? This kind of
record-by-record processing can often be handled by a single, properly
contstructed update or append query. Even if it can't, and you need to loop
through a recordset, you usually don't need to use a form to do it. And if
you do need to use a form's records, you do not normally need to use the
recordsetclone just to loop through them.

As an aside, you should not ever close the form's recordsetclone. You
shouldn't be closing either rst or rst2. It may be worthwhile explicitly
setting rst2 to Nothing between iterations of the outer loop, though
technically that should not be necessary.
 
Thanks Dirk,

Actually, I've always known that I was probably not going about the
Recordset looping the most efficient way, however, I haven't had any examples
to go by -- and even less time to go out and find out how to do it the most
efficient way.

I quite often use update and append queries and know how to use them well,
however all of these instances need to loop through records. I have queries
attached to all these forms of course, so do I just use the recordsetclone
for the queries? Or how do I loop through those records? I'm sure that just
a little explanation will work as I understand the concept but again, have
not seen an example that made sense that I could modify to work for my
situation.

Any time that you have to show me a thing or two would be great! I'm all
for efficiency and I really do like to try to do code the "right" way...

Dirk Goldgar said:
ThriftyFinanceGirl said:
Well that's what I thought, but it stops... (at the rst.movenext) Can you
see it?

Set rst = Forms!frmWorkWithTax.RecordsetClone

Do While Not rst.EOF ' Iterate through each element.
dblBusUnit = Forms!frmWorkWithTax!BusUnit
intTaxID = rst!TaxID
varExFrRecon = rst!ExcludeFromRecon
'check the length of TaxID
If Len(Nz(intTaxID, "")) <> 0 Then
strWhere3 = "WHERE ([LocationObjects]![TaxID] = " &
intTaxID & ")"
strWhere4 = "WHERE ([TaxRatesBases]![TaxID] = " &
intTaxID & ")"
End If

'if the tax is marked "exclude from Recon" skip all this
below
If varExFrRecon = False Then
If DCount("Taxable", "LocationObjects", "TaxID = " &
intTaxID) < 1 Then

'there are no LocationObjects for this tax
DoCmd.OpenForm "qryCopyLocObjGROUP", , , "[taxid] =
" & CopyTaxID & ""
Set rst2 = Forms!qryCopyLocObjGROUP.RecordsetClone

Do While Not rst2.EOF ' Iterate through each
element.
intObjID = Forms!qryCopyLocObjGROUP.ObjectAcctNo
TaxYN = Forms!qryCopyLocObjGROUP.Taxable

If IsNull(Forms!qryCopyLocObjGROUP.StartTaxM) Then
intStartM = 0
intStartY = 0
'create the copied record with the new Tax Rate
ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit,
"
& intTaxID & " AS taxid, " & _
"" & intObjID & " AS objectacctno, " &
TaxYN & " AS taxable;")

Else
intStartM = Forms!qryCopyLocObjGROUP.StartTaxM
intStartY = Forms!qryCopyLocObjGROUP.StartTaxY
'create the copied record with the new Tax Rate
ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, starttaxm, starttaxy, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit,
"
& intTaxID & " AS taxid, " & intStartM & " AS " & _
"starttaxm, " & intStartY & " AS
starttaxy, " & intObjID & " AS objectacctno, " & TaxYN & " AS taxable;")

End If
rst2.MoveNext
If (rst2.EOF) = False Then
Forms!qryCopyLocObjGROUP.Bookmark =
rst2.Bookmark
End If
Loop
rst2.Close
DoCmd.Close acForm, "qryCopyLocObjGROUP"

Else
'there are LocationObjects for this tax
'check the next record
End If
End If
rst.MoveNext
If (rst.EOF) = False Then
Forms!frmWorkWithTax.Bookmark = rst.Bookmark
End If

Loop
rst.Close
Click to expand...


What do you mean "it stops"? Is there an error raise? If so, what is it?

Looking at your code, it seems to me you that you are probably going about
this the wrong way. You seem to be using the forms' recordsetclones
primarily to navigate sequentially through the forms, and you're opening and
closing form "qryCopyLocObjGROUP" over and over again. Is there something
on the forms that you can't get by opening a query alone? This kind of
record-by-record processing can often be handled by a single, properly
contstructed update or append query. Even if it can't, and you need to loop
through a recordset, you usually don't need to use a form to do it. And if
you do need to use a form's records, you do not normally need to use the
recordsetclone just to loop through them.

As an aside, you should not ever close the form's recordsetclone. You
shouldn't be closing either rst or rst2. It may be worthwhile explicitly
setting rst2 to Nothing between iterations of the outer loop, though
technically that should not be necessary.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Your problem may be caused by having a reference to ADO and DAO. Both provide
the same 'MoveNext' method and they are coded the same:

ADO: rst.MoveNext
DAO: rst.MoveNext

However, Access can get confused if you have a reference to both in your
code so it is a good idea to include one or the other. Open the VBA editor
and select Tools > References. Uncheck the reference for either Microsoft DAO
3.x Object Library or Microsoft ActiveX Data Objects 2.x Library. DAO is the
native object model for manipulating databases.

Also, when you dimension your recordsets (you can have multiple), it is best
to further disambiguate DAO vs. ADO by using:

Dim rst as DAO.Recordset

Good luck!

Regards,

Paul



ThriftyFinanceGirl said:
Thanks Dirk,

Actually, I've always known that I was probably not going about the
Recordset looping the most efficient way, however, I haven't had any examples
to go by -- and even less time to go out and find out how to do it the most
efficient way.

I quite often use update and append queries and know how to use them well,
however all of these instances need to loop through records. I have queries
attached to all these forms of course, so do I just use the recordsetclone
for the queries? Or how do I loop through those records? I'm sure that just
a little explanation will work as I understand the concept but again, have
not seen an example that made sense that I could modify to work for my
situation.

Any time that you have to show me a thing or two would be great! I'm all
for efficiency and I really do like to try to do code the "right" way...

Dirk Goldgar said:
ThriftyFinanceGirl said:
Well that's what I thought, but it stops... (at the rst.movenext) Can you
see it?

Set rst = Forms!frmWorkWithTax.RecordsetClone

Do While Not rst.EOF ' Iterate through each element.
dblBusUnit = Forms!frmWorkWithTax!BusUnit
intTaxID = rst!TaxID
varExFrRecon = rst!ExcludeFromRecon
'check the length of TaxID
If Len(Nz(intTaxID, "")) <> 0 Then
strWhere3 = "WHERE ([LocationObjects]![TaxID] = " &
intTaxID & ")"
strWhere4 = "WHERE ([TaxRatesBases]![TaxID] = " &
intTaxID & ")"
End If

'if the tax is marked "exclude from Recon" skip all this
below
If varExFrRecon = False Then
If DCount("Taxable", "LocationObjects", "TaxID = " &
intTaxID) < 1 Then

'there are no LocationObjects for this tax
DoCmd.OpenForm "qryCopyLocObjGROUP", , , "[taxid] =
" & CopyTaxID & ""
Set rst2 = Forms!qryCopyLocObjGROUP.RecordsetClone

Do While Not rst2.EOF ' Iterate through each
element.
intObjID = Forms!qryCopyLocObjGROUP.ObjectAcctNo
TaxYN = Forms!qryCopyLocObjGROUP.Taxable

If IsNull(Forms!qryCopyLocObjGROUP.StartTaxM) Then
intStartM = 0
intStartY = 0
'create the copied record with the new Tax Rate
ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit,
"
& intTaxID & " AS taxid, " & _
"" & intObjID & " AS objectacctno, " &
TaxYN & " AS taxable;")

Else
intStartM = Forms!qryCopyLocObjGROUP.StartTaxM
intStartY = Forms!qryCopyLocObjGROUP.StartTaxY
'create the copied record with the new Tax Rate
ID
DoCmd.RunSQL ("INSERT INTO LocationObjects
(busunit, taxid, starttaxm, starttaxy, objectacctno, taxable) " & _
"SELECT " & dblBusUnit & " AS busunit,
"
& intTaxID & " AS taxid, " & intStartM & " AS " & _
"starttaxm, " & intStartY & " AS
starttaxy, " & intObjID & " AS objectacctno, " & TaxYN & " AS taxable;")

End If
rst2.MoveNext
If (rst2.EOF) = False Then
Forms!qryCopyLocObjGROUP.Bookmark =
rst2.Bookmark
End If
Loop
rst2.Close
DoCmd.Close acForm, "qryCopyLocObjGROUP"

Else
'there are LocationObjects for this tax
'check the next record
End If
End If
rst.MoveNext
If (rst.EOF) = False Then
Forms!frmWorkWithTax.Bookmark = rst.Bookmark
End If

Loop
rst.Close
Click to expand...


What do you mean "it stops"? Is there an error raise? If so, what is it?

Looking at your code, it seems to me you that you are probably going about
this the wrong way. You seem to be using the forms' recordsetclones
primarily to navigate sequentially through the forms, and you're opening and
closing form "qryCopyLocObjGROUP" over and over again. Is there something
on the forms that you can't get by opening a query alone? This kind of
record-by-record processing can often be handled by a single, properly
contstructed update or append query. Even if it can't, and you need to loop
through a recordset, you usually don't need to use a form to do it. And if
you do need to use a form's records, you do not normally need to use the
recordsetclone just to loop through them.

As an aside, you should not ever close the form's recordsetclone. You
shouldn't be closing either rst or rst2. It may be worthwhile explicitly
setting rst2 to Nothing between iterations of the outer loop, though
technically that should not be necessary.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Back
Top