Temporary recordsets

C

CharlesD

Hi,

I have the following code listed below, where I was trying to create four
temporary recordsets, the last two recordsets based on the first two
temporary recordsets created.
All works fine for the first two recordsets with the proper recordset counts
and all. When I try to reference the just created
recordset, I get an error message saying the recordset does not exist or I
have misspelled the name. I do not understand why I
cannot reference the recordset in the statement below:

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
and the StrSQL_Payments_To_Delete string references a just created recordset?

Any help would be appreciated.

Regards,

CharlesD

Private Sub cmdDelete_Click()
Dim rstCharges_Paid_In_Full, rstCharges_To_Delete,
rstPayments_FULLY_APPLYED, rstPayments_To_Delete As Recordset
Dim dbs As Database
Dim strCriteria, StrSQL_Charges, StrSQL_Charges_To_Delete, StrSQL_Payments,
StrSQL_Payments_To_Delete, strQuote As String
strQuote = Chr$(34)

On Error GoTo ErrorHandler

Set dbs = CurrentDb
StrSQL_Charges = "SELECT tblCHARGES.apkCHARGES AS fpkCharges,
tblRECEIPTS.apkRECEIPTS as fpkReceipts, tblRECEIPTS.fpkPAYMENT as fpkPayment
"
StrSQL_Charges = StrSQL_Charges & " FROM tblRECEIPTS RIGHT JOIN tblCHARGES
ON tblRECEIPTS.fpkCHARGES = tblCHARGES.apkCHARGES "
StrSQL_Charges = StrSQL_Charges & " WHERE
(((tblCHARGES.AMOUNT)=(tblCHARGES.PAID_AMT)));"

Set rstCharges_Paid_In_Full = dbs.OpenRecordset(StrSQL_Charges,
dbOpenDynaset, dbSeeChanges)
rstCharges_Paid_In_Full.MoveLast
rstCharges_Paid_In_Full.MoveFirst

StrSQL_Payments = "SELECT tblPAYMENT.apkPAYMENT as fpkPayment,
tblRECEIPTS.apkRECEIPTS as fpkReceipts , tblRECEIPTS.fpkCHARGES as fpkCharges
"
StrSQL_Payments = StrSQL_Payments & " FROM tblRECEIPTS INNER JOIN tblPAYMENT
ON tblRECEIPTS.fpkPAYMENT = tblPAYMENT.apkPAYMENT "
StrSQL_Payments = StrSQL_Payments & " WHERE
(((tblPAYMENT.AMOUNT)=[tblPAYMENT]![APPLYD_AMT]));"

Set rstPayments_FULLY_APPLYED = dbs.OpenRecordset(StrSQL_Payments,
dbOpenDynaset, dbSeeChanges)
rstPayments_FULLY_APPLYED.MoveLast
rstPayments_FULLY_APPLYED.MoveFirst

StrSQL_Payments_To_Delete = "SELECT rstPayments_FULLY_APPLYED.fpkPAYMENT as
fpkPayment FROM rstPayments_FULLY_APPLYED ;"

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
rstPayments_To_Delete.MoveLast
rstPayments_To_Delete.MoveFirst

' StrSQL = "DELETE FROM tblPAYMENT Where (tblPAYMENT.[apkPAYMENT] = " &
rstPayments_To_Delete.[fpkPayment]

Exit_cmdDelete_Click:
rstCharges_Paid_In_Full.Close
rstPayments_FULLY_APPLYED.Close
rstPayments_To_Delete.Close
Set dbs = Nothing
Set rstCharges_Paid_In_Full = Nothing
Set rstPayments_FULLY_APPLYED = Nothing
Set rstPayments_To_Delete = Nothing
Exit Sub

ErrorHandler:
MsgBox LTrim(RTrim(Me.NAME)) + "." + "Delete Charges and Payments -" +
"Error:" + AccessError(Err.Number)
GoTo Exit_cmdDelete_Click

End Sub
 
T

Tom van Stiphout

On Wed, 10 Mar 2010 20:08:01 -0800, CharlesD

Recordsets have to be based on tables and/or queries. They cannot
refer to other recordsets.
So "select * from myRecordset" is not allowed.

