Help obtaining values from table to put in array

[downhilljon]

Hi,

Have had a big break from coding, so feeling a little stupid. Hope someone can help!

The following method seems to me to be the best solution (with my limited knowledge), but am willing to accept suggestions on better methods.

In the big picture, I have a table (tblBrands) with the primary key as an autonumber, specifiying the BrandID. During the course of building and testing the database, the autonumber has skyrocketed, so while I only have 12 records in tblBrands, the autonumber currently goes upto a value of 110.

I'd like to obtain each of the 12 BrandID values for use later on, and thought an array would be handy to store them in. However I can't for the life of me figure out how to get the values from the table!

Here is my code so far:

intBrands = DCount("BrandID", "tblBrands")
 
'Create loop to go through tblBrands correct number of times, storing BrandID's in an array.
 
ReDim varArray(1 To intBrands)
For intCounter = 1 To intBrands
    'varArray(intCounter) = and here I need to get the value from tblBrands
Next intCounter

As an alternate method I believe I could open a recordset to obtain the values, but don't really understand recordsets, and as I am just making some small changes to my database, don't really want to invest time into learning a whole new area.

Hope someone has a suggestion!
Cheers,
Jon

 

[DCrake]

Ok lets say you have 100 brands in a table you first declare the number of elements in the array. (this could be automated to match the record count)

Dim varArray(99,1) '2 dimensional array 100 by 2 (zero based)
Dim nIndex As Integer ' counter value
Next we need to get the records from the table using vba

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("TblBrands")

If Not Rs.EOF Then
   Do Until Rs.EOF
      varArray(nIndex,0) = Rs("BrandID")
      varArray(nIndex,1) = Rs("BrandName")
      nIndex = nIndex + 1
      Rs.MoveNext
   Loop
   Rs.Close
End If
Set Rs = Nothing

If we now put this code in to a sub in a module we can call this at any point in the application life span.

Public Sub PopulateBrandArray()
Public varArray(99,1) '2 dimensional array 100 by 2 (zero based)
Dim nIndex As Integer ' counter value
Next we need to get the records from the table using vba

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("TblBrands")

If Not Rs.EOF Then
   Do Until Rs.EOF
      varArray(nIndex,0) = Rs("BrandID")
      varArray(nIndex,1) = Rs("BrandName")
      nIndex = nIndex + 1
      Rs.MoveNext
   Loop
   Rs.Close
End If
Set Rs = Nothing

End Sub

Now when we first open the application we can call this sub routine

Call PopulateBrandArray


To that end we now have a two dimensional array containing all the brand codes in col(0) and descriptions in col(1). We can now use this array anyway we want to. Hope this helps

David

 

[downhilljon]

That is fantastic David! Thanks for the quick and thorough reply. I'll give it a go this arvo, and let you know if there are any problems!

Cheers,
Jon

 

[gemma-the-husky]

but why do you need them in an array, when they are already in the table?

the way to go is to let access do the work

try to think in terms of processing sets of data using queries, to produce the answers you want.

In general its quite different to the way you might manage a spreadsheet

 

[downhilljon]

OK David, that did the trick! Thanks again.

Also, Thanks gemma-the-husky for the suggestion - I definitely appreciate the simpler approach, and if I had more time I would explore it, but right now I have my solution, so I'm happy!

Cheers,
Jon