ARRAYS

[JohnG via AccessMonster.com]

Array question:
I want to match an account number entered in a form to the same account
number in a transaction table by looping through the transaction table and
for every match (the number of matches can vary) extract the date and amount
field and load the date and amount into an instance of an array. Can someone
provide some Access VBA code to do this. I would be very grateful for this.
Thanks,
JohnG

 

[bindurajeesh]

The way i would do it would be to set your transaction table as a recordset
and put it in a while loop. Within the while loop an if statement which
compares the account number on the form with the recordset account number and
if it is equal then from that same recordset get date and amount and assign
those to an array structure;

while not rs.eof
if rs!accountnumber = forms!frmaccounts!accountnumber then
array[1],[2] = rs!date, rs!amount 'not sure about this code don't use
arrays
much
rs.movenext
else
rs.movenext
end if
wend

This is not exact but it might get you started.

 

[JohnG via AccessMonster.com]

Thanks for your reply, binduraeesh.
I'm very familiar with the basics of VBA coding . The only thing I need is
the array code for the loading the 2 fields into the instance of the array
and the incrementing of the subscript

JohnG

The way i would do it would be to set your transaction table as a recordset
and put it in a while loop. Within the while loop an if statement which
compares the account number on the form with the recordset account number and
if it is equal then from that same recordset get date and amount and assign
those to an array structure;

while not rs.eof
if rs!accountnumber = forms!frmaccounts!accountnumber then
array[1],[2] = rs!date, rs!amount 'not sure about this code don't use
arrays
much
rs.movenext
else
rs.movenext
end if
wend

This is not exact but it might get you started.

Array question:
I want to match an account number entered in a form to the same account

[quoted text clipped - 4 lines]

Thanks,
JohnG

 

[bindurajeesh]

here is something that I pulled out of help for a multidimensional array

Dim sngMulti(1 To 5, 1 To 10) As Single

Sub FillArrayMulti()
Dim intI As Integer, intJ As Integer
Dim sngMulti(1 To 5, 1 To 10) As Single

' Fill array with values.
For intI = 1 To 5
For intJ = 1 To 10
sngMulti(intI, intJ) = intI * intJ
Debug.Print sngMulti(intI, intJ)
Next intJ
Next intI
End Sub

Thanks for your reply, binduraeesh.
I'm very familiar with the basics of VBA coding . The only thing I need is
the array code for the loading the 2 fields into the instance of the array
and the incrementing of the subscript

JohnG

The way i would do it would be to set your transaction table as a recordset
and put it in a while loop. Within the while loop an if statement which
compares the account number on the form with the recordset account number and
if it is equal then from that same recordset get date and amount and assign
those to an array structure;

while not rs.eof
if rs!accountnumber = forms!frmaccounts!accountnumber then
array[1],[2] = rs!date, rs!amount 'not sure about this code don't use
arrays
much
rs.movenext
else
rs.movenext
end if
wend

This is not exact but it might get you started.

Array question:
I want to match an account number entered in a form to the same account

[quoted text clipped - 4 lines]

Thanks,
JohnG

 

[Graham Mandeno]

Hi John

First, you may not even need an array here. In VBA code you can open a
Recordset based on a SQL "select" statement - for example:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sSQL as String
sSQL = "Select TranDate, TranAmount from tblTransactions" _
& " where TranAccount=" & Me.AccountNo _
& " order by TranDate;"
Set db = CurrentDb
Set rs = db.OpenRecordset( sSQL )

Then you can loop through the recordset doing whatever you like with each
record:

Do Until rs.EOF
Debug.Print rs!TranDate, rs!TranAmount
rs.MoveNext
Loop

MoveNext moves the current record pointer forward to the next sequential
record. You can also use the MoveFirst, MoveLast and MovePrevious methods,
of use Move <rows> to move forwards a given number of records (or backwards
if the number is negative).

You can set the record position directly using AbsolutePosition:
rs.AbsolutePosition = 9
will position to the 10th record (zero-based)

You can also use FindFirst to search for a record with a particular value -
for example a given TranDate:
rs.FindFirst "TranDate>=DateSerial(Year(Date(), 1, 1)"
will find the first transaction in the current year.

If you still *really* need to use an array, I suggest using a user defined
data type as your array element, because you are dealing with two different
data types:

Type Transaction
tDate as Date
tAmount as Currency
End Type

Dim aTransactions() as Transaction
....
rs.MoveLast ' necessary to ascertain the RecordCount
ReDim aTransactions( rs.RecordCount - 1) ' zero-based
rs.MoveFirst
Do Until rs.EOF
aTransactions( rs.AbsolutePosition ).tDate = rs!TranDate
aTransactions( rs.AbsolutePosition ).tAmount = rs!TranAmount
rs.MoveNext
Loop