-Tom.
Microsoft Access MVP

Hi,

I have the following code listed below, where I was trying to create four
temporary recordsets, the last two recordsets based on the first two
temporary recordsets created.
All works fine for the first two recordsets with the proper recordset counts
and all. When I try to reference the just created
recordset, I get an error message saying the recordset does not exist or I
have misspelled the name. I do not understand why I
cannot reference the recordset in the statement below:

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
and the StrSQL_Payments_To_Delete string references a just created recordset?

Any help would be appreciated.

Regards,

CharlesD

Private Sub cmdDelete_Click()
Dim rstCharges_Paid_In_Full, rstCharges_To_Delete,
rstPayments_FULLY_APPLYED, rstPayments_To_Delete As Recordset
Dim dbs As Database
Dim strCriteria, StrSQL_Charges, StrSQL_Charges_To_Delete, StrSQL_Payments,
StrSQL_Payments_To_Delete, strQuote As String
strQuote = Chr$(34)

On Error GoTo ErrorHandler

Set dbs = CurrentDb
StrSQL_Charges = "SELECT tblCHARGES.apkCHARGES AS fpkCharges,
tblRECEIPTS.apkRECEIPTS as fpkReceipts, tblRECEIPTS.fpkPAYMENT as fpkPayment
"
StrSQL_Charges = StrSQL_Charges & " FROM tblRECEIPTS RIGHT JOIN tblCHARGES
ON tblRECEIPTS.fpkCHARGES = tblCHARGES.apkCHARGES "
StrSQL_Charges = StrSQL_Charges & " WHERE
(((tblCHARGES.AMOUNT)=(tblCHARGES.PAID_AMT)));"

Set rstCharges_Paid_In_Full = dbs.OpenRecordset(StrSQL_Charges,
dbOpenDynaset, dbSeeChanges)
rstCharges_Paid_In_Full.MoveLast
rstCharges_Paid_In_Full.MoveFirst

StrSQL_Payments = "SELECT tblPAYMENT.apkPAYMENT as fpkPayment,
tblRECEIPTS.apkRECEIPTS as fpkReceipts , tblRECEIPTS.fpkCHARGES as fpkCharges
"
StrSQL_Payments = StrSQL_Payments & " FROM tblRECEIPTS INNER JOIN tblPAYMENT
ON tblRECEIPTS.fpkPAYMENT = tblPAYMENT.apkPAYMENT "
StrSQL_Payments = StrSQL_Payments & " WHERE
(((tblPAYMENT.AMOUNT)=[tblPAYMENT]![APPLYD_AMT]));"

Set rstPayments_FULLY_APPLYED = dbs.OpenRecordset(StrSQL_Payments,
dbOpenDynaset, dbSeeChanges)
rstPayments_FULLY_APPLYED.MoveLast
rstPayments_FULLY_APPLYED.MoveFirst

StrSQL_Payments_To_Delete = "SELECT rstPayments_FULLY_APPLYED.fpkPAYMENT as
fpkPayment FROM rstPayments_FULLY_APPLYED ;"

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
rstPayments_To_Delete.MoveLast
rstPayments_To_Delete.MoveFirst

' StrSQL = "DELETE FROM tblPAYMENT Where (tblPAYMENT.[apkPAYMENT] = " &
rstPayments_To_Delete.[fpkPayment]

Exit_cmdDelete_Click:
rstCharges_Paid_In_Full.Close
rstPayments_FULLY_APPLYED.Close
rstPayments_To_Delete.Close
Set dbs = Nothing
Set rstCharges_Paid_In_Full = Nothing
Set rstPayments_FULLY_APPLYED = Nothing
Set rstPayments_To_Delete = Nothing
Exit Sub

ErrorHandler:
MsgBox LTrim(RTrim(Me.NAME)) + "." + "Delete Charges and Payments -" +
"Error:" + AccessError(Err.Number)
GoTo Exit_cmdDelete_Click

End Sub
Click to expand...
 
M

Marshall Barton

