Accessing Recordsets across procedures

[convexcube]

Hi Experts,

I have a unbound form which records details of complaints. It also records what products are affected in the complaint. These are stored in a table named ComplaintProducts that has the fields: ComplaintProductsIndex (Autonumber), ComplaintIndex & ProductName.

The product list is quite lengthy, so I have set up a separate form which opens when the Products Affected list box on the Complaints form is double clicked, containing a cascading list box method to choose the Product. When the product in the Products list box is double clicked, it is added to ComplaintProducts table. Double clicking on the Products Affected list box in this form deletes it from the table. This is all done through recordsets.

I want the ability to edit an existing record if necessary and this system provides that. I also want the user to be able to cancel this edit procedure if a mistake has been made, so on this form I have a cancel button. I would like the cancel button to disregard any changes made to the table while the form has been open and then close it.

In order to do this, I have set up on the Form Open procedure a recordset called rstProductsExisting to capture the products if any that are already there.
Then on the Cancel Button event I have placed code that deletes all the records that apply to the relevant complaint and then adds the records from rstProductsExisting back in.

Here is the code I have:

Private Sub Form_Open(Cancel As Integer)
    Dim rstProductsExisting As Recordset
    Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
End Sub
 
Private Sub cmdCancel_Click()
    Dim rstProductsCancel As Recordset
    Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
    If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
        Do While Not rstProductsCancel.EOF
            rstProductsCancel.Delete
            rstProductsCancel.MoveNext
        Loop
        If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
            Do While Not rstProductsExisting.EOF
                rstProductsCancel.AddNew
                rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
                rstProductsCancel!ProductName = rstProductsExisting!ProductName
                rstProductsCancel.MoveNext
                rstProductsCancel.Update
            Loop
                rstProductsExisting.Close
            Set rstProductsExisting = Nothing
            rstProductsCancel.Close
        Set rstProductsCancel = Nothing
    DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
End Sub

The cancel event when run tells me "compile error: variable not defined" highlighting "rstProductsExisting". So it seems you can't keep a recordset open across procedures.

I have read this article about persisting a recordset which would seem to be a solution. Before I implement this though, I thought I would ask if there is a simpler and/or better way to achieve what I want.

The other question I have is when I delete the records in rstProductsCancel, would that also remove the records from rstProductsExisting. My understanding of recordsets is that they create a copy of the information, rather than a reference to it, though I fear I'm wrong.

As you can probably tell, I have pretty limited knowledge of recordsets (I am learning more every day) and I would be grateful for any help or other ideas I can get.

Thanks in anticipation,

Kind Regards,
Ken.

[ADezii]

Hi Experts,

I have a unbound form which records details of complaints. It also records what products are affected in the complaint. These are stored in a table named ComplaintProducts that has the fields: ComplaintProductsIndex (Autonumber), ComplaintIndex & ProductName.

The product list is quite lengthy, so I have set up a separate form which opens when the Products Affected list box on the Complaints form is double clicked, containing a cascading list box method to choose the Product. When the product in the Products list box is double clicked, it is added to ComplaintProducts table. Double clicking on the Products Affected list box in this form deletes it from the table. This is all done through recordsets.

I want the ability to edit an existing record if necessary and this system provides that. I also want the user to be able to cancel this edit procedure if a mistake has been made, so on this form I have a cancel button. I would like the cancel button to disregard any changes made to the table while the form has been open and then close it.

In order to do this, I have set up on the Form Open procedure a recordset called rstProductsExisting to capture the products if any that are already there.
Then on the Cancel Button event I have placed code that deletes all the records that apply to the relevant complaint and then adds the records from rstProductsExisting back in.

Here is the code I have:

Private Sub Form_Open(Cancel As Integer)
    Dim rstProductsExisting As Recordset
    Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
End Sub
 
