How to update an existing document with record from open recordset details in Ms Access by criteria

[nector]

Hallo Experts

I have a challenge here I want to update an existing document in MS access with extra important information from some other table by criteria (example with WHERE CLAUSE) for example If I want to use a simple method then it will be like below:

UPDATE tblJournalHeader INNER JOIN tblVoucher ON tblJournalHeader.CreateID = tblVoucher.CreateID SET tblJournalHeader.Status = [Forms]![FrmJournalVourcherPosting]![CboStatus], tblVoucher.Authority = [Forms]![FrmJournalVourcherPosting]![CboStatus]
WHERE (((tblJournalHeader.CreateID)=[Forms]![FrmJournalVourcherPosting]![CboCreateID]));

But this method is very slow against SQL Server while using the method below is faster, but how do I put the criteria so the data goes to update only a particular document line, for example if a document has 6 fields like below meaning only those field with the correct reference number must be updated.

Here is working VBA code, the data is coming from the internet server:

 Set db = CurrentDb
Set rs = db.OpenRecordset("ViewtblESDPOS", dbOpenDynaset, dbSeeChanges)
    Set json = JsonConverter.ParseJson(strDataAudit)
   
    'Process data.
    Z = 1
    For Each Details In json
        rs.AddNew
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs![INVID] = CLng(Me.txtdataESDTransfers)
        rs.Update
        Z = Z + 1
    Next
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing

 

[Edgar_]

How does the JSON object look?

What do you mean by:

if a document has 6 fields like below meaning only those field with the correct reference number must be updated

Does this mean some documents in your JSON have these fields and others don't have them?

If so, then just perform a check before using rs.Edit ... rs.Update. Like:

For Each Details In json
  If Details.Exists("ESDTime") And Details.Exists("TerminalID") And Details.Exists("InvoiceCode") Then 'and so on
    rs.Edit
    '...
    rs.Update
  End If
Next Details

So, how does the JSON object look? You are also going to need to go to the right record before the edit, so rs.FindFirst and its check for no matches will be necessary.

 

[nector]

Okay, let me make things clear here:

An invoice header will have the following details as per listed below:

(1) Customer name (with customer details populated already)
(2) Date (With date details populated already)
(3) Primary Key (Already populated already)
(4) [ESDTime] (Blank require the said update)
(5) [TerminalID] (Blank require the said update)
(6) [InvoiceCode] (Blank require the said update)
(7) [InvoiceNumber] (Blank require the said update)
(8) [FiscalCode] (Blank require the said update)

The blank fields above are the one requires update as you can see the invoice header already has pre-record there, now to complete it we need to complete the blank fields with some record from Json as per below open record set. Now to make sure that each invoice has its own unique record, that is where I need to include the primary key as where clause criteria otherwise without where clause criteria, then the details that will be updated will not be matched correctly.

 Set db = CurrentDb
Set rs = db.OpenRecordset("ViewtblESDPOS", dbOpenDynaset, dbSeeChanges)
    Set json = JsonConverter.ParseJson(strDataAudit)

    'Process data.
    Z = 1
    For Each Details In json
        rs.AddNew
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs![INVID] = CLng(Me.txtdataESDTransfers)
        rs.Update
        Z = Z + 1
    Next

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing

Attached is the combined invoice detail missing above

 

[ebs17]

... SET tblJournalHeader.Status = [Forms]![FrmJournalVourcherPosting]![CboStatus], tblVoucher.Authority = [Forms]![FrmJournalVourcherPosting]![CboStatus] ...

Are you updating in two tables at the same time?
I wouldn't do something like that, but apply a separate query to each table.

 

[Edgar_]

Still confused, nector. Let's condense this information, the following is clear:

1. You have both a local and a remote database.
2. The local database has records with Customer names, Dates, and Primary keys but lacks certain details like ESDTime and TerminalID.
3. To fill in these missing details, you retrieve data from the remote database.
4. The remote database provides this data as a JSON string, which you convert into a dictionary using the ParseJson function.

Now, regarding the unclear part:

The primary key is crucial to ensure each invoice has a unique record, absolutely, but your VBA code uses .AddNew. In order to update these records correctly, you should use the .Edit method instead of .AddNew, because you have an existing record that you want to update. However, to position the recordset properly for updating, first use .FindFirst within your local database. After that, you can perform the update using .Edit.

Now, dealing with JSON adds complexity. You haven't shared how your JSON response is structured. Depending on whether the remote database sends multiple invoices at once or just one, you'll need to handle the matching and updating accordingly.

 

[nector]

Very well answered and thank you for this , now the problem still remain how to link that external data to the local database via the primary key. Example your suggestion is very good , but linking now how? How do I edit the required record , for example if this was possible:

Rs = Me.CboInvoiceNumber , this is where I have a problem??????????

