By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,658 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,658 IT Pros & Developers. It's quick & easy.

Accessing Recordsets across procedures

convexcube
P: 47
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Dim rstProductsExisting As Recordset
  3.     Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  4. End Sub
  5.  
  6. Private Sub cmdCancel_Click()
  7.     Dim rstProductsCancel As Recordset
  8.     Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  9.     If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
  10.         Do While Not rstProductsCancel.EOF
  11.             rstProductsCancel.Delete
  12.             rstProductsCancel.MoveNext
  13.         Loop
  14.         If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
  15.             Do While Not rstProductsExisting.EOF
  16.                 rstProductsCancel.AddNew
  17.                 rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
  18.                 rstProductsCancel!ProductName = rstProductsExisting!ProductName
  19.                 rstProductsCancel.MoveNext
  20.                 rstProductsCancel.Update
  21.             Loop
  22.                 rstProductsExisting.Close
  23.             Set rstProductsExisting = Nothing
  24.             rstProductsCancel.Close
  25.         Set rstProductsCancel = Nothing
  26.     DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
  27. 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.
Dec 21 '07 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,634
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Dim rstProductsExisting As Recordset
  3.     Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  4. End Sub
  5.  
  6. Private Sub cmdCancel_Click()
  7.     Dim rstProductsCancel As Recordset
  8.     Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  9.     If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
  10.         Do While Not rstProductsCancel.EOF
  11.             rstProductsCancel.Delete
  12.             rstProductsCancel.MoveNext
  13.         Loop
  14.         If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
  15.             Do While Not rstProductsExisting.EOF
  16.                 rstProductsCancel.AddNew
  17.                 rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
  18.                 rstProductsCancel!ProductName = rstProductsExisting!ProductName
  19.                 rstProductsCancel.MoveNext
  20.                 rstProductsCancel.Update
  21.             Loop
  22.                 rstProductsExisting.Close
  23.             Set rstProductsExisting = Nothing
  24.             rstProductsCancel.Close
  25.         Set rstProductsCancel = Nothing
  26.     DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
  27. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim rstProductsExisting As DAO.Recordset
  2.                  OR
  3. Dim rstProductsExisting As ADODB.Recordset
  4.  
It can still point to a Recordset Object in the Form's Open() Event as in:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  3. End Sub
This should eliminate the Runtime Error.
Dec 22 '07 #2

convexcube
P: 47
Thanks for the speedy reply ADezii,

If I understand you correctly, you are saying to place either of these:
Expand|Select|Wrap|Line Numbers
  1. Dim rstProductsExisting As DAO.Recordset
  2.                  OR
  3. Dim rstProductsExisting As ADODB.Recordset
  4.  
at the top of the modules code right after:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 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.
Dec 22 '07 #3

convexcube
P: 47
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCancel_Click()
  2.     Dim rstProductsCancel As Recordset
  3.     Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  4.     If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
  5.         Do While Not rstProductsCancel.EOF
  6.             rstProductsCancel.Delete
  7.             rstProductsCancel.MoveNext
  8.         Loop
  9.         If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
  10.             Do While Not rstProductsExisting.EOF
  11.                 rstProductsCancel.AddNew
  12.                 rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
  13.                 rstProductsCancel!ProductName = rstProductsExisting!ProductName
  14.                 rstProductsCancel.MoveNext
  15.                 rstProductsCancel.Update
  16.             Loop
  17.                 rstProductsExisting.Close
  18.             Set rstProductsExisting = Nothing
  19.             rstProductsCancel.Close
  20.         Set rstProductsCancel = Nothing
  21.     DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
  22. 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
Dec 22 '07 #4

ADezii
Expert 5K+
P: 8,634
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCancel_Click()
  2.     Dim rstProductsCancel As Recordset
  3.     Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  4.     If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
  5.         Do While Not rstProductsCancel.EOF
  6.             rstProductsCancel.Delete
  7.             rstProductsCancel.MoveNext
  8.         Loop
  9.         If Not rstProductsExisting.EOF Then rstProductsExisting.MoveFirst
  10.             Do While Not rstProductsExisting.EOF
  11.                 rstProductsCancel.AddNew
  12.                 rstProductsCancel!ComplaintsIndex = rstProductsExisting!ComplaintsIndex
  13.                 rstProductsCancel!ProductName = rstProductsExisting!ProductName
  14.                 rstProductsCancel.MoveNext
  15.                 rstProductsCancel.Update
  16.             Loop
  17.                 rstProductsExisting.Close
  18.             Set rstProductsExisting = Nothing
  19.             rstProductsCancel.Close
  20.         Set rstProductsCancel = Nothing
  21.     DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
  22. 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.
Dec 22 '07 #5

convexcube
P: 47
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.
Dec 22 '07 #6

ADezii
Expert 5K+
P: 8,634
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.
Dec 22 '07 #7

convexcube
P: 47
No worries ADezii,

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

Take care,
Ken.
Dec 22 '07 #8

