ADODB looping through and update help!

[fluffyvampirekitten]

Could anyone help me with ADODB recordset?
Thanks in advance

I have a table, DuplicateSystemEPOS and a query , DuplicateSystemEPOS_INS.

For the query , I did some calculation to count the No_of_Visits and store it in No_Of_INSV column.


Thus, I will get a result in the range of 0 to 3

So what I'm trying to do now is to Update the DuplicateSystemEPOS table AND its column ,INSTITUTION_ACT.
For Example , if the No_Of_INSV is 0, I wanna update the DuplicateSystemEPOS ( INSTITUTION_ACT) column to "P-NHGP".

Both table and query have GROUP_NO as the primary key and the table have duplicate records.


Here are the code that I've been testing out but whenever I run the code below , it does not update anything .

Option Explicit
Option Compare Database
Private Sub Update_EPOS()
'Initialize variable // Open recordset
Dim rsDuplicateEpos As ADODB.Recordset
Set rsDuplicateEpos = New ADODB.Recordset
'Connection to the database
Dim connector As ADODB.Connection
Set connector = CurrentProject.Connection
'adOpenDynamic - type of cursor to know or what is going on with the record set you are working with
rsDuplicateEpos.Open "DuplicateSystemEPOS_INS", connector, adOpenDynamic, adLockBatchoptimistic
Dim rsDuplicateSystemEPOS As ADODB.Recordset
Set rsDuplicateSystemEPOS = New ADODB.Recordset
rsDuplicateSystemEPOS.Open "DuplicateSystemEPOS", connector, adOpenDynamic, adLockBatchoptimistic

'Find the target record
'While rsDuplicateEpos.EOF = False
Do Until rsDuplicateEpos.EOF
    'Check for match
        If rsDuplicateEpos("No_of_INSV").value = 0 Then
            If rsDuplicateEpos("GROUP_NO") = rsDuplicateSystemEPOS("GROUP_NO") Then
            'rsDuplicateEpos("INSTITUTION_ACT").value = "P-NHGP"
            'rsDuplicateEpos.Update
            'rsDuplicateSystemEPOS.AddNew
            rsDuplicateSystemEPOS("INSTITUTION_ACT").value = " P-NHGP"
            rsDuplicateSystemEPOS.Update
            End If
        End If
            'exit loop
           'rsDuplicateEpos.MoveLast
           
               rsDuplicateEpos.MoveNext
Loop
'Close variable/ recordset
rsDuplicateEpos.Close
Set rsDuplicateEpos = Nothing
rsDuplicateSystemEPOS.Close
Set rsDuplicateSystemEPOS = Nothing
End Sub

[June7]

AddNew is to add a new record, not edit existing.

Saving calculated data is usually not a good idea. Why do you need to save the P-NHGP value? Why not just calculate when needed?

[fluffyvampirekitten]

At first my main table is DuplicateSystem - whereby all the duplicate records are stored ( GROUP NO , LAST VISIT INSTITUTION , LAST VISIT DATE , INSTITUTION TO ACT) .

So I wanna find how many institutions did the user visit the institution to identify the (INSTITUTION TO ACT)
I'll use this query

SELECT DISTINCT DuplicateSystem.GROUP_NO, Count(DuplicateSystem.LV_INS) AS NO_OF_INSV
FROM DuplicateSystem
GROUP BY DuplicateSystem.GROUP_NO;

Thus, if the user did not visit any visit , NO_OF_INS =0. Thus, I need to record down in DuplicateSystem ( INSTITUTION TO ACT) that user did not visit any institution ("P-NHGP")

If the user visited once , NO_OF_INS =1. Thus, I need to record down in
DuplicateSystem the INSTITUTION TO ACT = LAST VISIT INSTITUTION.

[June7]

You are missing my point. Saving this calculated data is not advised and is probably totally unnecessary. Calculate that value when it is needed.

[fluffyvampirekitten]

You are missing my point. Saving this calculated data is not advised and is probably totally unnecessary. Calculate that value when it is needed.

How do you suggest me calculating it ?
I only know how to use query to calculate.

[June7]

The query can be included in report RecordSource then expression in textbox:

=IIf([NO_OF_INSV] = 0, "P-NHGP", Null)