CODEWITHSUNDEEP
vbams-access
Jaskier
Jaskier

Reputation: 1095

Adding to every other array position MS Access

I'm needing to take one array (firstArray) and input into a second array (secondArray). However, the first four fields are the same value. After the first four positions, it begins to alternate in values.

Example:

firstArray

+---------+
| ID#     |
| Name    |
| month1  |
| month2  |
| month3  |
| etc...  |
+---------+

secondArray

+----------+
| ID#      |
| Name     |
| month1   |
| month2   |
| NewField |
| month3   |
| NewField |
| month4   |
| etc...   |
+----------+

I'm fairly new to VBA, so I apologize for the awful code.

Code so far:

Dim i As Integer
i = 0
Dim j As Integer
ReDim secondArray(0 To (fieldCount - 4) * 2)
Dim finalCountDown As Integer
finalCountDown = (fieldCount - 4) * 2

secondArray(0) = firstArray(0)
secondArray(1) = firstArray(1)
secondArray(2) = firstArray(2)
secondArray(3) = firstArray(3)

i = 3
j = 3
Do Until i > finalCountDown
    i = i + 1
    secondArray(i) = "NewField"
    i = i + 1
    j = j + 1
    secondArray(i) = firstArray(j)
Loop

I also have a MsgBox to iterate through and output my fields:

'//------ testing output
i = 0
For i = 0 To finalCountDown
    MsgBox secondArray(i)
Next i

I appreciate any help! If there's any confusion, I'll gladly try to explain some more!

EDIT:

The two arrays are of different size but are dynamic. firstArray is firstly set to 20 positions while secondArray is originally set to 32 positions. (These are calculated each time this process is ran with the archived data being pulled. This allows my users to add data and not have to worry about having to manually add in the values to my arrays.)

EDIT2:

I've added Erik's portion to my code with a few alterations. I also added a separate counter for my firstArray in order to make sure it's inputting the correct rows into the correct positions of my secondArray.

EDIT3:

Here is the code that ended up working for me:

Dim i As Integer
i = 0
Dim j As Integer
'removed the " - 4"
ReDim secondArray(0 To (fieldCount * 2))
Dim finalCountDown As Integer
'removed the " - 4"
finalCountDown = (fieldCount * 2)

secondArray(0) = firstArray(0)
secondArray(1) = firstArray(1)
secondArray(2) = firstArray(2)
secondArray(3) = firstArray(3)

i = 3
'created own counter for firstArray
j = 3
Do Until i > finalCountDown
    i = i + 1
    secondArray(i) = "NewField"
    i = i + 1
    j = j + 1
    secondArray(i) = firstArray(j)
Loop

The error I was getting was due "Subscript not in Range" due to the fact that my finalCountDown variable was less than my array needed to be. Allowing the variable to become larger than my array allowed my array to finish iterating through itself and now inputs the proper fields in the proper order.

I'm accepting Erik's answer as it was the stepping stone to answering the question!

Upvotes: 0

Views: 44

Answers (1)

Erik A
Erik A

Reputation: 32682

For the adjusted code, you can do a simple check to check if the j value is valid:

Dim i As Integer
i = 0
Dim j As Integer
ReDim secondArray(0 To (fieldCount - 4) * 2)
Dim finalCountDown As Integer
finalCountDown = (fieldCount - 4) * 2

secondArray(0) = firstArray(0)
secondArray(1) = firstArray(1)
secondArray(2) = firstArray(2)
secondArray(3) = firstArray(3)

i = 3
j = 3
Do Until i > finalCountDown
    i = i + 1
    finalArray(i) = "NewField"
    i = i + 1
    j = j + 1
    If j => LBound(colheaders) And j <= UBound(colHeaders) Then
        finalArray(i) = colHeaders(j)
    End If
Loop

Upvotes: 1

Related Questions