469,315 Members | 1,825 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How to edit and update recordset

547 512MB
I use the following code to append individual records from the "Fis_CaptureT" table, to the "Fis_CaptDataT" in realtime per row. It works 100%, until i correct an error in the "OrderQty" field, in the "Fis_CaptureT" 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

✓ answered by ADezii

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
  4.  
  5. Set MyDB = CurrentDb
  6. strSQL = "SELECT * FROM Fis_CaptDataT WHERE [Fis_CaptID] = " & Me![Fis_CaptID]
  7.  
  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
  40.  
  41. rst.Close
  42. Set rst = Nothing

4 19861
ADezii
8,800 Expert 8TB
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
  4.  
  5. Set MyDB = CurrentDb
  6. strSQL = "SELECT * FROM Fis_CaptDataT WHERE [Fis_CaptID] = " & Me![Fis_CaptID]
  7.  
  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
  40.  
  41. rst.Close
  42. Set rst = Nothing
Oct 23 '11 #2
neelsfer
547 512MB
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
neelsfer
547 512MB
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
ADezii
8,800 Expert 8TB
Glad it all worked out for you...
Oct 23 '11 #5

Post your reply

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

Similar topics

10 posts views Thread by Jim | last post: by
reply views Thread by jinhy82 | last post: by
3 posts views Thread by thebison | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.