Private Sub cmdCancel_Click()
    Dim rstProductsCancel As Recordset
    Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
    If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
        Do While Not rstProductsCancel.EOF
            rstProductsCancel.Delete
            rstProductsCancel.MoveNext
        Loop
        If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
            Do While Not rstProductsExisting.EOF
                rstProductsCancel.AddNew
                rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
                rstProductsCancel!ProductName = rstProductsExisting!ProductName
                rstProductsCancel.MoveNext
                rstProductsCancel.Update
            Loop
                rstProductsExisting.Close
            Set rstProductsExisting = Nothing
            rstProductsCancel.Close
        Set rstProductsCancel = Nothing
    DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
End Sub

The cancel event when run tells me "compile error: variable not defined" highlighting "rstProductsExisting". So it seems you can't keep a recordset open across procedures.

I have read this article about persisting a recordset which would seem to be a solution. Before I implement this though, I thought I would ask if there is a simpler and/or better way to achieve what I want.

The other question I have is when I delete the records in rstProductsCancel, would that also remove the records from rstProductsExisting. My understanding of recordsets is that they create a copy of the information, rather than a reference to it, though I fear I'm wrong.

As you can probably tell, I have pretty limited knowledge of recordsets (I am learning more every day) and I would be grateful for any help or other ideas I can get.

Thanks in anticipation,

Kind Regards,
Ken.

Look no further than Line #2 as far as the Error is concerned. The Object Variable rstProductsExisting is Declared within the Form's Open() Event, and thus has only Procedural Level Scope. Once the Open() Event has finished executing, this Variable goes out of Scope, has no existence, and to reference it is meaningless. Make the Declaration in the Form's Declarations Section as in:

Dim rstProductsExisting As DAO.Recordset
                 OR
Dim rstProductsExisting As ADODB.Recordset
 

It can still point to a Recordset Object in the Form's Open() Event as in:

Private Sub Form_Open(Cancel As Integer)
Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
End Sub

This should eliminate the Runtime Error.

[convexcube]

Thanks for the speedy reply ADezii,

If I understand you correctly, you are saying to place either of these:

Dim rstProductsExisting As DAO.Recordset
                 OR
Dim rstProductsExisting As ADODB.Recordset
 

at the top of the modules code right after:

Option Compare Database
Option Explicit

After doing this and altering the Form Open Event as you suggested I am getting a different error (although it seems very similar):

Runtime Error 91: Object variable or With block variable not set.

Not sure where to go from here.

Kind regards,
Ken.

[convexcube]

Hi ADezii,

I have changed the Form Open Event to a Form Load Event and Error 91 no longer shows. However, when clicking on the Cancel button I get another error:

3167: record is deleted

occuring at line 12:

Private Sub cmdCancel_Click()
    Dim rstProductsCancel As Recordset
    Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
    If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
        Do While Not rstProductsCancel.EOF
            rstProductsCancel.Delete
            rstProductsCancel.MoveNext
        Loop
        If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
            Do While Not rstProductsExisting.EOF
                rstProductsCancel.AddNew
                rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
                rstProductsCancel!ProductName = rstProductsExisting!ProductName
                rstProductsCancel.MoveNext
                rstProductsCancel.Update
            Loop
                rstProductsExisting.Close
            Set rstProductsExisting = Nothing
            rstProductsCancel.Close
        Set rstProductsCancel = Nothing
    DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
End Sub

Which would seem to confirm my fears about the recordset being a reference rather than a copy. Am I right? What further action would you suggest?

Kind regards,
Ken

[ADezii]

Hi ADezii,

I have changed the Form Open Event to a Form Load Event and Error 91 no longer shows. However, when clicking on the Cancel button I get another error:

3167: record is deleted

occuring at line 12:

Private Sub cmdCancel_Click()
    Dim rstProductsCancel As Recordset
    Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
    If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
        Do While Not rstProductsCancel.EOF
            rstProductsCancel.Delete
            rstProductsCancel.MoveNext
        Loop
        If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
            Do While Not rstProductsExisting.EOF
                rstProductsCancel.AddNew
                rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
                rstProductsCancel!ProductName = rstProductsExisting!ProductName
                rstProductsCancel.MoveNext
                rstProductsCancel.Update
            Loop
                rstProductsExisting.Close
            Set rstProductsExisting = Nothing
            rstProductsCancel.Close
        Set rstProductsCancel = Nothing
    DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
