473,899 Members | 3,655 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to edit and update recordset

547 Contributor
I use the following code to append individual records from the "Fis_Captur eT" table, to the "Fis_CaptDa taT" in realtime per row. It works 100%, until i correct an error in the "OrderQty" field, in the "Fis_Captur eT" table.

Expand|Select|Wrap|Line Numbers
  1.    Dim MyDB As DAO.Database
  2.    Dim rstEntry As DAO.Recordset
  3.       Set MyDB = CurrentDb
  4.        Set rstEntry =  MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
  5.   With rstEntry
  6.   .AddNew
  7.   ![Client_lookup] = Me![Client_lookup]
  8.   ![Item_Lookup] = Me![Item_Lookup]
  9.   ![CPrice] = Me![Price]
  10.   ![Order_No] = Me![Order_No]
  11.   ![OrderDate] = Me![OrderDate]
  12.   ![OrderQty] = Me![OrderQty]
  13.   ![InvoiceQty] = Me![InvoiceQty]
  14.   .Fields("Transaction" & CStr(Me![Transaction])) =    Me![InvoiceQty]
  15.   ![Fis_CaptID] = Me![Fis_CaptID]
  16.     .update
  17.     End With
  18.    rstEntry.close
  19.    Set rstEntry = Nothing
My question - how can i change the "OrderQty" in the
main capturing table - " Fis_CaptureT" and then it updates this same record in realtime in the 2nd table - "Fis_CaptDataT" , without creating another record when changing it.

mainform = OrderingF
subform = Fis_OrderICNSF
field = OrderQty in both tables
main table = Fis_CaptureT
2nd table = Fis_CaptDataT
[Fis_CaptID] appears in both tables, and is set for "no duplicates allowed" in both

I imagine it has to do with edit+update in the recordset. But how do i adjust the above code to incorporate this?

Currently i get around this by using the following code
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Fis_OrderEditF", acNormal, "", "[Fis_CaptID]=" & "" & Fis_CaptID & "", , acNormal
  2. [Forms]![Fis_OrderEditF]![OrderQty] = [Forms]![OrderingF]![Fis_OrderICNSF]![OrderQty]
  3. [Forms]![Fis_OrderEditF]![InvoiceQty] = [Forms]!
  4. [Forms]![Fis_OrderEditF].Requery
  5. DoCmd.close acForm, "Fis_OrderEditF"
The code is situated in the "error trapping" part of the main recordset append code at the bottom, and if a duplicate of the same ID gets appended due to a change in the "OrderQty" and it getting appended, it kicks in.
I open a 2nd form in the background containing the Fis_CaptDataT table, update the specific field using the afterupdate function and closing it again immediately. Is there a better way using recordsets?

Please help. I have struggling for 2 days non-stop now.
Oct 23 '11 #1
4 20898
8,834 Recognized Expert Expert
If I am interpreting your Request correctly, you must first see if the Data has been Captured. It it hasn't been Captured, it is an AddNew operation, if it was Captured, it is an Edit operation:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim strSQL As String
  5. Set MyDB = CurrentDb
  6. strSQL = "SELECT * FROM Fis_CaptDataT WHERE [Fis_CaptID] = " & Me![Fis_CaptID]
  8. 'See if the Data has already been Captured, if not, Add, not Edit the Record
  9. If DCount("*", "Fis_CaptDataT", "[Fis_CaptID] = " & Me![Fis_CaptID]) = 0 Then     'NOT Captured/ADD
  10.   Set rst = MyDB.OpenRecordset("Fis_CaptDataT", dbOpenDynaset, dbAppendOnly)
  11.     With rst
  12.       .AddNew
  13.         ![Client_lookup] = Me![Client_lookup]
  14.         ![Item_Lookup] = Me![Item_Lookup]
  15.         ![CPrice] = Me![Price]
  16.         ![Order_No] = Me![Order_No]
  17.         ![OrderDate] = Me![OrderDate]
  18.         ![OrderQty] = Me![OrderQty]
  19.         ![InvoiceQty] = Me![InvoiceQty]
  20.         .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
  21.         ![Fis_CaptID] = Me![Fis_CaptID]
  22.       .Update
  23.   End With
  24. Else        'Data Captured, so Edit the Recordset
  25.   Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  26.     With rst
  27.       .Edit
  28.         ![Client_lookup] = Me![Client_lookup]
  29.         ![Item_Lookup] = Me![Item_Lookup]
  30.         ![CPrice] = Me![Price]
  31.         ![Order_No] = Me![Order_No]
  32.         ![OrderDate] = Me![OrderDate]
  33.         ![OrderQty] = Me![OrderQty]
  34.         ![InvoiceQty] = Me![InvoiceQty]
  35.         .Fields("Transaction" & CStr(Me![Transaction])) = Me![InvoiceQty]
  36.         ![Fis_CaptID] = Me![Fis_CaptID]
  37.       .Update
  38.     End With
  39. End If
  41. rst.Close
  42. Set rst = Nothing