CharlesD said:
I have the following code listed below, where I was trying to create four
temporary recordsets, the last two recordsets based on the first two
temporary recordsets created.
All works fine for the first two recordsets with the proper recordset counts
and all. When I try to reference the just created
recordset, I get an error message saying the recordset does not exist or I
have misspelled the name. I do not understand why I
cannot reference the recordset in the statement below:

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
and the StrSQL_Payments_To_Delete string references a just created recordset?


Private Sub cmdDelete_Click()
Dim rstCharges_Paid_In_Full, rstCharges_To_Delete,
rstPayments_FULLY_APPLYED, rstPayments_To_Delete As Recordset
Dim dbs As Database
Dim strCriteria, StrSQL_Charges, StrSQL_Charges_To_Delete, StrSQL_Payments,
StrSQL_Payments_To_Delete, strQuote As String
strQuote = Chr$(34)

On Error GoTo ErrorHandler

Set dbs = CurrentDb
StrSQL_Charges = "SELECT tblCHARGES.apkCHARGES AS fpkCharges,
tblRECEIPTS.apkRECEIPTS as fpkReceipts, tblRECEIPTS.fpkPAYMENT as fpkPayment
"
StrSQL_Charges = StrSQL_Charges & " FROM tblRECEIPTS RIGHT JOIN tblCHARGES
ON tblRECEIPTS.fpkCHARGES = tblCHARGES.apkCHARGES "
StrSQL_Charges = StrSQL_Charges & " WHERE
(((tblCHARGES.AMOUNT)=(tblCHARGES.PAID_AMT)));"

Set rstCharges_Paid_In_Full = dbs.OpenRecordset(StrSQL_Charges,
dbOpenDynaset, dbSeeChanges)
rstCharges_Paid_In_Full.MoveLast
rstCharges_Paid_In_Full.MoveFirst

StrSQL_Payments = "SELECT tblPAYMENT.apkPAYMENT as fpkPayment,
tblRECEIPTS.apkRECEIPTS as fpkReceipts , tblRECEIPTS.fpkCHARGES as fpkCharges
"
StrSQL_Payments = StrSQL_Payments & " FROM tblRECEIPTS INNER JOIN tblPAYMENT
ON tblRECEIPTS.fpkPAYMENT = tblPAYMENT.apkPAYMENT "
StrSQL_Payments = StrSQL_Payments & " WHERE
(((tblPAYMENT.AMOUNT)=[tblPAYMENT]![APPLYD_AMT]));"

Set rstPayments_FULLY_APPLYED = dbs.OpenRecordset(StrSQL_Payments,
dbOpenDynaset, dbSeeChanges)
rstPayments_FULLY_APPLYED.MoveLast
rstPayments_FULLY_APPLYED.MoveFirst

StrSQL_Payments_To_Delete = "SELECT rstPayments_FULLY_APPLYED.fpkPAYMENT as
fpkPayment FROM rstPayments_FULLY_APPLYED ;"

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
rstPayments_To_Delete.MoveLast
rstPayments_To_Delete.MoveFirst

' StrSQL = "DELETE FROM tblPAYMENT Where (tblPAYMENT.[apkPAYMENT] = " &
rstPayments_To_Delete.[fpkPayment]

Exit_cmdDelete_Click:
rstCharges_Paid_In_Full.Close
rstPayments_FULLY_APPLYED.Close
rstPayments_To_Delete.Close
Set dbs = Nothing
Set rstCharges_Paid_In_Full = Nothing
Set rstPayments_FULLY_APPLYED = Nothing
Set rstPayments_To_Delete = Nothing
Exit Sub

ErrorHandler:
MsgBox LTrim(RTrim(Me.NAME)) + "." + "Delete Charges and Payments -" +
"Error:" + AccessError(Err.Number)
GoTo Exit_cmdDelete_Click

End Sub
Click to expand...


As Tim said, queries know nothing about recordsets.

AFAICS, you are not using the recordsets for anything so why
have you focused on using them?

Maybe all you need to do is refer to the first query in the
second query or combine the two queries into one query.
 
C

CharlesD

Hi Marsh and Tim,

Thanks for your replies. I see the issue and have queries written now that
work.

Regards,

CharlesD