Set db = CurrentDb
Set rs = db.OpenRecordset("ViewtblESDPOS", dbOpenDynaset, dbSeeChanges)
    Set json = JsonConverter.ParseJson(strDataAudit)
    'Process data.
    Z = 1
    For Each Details In json
        rs.Edit
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs![INVID] = CLng(Me.txtdataESDTransfers)
        rs.Update

        Z = Z + 1

    Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing

 

[Edgar_]

Use FindFirst before rs.Edit

rs.FindFirst "[Primary Key]=" & Me.CboInvoiceNumber
If Not rs.NoMatch Then
rs.Edit
'...
rs.Update
End If

You can also avoid this type of update and use currentDb.Execute "UPDATE ... SET ... WHERE ... "

 

[nector]

Thank you so much that is what was looking for now let me try it.

 

[nector]

Hi Edgar,

Is this what you meant below?

'Processing data from the string above
    Set db = CurrentDb
Set rs = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)
    Set json = JsonConverter.ParseJson(strDataAudit)
    
    'Process data.
   rs.FindFirst "[InvoiceID]=" & Me.txtAuditingEsdValues
    Z = 1
    For Each Details In json
        rs.Edit
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs.Update
        Z = Z + 1
    Next
  
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing

 

[Edgar_]

Sure, how'd it go?

 

[Pat Hartman]

Your process doesn't make any sense to me so far.
In your first explanation you showed an update query that updated a specific record. There is no indication of how you obtain CreateID. The implication is that you have a form's recordset open and you are navigating through the recordset manually, one record at a time and in some form level event, you would be running your append query. I agree, this would be painfully slow for anything more than a couple of records and also fraught with potential dangers.
The second example in the first post seems to be looping through a Json recordset and adding a new record for each to ViewtblESDPOS which does not seem to have any relationship with the two tables shown in the first example. Using the same names for equivalent examples would help the rest of us immensely. In this second example, there doesn't seem to be any common key fields which you mention in #3.

You had some exchanges with other experts who seemed to be making guesses regarding what you really were trying to do.

My suggestion is that instead of making us guess, just tell us.
1. How do you get the Json file?
2. What fields does it contain?
3. What do you want to do with it?

Just FYI - the "Find First" process you are envisioning is very inefficient. Since presumably, the Json file has id's that match some existing record in a table that you want to update, it is probably more efficient in the long run to load the Json data to a temp table and use a join from the temp table to update the existing record. No loop, no VBA. Just two declarative SQL statements. Load the Json file into a table, Join the temp table to the permanent table and update it.

Then we get back to #3 and the question that post raises is WHY is there an already existing partial record? Updating this partial record makes no sense because the relationship to the Json data cannot be 1-1. It looks like 1-m. Perhaps what you need to use is an append query to add the Json data to a child table but somehow acquire the FK of the parent record.

 

[nector]

Oaky thank you so much for this

The json file comes from an external cloud database which contains the essential information which needs to be part of the invoice header, currently its working okay by saving it in another table no relationship to the invoice header table, now at the time of saving, it is done in that invoice temporal table the Json detail is saved together with the invoice ID (Invoice ID which is a primary key for the invoice header table) for the purpose of easy referencing.

Then to combine that essential saved Json detail which has the listed fields below I use now Dlookup which appear to very slow hence the need to change because of the switch to SQL Server, but works very well with Access Vs Access:

(1) ESD Time
(2) Terminal Code
(3) Invoice Code
(4) Invoice Number
(5) Fisical Code

All I need is instead of saving that Json data in a different table I want to save directly in the invoice header table which I can easily do but the challenge here is how to save it in the correct line like the way we use DLookup by criteria.

I thought the code below is the better method to reference the record so that it saved in the correct line, but it appears like you do not agree to this :

rs.FindFirst "[InvoiceID]=" & Me.txtAuditingEsdValues

Simple question ,can the record set assigned with reference ID or not? that is where my issue is

 

[nector]

I think I will try Rich BB code with minor amendments.

'Processing data from the string above
    Set db = CurrentDb
Set rs = db.OpenRecordset("tblPOSStocksSold", dbOpenDynaset, dbSeeChanges)
    Set json = JsonConverter.ParseJson(strDataAudit)
   
    'Process data.
    Z = 1
rs.FindFirst "[ItemSoldID]=" & Me.txtAuditingEsdValues
If Not rs.NoMatch Then
MsgBox "No Match Found", vbExclamation, "No matching data found"
Exit Sub
Else
    For Each Details In json
        rs.Edit
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs.Update
        Z = Z + 1
    Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing
End If

 

[Pat Hartman]

Then to combine that essential saved Json detail which has the listed fields below I use now Dlookup which appear to very slow hence the need to change because of the switch to SQL Server, but works very well with Access Vs Access:

You don't need a VBA loop at all. Join the json temp table to the permanent table and use an update query. If this is slow, add an index to the InvoiceID in both tables. If you are running the query from Access, you probably need a PK on the temptable.

 

[nector]

Thank you Pat Hartman
Noted and highly appreciated.

 

[Pat Hartman]

Action queries are almost always better solutions than code loops if you have the option of using one They are faster to execute, sometimes vastly faster and easier to code.