End Sub

Which would seem to confirm my fears about the recordset being a reference rather than a copy. Am I right? What further action would you suggest?

Kind regards,
Ken

Hello Ken, in Lines 5 to 8 you are Deleting Records from Complaint Products which contain a specific Complaint Index. In Lines 10 to 16, should the User press the Cancel Key, you are trying to Restore Records from rstProductsExisting to the rstProductsCancel Recordset. The problem is that the Records referred to by rstProductsExisting were already Deleted in the earlier loop essentially making it an Empty Recordset. I feel as though the basic problem here is your conception of Recordsets. Assuming that a Recordset is Updateable, any Additions/Deletions/Modifications made within the context of the Recordset are also reflected in its Data Source. If two Recordsets point to exactly the same Data Set, and Records are Delected by the 1st Recordset, they cannot be restored by the 2nd. I hope I am making this concept clear.

[convexcube]

Hi ADezii,

Yes, you have now made it clear to me that what I feared is correct. I had hoped that the records may be held in memory like a clipboard, but alas they are not.

I can see a few ways to deal with this scenario:

1. Create a holding table with the same structure to hold the records until the user presses cancel or closes the form.
2. Copy the records to variables in vba and restore them to the table when the user clicks cancel.
3. Use the records in the rstProductsExisting to filter them out of the rstProductsCancel Delete process.

What do you think would be the best route to take, or do you have another suggestion?

Thank you so much for your time.

Kind Regards,
Ken.

[ADezii]

Hi ADezii,

Yes, you have now made it clear to me that what I feared is correct. I had hoped that the records may be held in memory like a clipboard, but alas they are not.

I can see a few ways to deal with this scenario:

1. Create a holding table with the same structure to hold the records until the user presses cancel or closes the form.
2. Copy the records to variables in vba and restore them to the table when the user clicks cancel.
3. Use the records in the rstProductsExisting to filter them out of the rstProductsCancel Delete process.

What do you think would be the best route to take, or do you have another suggestion?

Thank you so much for your time.

Kind Regards,
Ken.

Too close to bedtime now, but myself or someone else will surely get back to you. Can't think straight when I'm tired (LOL). Good night.

[convexcube]

No worries ADezii,

I'm not in a hurry so take your time anyway.

Take care,
Ken.

[zachster17]

Ok, here is what I am assuming:

-When the user hits cancel you want to change the record back to the values it was before the user started making changes


I pasted code below that might work if you like the method. My reccemendation is to add a variant varaible to the module and put all the records of the product/complaint index into an array first. Then, if the user presses cancel it can just use the array created on load to add the values back. You wouldn't need to have the rstProductsExisting declared form-wide using this method.

Option Compare Database
Dim rstProductsExisting As DAO.Recordset
Dim arrCancel As Variant 'array object to hold previous values
Private Sub Form_Load()
 
    Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
 
    'get all the values of the cancel entries for the product on loading (for that complaints id)
 
    arrCancel = ""
 
    Do While Not rstProductsExisting.EOF
        arrCancel = arrCancel & rstProductsExisting!ProductName & ";" 'get all values that are in the table into a string
        rstProductsExisting.MoveNext
    Loop
 
   arrCancel = Left(arrCancel, Len(arrCancel) - 1) 'get rid of the extra ';' at the end
    arrCancel = Split(arrCancel, ";") 'convert string into array
 
 
 
End Sub
Private Sub cmdCancel_Click()
 
    Dim rstProductsCancel As Recordset
    Dim intCounter As Integer 'counter variable
 
    Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
 
    If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
 
    Do While Not rstProductsCancel.EOF
        rstProductsCancel.Delete
    rstProductsCancel.MoveNext
    Loop
 
    For intCounter = 0 To UBound(arrCancel)
 
        rstProductsCancel.AddNew
        rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value
        rstProductsCancel!ProductName = arrCancel(intCounter) 'add values in order from the array
        rstProductsCancel.Update
 
    Next
 
 
    rstProductsExisting.Close
    Set rstProductsExisting = Nothing
    rstProductsCancel.Close
    Set rstProductsCancel = Nothing
    DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
 
