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 ComplaintProduc ts that has the fields: ComplaintProduc tsIndex (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 ComplaintProduc ts 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 rstProductsExis ting 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 rstProductsExis ting 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 "rstProductsExi sting". 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 rstProductsCanc el, would that also remove the records from rstProductsExis ting. 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.
12 2237 ADezii 8,834
Recognized Expert Expert
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 ComplaintProduc ts that has the fields: ComplaintProduc tsIndex (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 ComplaintProduc ts 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 rstProductsExis ting 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 rstProductsExis ting 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 "rstProductsExi sting". 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 rstProductsCanc el, would that also remove the records from rstProductsExis ting. 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 rstProductsExis ting 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.
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.
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 8,834
Recognized Expert Expert
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 rstProductsExis ting to the rstProductsCanc el Recordset. The problem is that the Records referred to by rstProductsExis ting 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.
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: - Create a holding table with the same structure to hold the records until the user presses cancel or closes the form.
- Copy the records to variables in vba and restore them to the table when the user clicks cancel.
- Use the records in the rstProductsExis ting to filter them out of the rstProductsCanc el 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 8,834
Recognized Expert Expert
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:- Create a holding table with the same structure to hold the records until the user presses cancel or closes the form.
- Copy the records to variables in vba and restore them to the table when the user clicks cancel.
- Use the records in the rstProductsExis ting to filter them out of the rstProductsCanc el 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.
No worries ADezii,
I'm not in a hurry so take your time anyway.
Take care,
Ken.
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 rstProductsExis ting 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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Toonman |
last post by:
I have a single .asp page that opens a connection and then sequentially
opens and closes 14 recordsets from stored procedures to obtain various
product information before closing the connection.
Is it common practice to do something like this? Or is opening 14
recordsets going to become a real problem when the page goes live and starts
getting high web traffic?
Thank you in advance for any information you might provide.
|
by: sans_spam |
last post by:
I want to pull 2 different recordsets using 2 different stored
procedures using the same Data Connection. Below is the code that I
currently have, but I'm getting an 'Object Required' error message. Am
I doing this correctly, or at least headed in the right direction? Can
someone tell me the most efficient way (provide a code example) to pull
2 different recordsets from the same Data Connection?
Set dConn =...
|
by: Ryan.Chowdhury |
last post by:
This is a general question regarding the use of view and stored
procedures. I'm fairly new to databases and SQL.
I've created a SQL database using an Access Data Project ("ADP") and
I'm satified with the table structure. I've moved on to building some
front ends for our users.
I'm running into situations where I want subreports to be built from
queries that are dependent on the values in other controls.
I've played with stored...
|
by: milindsj |
last post by:
Hello,
I have a application hosted on tomcat.
I use a jsp to set a cookie say channelid.
Now I am redirecting this page to some other appliaction which is
hosted in tomcat on some other machine.
I am having problems in accessing the cookie. The second
application in which i am supposed to get the cookie is getting null.
Let me know if someone has any idea.
Thanks in advance.
|
by: Corrine |
last post by:
Given a backend on a network server and a frontend on a local computer, when is
data pulled across the network? For example, does Rst.Recordcount pull data
across the network? How about Rst.Findfirst? When a form or report opens, do
they pull all the data in the recordsource across the network?
Thank you for your help!
Corrine
| |
by: Evan Kontos |
last post by:
I am having an issue w/regard to stored procedures. If I do the
following:
Dim OccCnctn As New OleDb.OleDbConnection
Dim OccCmd As New OleDb.OleDbCommand
Dim OccRdr As OleDb.OleDbDataReader
OccCnctn.ConnectionString = "Integrated Security=SSPI;Packet
Size=4096;Data Source='COMTEK-9100WXP';Tag with column collation when
possible=False;Initial Catalog=Move Manager;Use Procedure for
Prepare=1;Auto Translate=True;Persist Security
|
by: Randy Harris |
last post by:
I was inspired by the recent discussion of returning multiple recordsets to
ADO from a stored procedure. (Amazed is probably more accurate). I asked
about how to accomplish same with Oracle and got a nudge in the right
direction from Mr. Kreft. I promised to provide details once working, so
here it is.
The code is shown below. My next step is to build this technique into my
application. I'm hoping for substantial performance gain.
...
|
by: mooreit |
last post by:
The purpose for my questions is accessing these technologies from
applications. I develop both applications and databases. Working with
Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test
Environments.
What is the purpose of a view if I can just copy the vode from a view
and put it into a stored procedure?
Should I be accessing views from stored procedures?
|
by: KAData |
last post by:
I have a program that will access remote stored procedures via visual basic.net application. I have SQL Server Management Studio Express (2005) installed on the client computers that will be accessing an SQL Server running SQL 2003 remotely. I have been having a problem setting up the client computers to access the stored procedures remotely. I just recently heard that SQL Express is not able to run remote stored procedures. Anyone have any...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |