CODEWITHSUNDEEP
ms-accessvba
user677275
user677275

Reputation: 77

how to reference array variable in vba access to another variable

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

Upvotes: 0

Views: 9870

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112269

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!

Upvotes: 1

Eric
Eric

Reputation: 181

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

Upvotes: 0

Related Questions