how to reference array variable in vba access to another variable

Question

hi guys im trying to take the value of data(0) and put it into say variable InvoiceNumber. I tried putting an image of my watch screen but i ended up not being allow. but here is what my watch screen would look like. data
data(0)
data(0,1) 1
data(0,2) 2
data(0,3) 3

I have tried

dim InvoiceNumber as variant <br/>
invoiceNumber = data(0)

but i keep getting error. I dont know how to reference just the part of that array. any help would be greatly appreciated.

here is the full code for anyone that would like to see a little more.

Dim db As Database
Dim rs As Recordset
Dim data As Variant
Dim colummn1 As Variant

Dim obj As Object


Set db = CurrentDb
Set rs = db.OpenRecordset("select * from Table1")
'set obj = new object
While Not rs.EOF
 'MsgBox (rs.RecordCount)
'MsgBox (rs.Fields.Count)

data = rs.GetRows(rs.Fields.Count)


Column1 = data.data(0)

Wend
rs.Close
db.Close

End Sub

Are you sure you only want the same number of records as there are columns in Table1? That's what you're doing with data = rs.GetRows(rs.Fields.Count) Also, you have typos in your variable name colummn1 vs. Column1. — Tim Williams
– Tim Williams, Commented Dec 22, 2011 at 20:09

Olivier Jacot-Descombes · Accepted Answer · 2011-12-24 16:09:23Z

1

Try

Column1 = data(0,0)

instead of

Column1 = data.data(0)

data contains a two-dimensional array. The fist index is the field number, the second index is the row number. Both start at zero. So data(0,0) is the first field of the first row. data(1,0) is the second field of the first row.

I would try to make an array of invoices by using a user defined type

Public Type Invoice
    Nr As Variant
    IssueDate As Variant
    Total As Variant
    'Or whatever your invoice contains
End Type

Public Sub TestGetRecords()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim data As Variant
    Dim numRecords As Long, i As Long
    Dim Invoices() As Invoice

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Table1")
    data = rs.GetRows()
    rs.Close
    db.Close

    numRecords = UBound(data, 2) + 1
    ReDim Invoices(0 To numRecords - 1) As Invoice
    For i = 0 To numRecords - 1
        Invoices(i).Nr = data(0, i)
        Invoices(i).IssueDate = data(1, i)
        Invoices(i).Total = data(2, i)
    Next i

    Debug.Print Invoices(0).Total
End Sub

An error in you solution is, that you placed GetRows in a loop. However, GetRows returns all the rows at once!

edited Dec 24, 2011 at 16:09

answered Dec 22, 2011 at 19:47

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user677275 Over a year ago

i have tried that but im trying to get the whole array at postion 0 to go into invoice number. so for example i would end up with <br/> invoiceNumber invoiceNumber(0) invoiceNumber(0,1) 1 invoiceNumber(0,2) 2 invoiceNumber(0,3) 3

user677275 Over a year ago

so would i have to loop through all the resulsed to get all the 0's? like data(0,0),data(0,1),data(0,3)? i guess im a little spoiled because this data = rs.GetRows(rs.Fields.Count) gets the entire table into an array but now im trying to separate each column into single arrays. any ideas?

user677275 Over a year ago

hey olive i keep getting error invalid qualifier error ? any ideas on this line Invoices.Nr = data(0, i) any ideas?

Olivier Jacot-Descombes Over a year ago

Sorry, I made an error. Since Invoices is an array of Invoice, you have to select a single invoice by Invoices(i) where i ranges from 0 to the number of invoices minus one, like Invoices(1).Nr. I corrected it.

Eric · Accepted Answer · 2011-12-23 09:51:01Z

0

I am finding it a little hard to understand what you are trying to achieve.

You are reading an entire table of data into a recordset object, loading the fields of the recordset object into a two dimensional array and then trying to iterate through the array to output the results to a single variable (or a sum of array values).

Why don't you just use an SQL expression to extract the data directly from your table?

If we can assume you have a table of invoices (say ... "myInvoiceTable") with at least the following fields ...

invoiceID invoiceValue (and probably many others, eg. invoiceDate, clientID, etc.)

you can write an expression something like this

"SELECT SUM(invoiceValue) AS MyTotal FROM myInvoiceTable WHERE invoiceID > xxxx"

This might go into your VBA code like this.

Dim rst as Recordset
Dim strSQL as string

strSQL = "SELECT SUM(invoiceValue) AS MyTotal FROM myInvoiceTable WHERE invoiceID > 400" '-- use an appropriate WHERE clause if needed

Set rst = CurrentDb.Openrecordset(strSQL) '-- the rst object should contain a single value "MyTotal" which is the sum of the fields you are trying to access

MsgBox rst!MyTotal '-- use this value wherever you need to use it

answered Dec 23, 2011 at 9:51

Eric

1812 bronze badges

2 Comments

user677275 Over a year ago

I was basically trying to get each column in my table into an array but I'm new tho acces vba. The above solution got me what I needed thanks for replying.

Eric Over a year ago

Good that you got an answer that met your needs. VBA provides a number data objects for you to use including Arrays, Collections and Recordsets it is probably worth your time reading the manual to understand them better. An understanding of SQL is also helpful but in MS Access not always necessary.

Collectives™ on Stack Overflow

how to reference array variable in vba access to another variable

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related