update records below with array

[HS_1]

I trying to populate automatically 2 records below with array values after I select "L1" from combobox value list. What my code does it sits on first record and changes the text from L1 to L2 and finally L3 (see #6) instead of doing what #1.2.3 shows. Wonder what I am doing wrong here or maybe my approach is completely incorrect and there is a better way to solve my problem? Would greatly appreciate your suggestions.

Private Sub cboCoreSeq_AfterUpdate()'declare a variant array
    Dim strCoreSeq(1 To 2) As String
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tblJbPowerAssignment")
'populate array
    strCoreSeq(1) = "L2"
    strCoreSeq(2) = "L3"
    
'declare a variant to hold the array element
    Dim item As Variant
    If Me.cboCoreSeq = "L1" Then
        
'loop through array
        For Each item In strCoreSeq
            
            rec.Edit
            rec("CoreSeq") = item
            rec.Update
            rec.MoveNext
            
            Debug.Print item
            Me.cboCoreSeq = item
            Next item
        Else
        End If
        
'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing
    End Sub

[Minty]

I assume that tblJbPowerAssignment is also the record source for the form.
If so why are you overwriting the same value with

Me.cboCoreSeq = item

that makes no sense?

Simply run the code then refresh the form.

[HS_1]

Minty, thanks for the replay, yes tblJbPowerAssignment is the record source, the field name is CoreSeq and primary key is IdJb.
Let me explain what I am trying to accomplish. The value list contains the following L,N,L1,L2,L3,RD,BK,WH,SH. There is 4 possible combinations to populate the records: L/N , L1/L2, L1/L2/L3 & RD/BK/WH/SH.
Right now user has to choose the appropriate letter from drop down list to populate one record, and do the same for the next in the same combination group which can be time consuming especially when he has to do this many times.
So the idea is to replace the value list with only 4 entries P1, P2, P3 & R, and if the user chooses P3 for example then P3 will be replaced with L1 and 2 next records will be populated with L2 & L3 in coreseq field and and stored in table , see all possible combinations below.
I was hopping that the code would do this for one combination but it does not.

[Minty]

If these values won't change just hard code an insert query.
In fact create a table with all the values;

List_Value	Ref_ID
L	1
N	1
L1	2
L2	2
L1	3
L2	3
L3	3
RD	4
BK	4
WH	4
SH	4

Now based on the choice you can run an insert query something like

INSERT INTO tblJbPowerAssignment (CoreSeq) (Select List_Value FROM tblMyNewList WHERE Ref_ID = 1)

[HS_1]

Minty thanks for your suggestion I was able to make the code working for one combination by adding to loop "DoCmd.GoToRecord , , acNext"

Private Sub cboCoreSeq_AfterUpdate()'declare a variant array
    Dim strCoreSeq(1 To 3) As String
    Dim j As Integer
    Dim coreseq As String
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tblJbPowerAssignment")
'populate array
    strCoreSeq(1) = "L1"
    strCoreSeq(2) = "L2"
    strCoreSeq(3) = "L3"
    
'declare a variant to hold the array element
    Dim item As Variant
    If Me.cboCoreSeq = "P3" Then
        
'loop through array


        For Each item In strCoreSeq
            
            rec.Edit
            rec("CoreSeq") = item
            rec.Update
            rec.MoveNext
            
            Debug.Print item
            Me.cboCoreSeq.Value = item
           DoCmd.GoToRecord , , acNext
            Next item


        Else
        End If
        
'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing

    End Sub

[Minty]

I'm amazed that works as you are still effectively storing the value twice once here
rec("CoreSeq") = item

And then again here
Me.cboCoreSeq.Value = item

So I'm intrigued that you don't get a error saying the record has been updated by another process?
The insert query would be single line execution and not need the loop.

But if you have it solved then perfect.

[HS_1]

Minty, Sorry for the unclear previous message, of course I am going to use your suggestion which much better solution to the problem that the code I came up with. Thanks one more time.

[Minty]

No need to be sorry, with Access there are always at least 13 ways to achieve a suitable result.

The difference is that some are easier to maintain without having to re-write hard coded values in VBA if the data changes.
Using tables to drive these types of processes means that if another sequence comes along or you need to lose one you simply edit or add values to your table and it all still works!
An end user could do the update, making it super easy to manage.