zachster17
P: 30
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim rstProductsExisting As DAO.Recordset
  3. Dim arrCancel As Variant 'array object to hold previous values
  4. Private Sub Form_Load()
  5.  
  6.     Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  7.  
  8.     'get all the values of the cancel entries for the product on loading (for that complaints id)
  9.  
  10.     arrCancel = ""
  11.  
  12.     Do While Not rstProductsExisting.EOF
  13.         arrCancel = arrCancel & rstProductsExisting!ProductName & ";" 'get all values that are in the table into a string
  14.         rstProductsExisting.MoveNext
  15.     Loop
  16.  
  17.    arrCancel = Left(arrCancel, Len(arrCancel) - 1) 'get rid of the extra ';' at the end
  18.     arrCancel = Split(arrCancel, ";") 'convert string into array
  19.  
  20.  
  21.  
  22. End Sub
  23. Private Sub cmdCancel_Click()
  24.  
  25.     Dim rstProductsCancel As Recordset
  26.     Dim intCounter As Integer 'counter variable
  27.  
  28.     Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  29.  
  30.     If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
  31.  
  32.     Do While Not rstProductsCancel.EOF
  33.         rstProductsCancel.Delete
  34.     rstProductsCancel.MoveNext
  35.     Loop
  36.  
  37.     For intCounter = 0 To UBound(arrCancel)
  38.  
  39.         rstProductsCancel.AddNew
  40.         rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value
  41.         rstProductsCancel!ProductName = arrCancel(intCounter) 'add values in order from the array
  42.         rstProductsCancel.Update
  43.  
  44.     Next
  45.  
  46.  
  47.     rstProductsExisting.Close
  48.     Set rstProductsExisting = Nothing
  49.     rstProductsCancel.Close
  50.     Set rstProductsCancel = Nothing
  51.     DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
  52.  
  53. End Sub
  54.  
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
Dec 22 '07 #9

convexcube
P: 47
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.
Dec 22 '07 #10

convexcube
P: 47
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:
Expand|Select|Wrap|Line Numbers
  1. If Not arrCancel = "" Then
  2.     arrCancel = Left(arrCancel, Len(arrCancel) - 1)
  3.     arrCancel = Split(arrCancel, ";")
  4. End If
In the Cancel Click Event
Expand|Select|Wrap|Line Numbers
  1. If IsArray(arrCancel) = True Then
  2.     For intCounter = 0 To UBound(arrCancel)
  3.         rstEmployeesCancel.AddNew
  4.         rstEmployeesCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex
  5.         rstEmployeesCancel!EmployeeName = arrCancel(intCounter)
  6.         rstEmployeesCancel.Update
  7.     Next
  8. 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.
Dec 22 '07 #11

ADezii
Expert 5K+
P: 8,634
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim rstProductsExisting As DAO.Recordset
  3. Dim arrCancel As Variant 'array object to hold previous values
  4. Private Sub Form_Load()
  5.  
  6.     Set rstProductsExisting = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  7.  
  8.     'get all the values of the cancel entries for the product on loading (for that complaints id)
  9.  
  10.     arrCancel = ""
  11.  
  12.     Do While Not rstProductsExisting.EOF
  13.         arrCancel = arrCancel & rstProductsExisting!ProductName & ";" 'get all values that are in the table into a string
  14.         rstProductsExisting.MoveNext
  15.     Loop
  16.  
  17.    arrCancel = Left(arrCancel, Len(arrCancel) - 1) 'get rid of the extra ';' at the end
  18.     arrCancel = Split(arrCancel, ";") 'convert string into array
  19.  
  20.  
  21.  
  22. End Sub
  23. Private Sub cmdCancel_Click()
  24.  
  25.     Dim rstProductsCancel As Recordset
  26.     Dim intCounter As Integer 'counter variable
  27.  
  28.     Set rstProductsCancel = CurrentDb.OpenRecordset("SELECT* FROM ComplaintProducts WHERE ComplaintsIndex = " & Forms!Main!Complaints.Form!txtComplaintIndex)
  29.  
  30.     If Not rstProductsCancel.EOF Then rstProductsCancel.MoveFirst
  31.  
  32.     Do While Not rstProductsCancel.EOF
  33.         rstProductsCancel.Delete
  34.     rstProductsCancel.MoveNext
  35.     Loop
  36.  
  37.     For intCounter = 0 To UBound(arrCancel)
  38.  
  39.         rstProductsCancel.AddNew
  40.         rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value
  41.         rstProductsCancel!ProductName = arrCancel(intCounter) 'add values in order from the array
  42.         rstProductsCancel.Update
  43.  
  44.     Next
  45.  
  46.  
  47.     rstProductsExisting.Close
  48.     Set rstProductsExisting = Nothing
  49.     rstProductsCancel.Close
  50.     Set rstProductsCancel = Nothing
  51.     DoCmd.Close acForm, "ComplaintsProducts", acSaveNo
  52.  
  53. End Sub
  54.  
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:
Expand|Select|Wrap|Line Numbers
  1. rstProductsCancel!ComplaintsIndex = Forms!Main!Complaints.Form!txtComplaintIndex 'use form for this value
Dec 22 '07 #12

zachster17
P: 30
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 22 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.