Oct 23 '11 #2
547 Contributor
Thx Mr Adezi. I will incorporate it. I think you have it right.

I may start to capture data and on completion of that specific row, I may notice an error. I then need to go back to the row with the error, change the quantity and it is updated to the 2nd table.
Oct 23 '11 #3
547 Contributor
Working 100%. thx a million Adezi. Taken me months to get this right. I can now also use this in my timing program
Oct 23 '11 #4
8,834 Recognized Expert Expert
Glad it all worked out for you...
Oct 23 '11 #5

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

Similar topics

by: Jim | last post by:
Hi, Let me explain how I'd generally do things. For a page where the user edits data, I'd generally call it something like editfred.php (for example), the page which updates the data would be called updatefred.php and equally the page which processes a delete would be deletefred.php. I like splitting the pages up this way, it feels less cluttered and more organised than throwing all the functionality in one fred.php.
by: Tom | last post by:
I created a c# web form. I added a DataGrid1 to it. I added the Edit, Update, Cancel Button Column. I added two Bound columns. All is well. But when I run the app and press the edit button, the text box widht is not dynamic. its fixed! So only a portion
by: Andreas Klemt | last post by:
Hello, how can I easily solve the nested DataGrid problem working correctly with edit / update events? Is there any very simple example? Who has the same problems? Thanks in advance, Andreas
by: Hartmut Schroth | last post by:
Hi, I need a solution for the following problem: In the item template of a datalist control I have already a button control with the commandname set to "select" to perform some specific database action. I additionally want to update an integer value in the underlying data source by simply clicking an a check box or an imagebutton WITHOUT USING THE EDIT/UPDATE COMMANDNAME property of the
by: pmud | last post by:
Hi, I have an ASP.NET application using C# code. I am using a datagrid to display records from a database based on a user input, i.e a user enters a compnay name in text box & when he clicks a "Submit" button, only those records are displayed where company name matches with user input. I need the datagrid to be editable. For this i used the walkthrough from ..NET "Using a datagrid for reading & writing data to database" or it was some...
by: jinhy82 | last post by:
Hello! I am using DataGrid to display details from Ms Access. Fe features such as Edit, Update and Delete are added, but they did no function. Can anyone help me? Thank you very much!!! For the update function, am I suppose to write the commane : UPDAT <tablename> SET = "..."? As I am using the "EditCommandColumn", so when I press the "Edit" link everyfield will changed to textbox form, may I know what is that nam for the textbox? Here...
by: thebison | last post by:
Hi all, I hope someone can help with this relatively simple problem. I am building a timesheet application using ASP.NET C# with Visual Studio 2003.As it is only a protoype application, my database has been made in MSDE. My problem is to do with a DataGrid. I have successfully coded the DataGrid so that you can Edit, Update, Cancel. However as my Update Stored Procedure only updates certain columns I would like to make
by: anwarulhuq | last post by:
I have placed Edit-Update-Cancel Link button from Properties builder of the Data Grid. Its working but the problem what im facing is.. It doesnt work till i click for two times. Link button should work for only one click right. Please help me out to solve this.
by: yehey2010 | last post by:
Hi, I have problem with my ASP.Net Code, I used GridView with Edit/Update on it. However the program seems not work correctly because it doesn't Update the OLD VALUE with the NEW VALUE. Please help on this. thanks so much <%@ Page Language="C#" Debug="true" MasterPageFile="MasterPage.Master" Title="Untitled Page" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <script runat="server">
by: pieandpeas | last post by:
Hi, i've been searching the internet for a good reference of how to add the update column functionality to a gridview, coding everything in the .vb behind the page stuff myself, e.g not using a sqldatasource etc.. I've got my dataset bound to a gvw, and it brings back data. I'm just not sure how to approach the coding side of updating a row of a gridview using the edit/update buttons. Does anyone have a good example or code which i could...
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: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
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: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.