Marshall Barton said:
CharlesD said:
I have the following code listed below, where I was trying to create four
temporary recordsets, the last two recordsets based on the first two
temporary recordsets created.
All works fine for the first two recordsets with the proper recordset counts
and all. When I try to reference the just created
recordset, I get an error message saying the recordset does not exist or I
have misspelled the name. I do not understand why I
cannot reference the recordset in the statement below:

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
and the StrSQL_Payments_To_Delete string references a just created recordset?


Private Sub cmdDelete_Click()
Dim rstCharges_Paid_In_Full, rstCharges_To_Delete,
rstPayments_FULLY_APPLYED, rstPayments_To_Delete As Recordset
Dim dbs As Database
Dim strCriteria, StrSQL_Charges, StrSQL_Charges_To_Delete, StrSQL_Payments,
StrSQL_Payments_To_Delete, strQuote As String
strQuote = Chr$(34)

On Error GoTo ErrorHandler

Set dbs = CurrentDb
StrSQL_Charges = "SELECT tblCHARGES.apkCHARGES AS fpkCharges,
tblRECEIPTS.apkRECEIPTS as fpkReceipts, tblRECEIPTS.fpkPAYMENT as fpkPayment
"
StrSQL_Charges = StrSQL_Charges & " FROM tblRECEIPTS RIGHT JOIN tblCHARGES
ON tblRECEIPTS.fpkCHARGES = tblCHARGES.apkCHARGES "
StrSQL_Charges = StrSQL_Charges & " WHERE
(((tblCHARGES.AMOUNT)=(tblCHARGES.PAID_AMT)));"

Set rstCharges_Paid_In_Full = dbs.OpenRecordset(StrSQL_Charges,
dbOpenDynaset, dbSeeChanges)
rstCharges_Paid_In_Full.MoveLast
rstCharges_Paid_In_Full.MoveFirst

StrSQL_Payments = "SELECT tblPAYMENT.apkPAYMENT as fpkPayment,
tblRECEIPTS.apkRECEIPTS as fpkReceipts , tblRECEIPTS.fpkCHARGES as fpkCharges
"
StrSQL_Payments = StrSQL_Payments & " FROM tblRECEIPTS INNER JOIN tblPAYMENT
ON tblRECEIPTS.fpkPAYMENT = tblPAYMENT.apkPAYMENT "
StrSQL_Payments = StrSQL_Payments & " WHERE
(((tblPAYMENT.AMOUNT)=[tblPAYMENT]![APPLYD_AMT]));"

Set rstPayments_FULLY_APPLYED = dbs.OpenRecordset(StrSQL_Payments,
dbOpenDynaset, dbSeeChanges)
rstPayments_FULLY_APPLYED.MoveLast
rstPayments_FULLY_APPLYED.MoveFirst

StrSQL_Payments_To_Delete = "SELECT rstPayments_FULLY_APPLYED.fpkPAYMENT as
fpkPayment FROM rstPayments_FULLY_APPLYED ;"

Set rstPayments_To_Delete = dbs.OpenRecordset(StrSQL_Payments_To_Delete,
dbOpenDynaset, dbSeeChanges) ' ERROR HAPPENS HERE
rstPayments_To_Delete.MoveLast
rstPayments_To_Delete.MoveFirst

' StrSQL = "DELETE FROM tblPAYMENT Where (tblPAYMENT.[apkPAYMENT] = " &
rstPayments_To_Delete.[fpkPayment]

Exit_cmdDelete_Click:
rstCharges_Paid_In_Full.Close
rstPayments_FULLY_APPLYED.Close
rstPayments_To_Delete.Close
Set dbs = Nothing
Set rstCharges_Paid_In_Full = Nothing
Set rstPayments_FULLY_APPLYED = Nothing
Set rstPayments_To_Delete = Nothing
Exit Sub

ErrorHandler:
MsgBox LTrim(RTrim(Me.NAME)) + "." + "Delete Charges and Payments -" +
"Error:" + AccessError(Err.Number)
GoTo Exit_cmdDelete_Click

End Sub
Click to expand...


As Tim said, queries know nothing about recordsets.

AFAICS, you are not using the recordsets for anything so why
have you focused on using them?

Maybe all you need to do is refer to the first query in the
second query or combine the two queries into one query.
Click to expand...
 
Top