End Sub
 

Sorry for changing the tabbing in the code; I can only write code doing it like that--it's a flaw haha.

Let me know if this works!

Thanks,

Zach

[convexcube]

Thanks Zach,

This is exactly what I want to achieve, and its the solution I'd most prefer to use as well. I understand all the code too. I'll let you know if I have any problems.

Once again, thanks for taking the time.

Hope you and your family have a great Christmas and holiday season,

Kind Regards,
Ken.

[convexcube]

Hi Zach,

I have implemented your code and it works great. I just had to add some lines of code to deal with the case where there are no records in the recordset. I'll place them here in case anyone needs to do this.

In the Form Load Event:

If Not arrCancel = "" Then
    arrCancel = Left(arrCancel, Len(arrCancel) - 1)
    arrCancel = Split(arrCancel, ";")
End If

In the Cancel Click Event

If IsArray(arrCancel) = True Then
    For intCounter = 0 To UBound(arrCancel)
        rstEmployeesCancel.AddNew
        rstEmployeesCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex
        rstEmployeesCancel!EmployeeName = arrCancel(intCounter)
        rstEmployeesCancel.Update
    Next
End If

Where Zach had lines 2 & 3 in the Form Load Event and lines 2-7 in the Cancel Click Event, I placed the If statement around it so these lines are not executed (which cause errors in the case that the recordset was empty).

Kind Regards,
Ken.

[ADezii]

Ok, here is what I am assuming:

-When the user hits cancel you want to change the record back to the values it was before the user started making changes


I pasted code below that might work if you like the method. My reccemendation is to add a variant varaible to the module and put all the records of the product/complaint index into an array first. Then, if the user presses cancel it can just use the array created on load to add the values back. You wouldn't need to have the rstProductsExisting declared form-wide using this method.

Option Compare Database
Dim rstProductsExisting As DAO.Recordset
Dim arrCancel As Variant 'array object to hold previous values
Private Sub Form_Load()
 
    Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
 
    'get all the values of the cancel entries for the product on loading (for that complaints id)
 
    arrCancel = ""
 
    Do While Not rstProductsExisting.EOF
        arrCancel = arrCancel & rstProductsExisting!ProductName & ";" 'get all values that are in the table into a string
        rstProductsExisting.MoveNext
    Loop
 
   arrCancel = Left(arrCancel, Len(arrCancel) - 1) 'get rid of the extra ';' at the end
    arrCancel = Split(arrCancel, ";") 'convert string into array
 
 
 
End Sub
Private Sub cmdCancel_Click()
 
    Dim rstProductsCancel As Recordset
    Dim intCounter As Integer 'counter variable
 
    Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
 
    If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
 
    Do While Not rstProductsCancel.EOF
        rstProductsCancel.Delete
    rstProductsCancel.MoveNext
    Loop
 
    For intCounter = 0 To UBound(arrCancel)
 
        rstProductsCancel.AddNew
        rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value
        rstProductsCancel!ProductName = arrCancel(intCounter) 'add values in order from the array
        rstProductsCancel.Update
 
    Next
 
 
    rstProductsExisting.Close
    Set rstProductsExisting = Nothing
    rstProductsCancel.Close
    Set rstProductsCancel = Nothing
    DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
 
End Sub
 

Sorry for changing the tabbing in the code; I can only write code doing it like that--it's a flaw haha.

Let me know if this works!

Thanks,

Zach

Nice approach, but you will probably not be able to use the Form's value in the current context since the Current Record will have been Deleted and the Record Pointer not moved:

rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value

[zachster17]

Nice approach, but you will probably not be able to use the Form's value in the current context since the Current Record will have been Deleted and the Record Pointer not moved:

rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value

If that's the case, then I would add another form-wide variable at the top under Option Compare Database and set it equal to Forms!Main!Complaints.Form!txtComplaintIndex in the Form Load event. That would probably get around that.

Zach