Array programming help needed please.

[Guest]

I am trying to setup an array to store a single value from multiple record
sets. The value is Margin. Here is the code I have:

Option Compare Database
Option Explicit
Dim ValI As Double
Dim ValArr As Variant

Private Sub FillArray()
ValI = 0
While Not Recordset.EOF
ValArr(ValI) = Margin
Recordset.MoveNext
Wend
End Sub

Private Sub PrintArray()
ValI = 0
For ValI = 0 To UBound(ValArr())
Debug.Print " "; ValArr(ValI)
Next ValI
End Sub

I have a Tabular form setup that reads data via a query and populates the
form. So the first line would be recordset 1, the second line is recordset 2,
and so on. What I am attempting to do is store the values of 'Margin' into an
array to be used later in some calculations. I have the FillArray procedure,
and PrintArray procedure setup on a couple buttons at the bottom of the form
so I can control when it does either (later to be moved to be called from the
form load). Every time I press the Fill Array button I get a type mismatch
error (runtime error 13), and have tried to correct it with no success.
Probably something simple I am overlooking. I could really use some help on
this. Thanks in advance for any and all answers / help.

C_Ascheman

 

[Carl Colijn]

I am trying to setup an array to store a single value from multiple
record sets. The value is Margin. Here is the code I have:

Option Compare Database
Option Explicit
Dim ValI As Double
Dim ValArr As Variant

Private Sub FillArray()
ValI = 0
While Not Recordset.EOF
ValArr(ValI) = Margin
Recordset.MoveNext
Wend
End Sub

Private Sub PrintArray()
ValI = 0
For ValI = 0 To UBound(ValArr())
Debug.Print " "; ValArr(ValI)
Next ValI
End Sub

Every time I press the Fill Array button I get a type
mismatch error (runtime error 13), and have tried to correct it with
no success. Probably something simple I am overlooking. I could
really use some help on this. Thanks in advance for any and all
answers / help.

Hi C_Ascheman,

If you do not explicitly assign an array to a variant, it will not hold an
array and thus you cannot use array notation on it. The best thing to do is
to make a real array of your ValArr variable.

Example (also adjusted the code a bit, but it is NOT TESTED):
Option Compare Database
Option Explicit
Dim ValArr() As Variant

Private Sub FillArray()
Dim RecordNr As Long
If Not Recordset.EOF And Not Recordset.BOF Then
Recordset.MoveLast
End If
If Recordset.RecordCount > 0 Then
Redim ValArr(Recordset.RecordCount)

Recordset.MoveFirst
RecordNr = 0
While Not Recordset.EOF
ValArr(RecordNr) = Margin
RecordNr = RecordNr + 1
Recordset.MoveNext
Wend
End If
End Sub

Private Sub PrintArray()
Dim RecordNr As Long
For RecordNr = 0 To UBound(ValArr)
Debug.Print " "; ValArr(RecordNr)
Next
End Sub


Another option might be to use a generic collection object; this way you can
use the For Each... Next syntax and you are relieved from the burden of
dimensioning the array yourself

Example, again not tested:
Option Compare Database
Option Explicit
Dim ValArr As Collection

Private Sub FillArray()
Set ValArr = New Collection
While Not Recordset.EOF
Call ValArr.Add(Margin)
Recordset.MoveNext
Wend
End Sub

Private Sub PrintArray()
Dim NextMargin As Variant
For Each NextMargin In ValArr
Debug.Print " "; NextMargin
Next
End Sub

Hope this helps,
Carl

 

[Guest]

Carl that helped alot. Here is the code I am using:

Private Sub FillArray()
Dim RecordNr As Long
If Not Recordset.EOF And Not Recordset.BOF Then
Recordset.MoveLast
End If
If Recordset.RecordCount > 0 Then
ReDim ValArr(Recordset.RecordCount)

Recordset.MoveFirst
RecordNr = 0
While Not Recordset.EOF
ValArr(RecordNr) = Margin
RecordNr = RecordNr + 1
Recordset.MoveNext
Wend
End If
End Sub

Like I said it works great. Having another problem now. Taking the above
code I created another procedure to do some math. All works good except the
last line of math. To give you and example:

(Before Math)

Margin Net_Change
500 0
1000 0
1200 0

(After the Math - how it should look)

Margin Net_Change
500 +500
1000 +500
1200 +200

The problem I am having is the last line on the form is displaying a
whatever the Margin is as a negative value. The way the code is setup is to
take the the Margin of the current Record set and subtract it from the line
of the previous Record set and display the difference as a +/- value. Here is
my code I have, and like I said it works good until the math of the last
record set.

Private Sub ArrMath()
Dim RecordNr As Long
RecordNr = 0
Recordset.MoveFirst
Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr)
Recordset.MoveNext
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next
End Sub

Any help that anyone can give me will be greatly appreciated. Thanks in
advance.

C_Ascheman

 

[Guest]

No idea why my previous post was blank. Anyway the code you gave me helped me
greatly, and it works good with some small modifications:

Private Sub FillArray()
Dim RecordNr As Long
If Not Recordset.EOF And Not Recordset.BOF Then
Recordset.MoveLast
End If
If Recordset.RecordCount > 0 Then
ReDim ValArr(Recordset.RecordCount)

Recordset.MoveFirst
RecordNr = 0
While Not Recordset.EOF
ValArr(RecordNr) = Margin
RecordNr = RecordNr + 1
Recordset.MoveNext
Wend
End If
End Sub

Now I am trying to do some math and display the value in a box called
Net_Change of each Recordset. It works goood until the last recordset in
which it displays the Margin amount as a negative value. Here is the code for
the math part:

Private Sub ArrMath()
Dim RecordNr As Long
RecordNr = 0
Recordset.MoveFirst
Net_Change = ValArr(0)
For RecordNr = 1 To UBound(ValArr)
Recordset.MoveNext
Net_Change = ValArr(RecordNr) - ValArr(RecordNr - 1)
Next
End Sub

How it displaying on the form is like this:

Margin Net_Change
500 +500
1000 +500
1200 -1200

It should look like this:

Margin Net_Change
500 +500
1000 +500
1200 +200

Any more help would, and is greatly appreciated.

C_Ascheman