How do you refer to a field or variable name built dynamically in VBA

[Phil Davis]

Hi, I'm relatively new to VBA (6.3) but have many years coding experience in other languages.

I'm executing an ACCESS SQL query from VBA which returns a single record with fields names C1, C2 ..... C12.

I'd like to assign the (numeric) values from these fields to elements of an array.

Rather than writing simple code such as:

Dim Poprange(11) As Integer

poprange(0) = !C1
poprange(1) = !C2
..
poprange(11) = !C12

I'd like to do something like:

Dim I As Integer
Dim Poprange(11) As Integer
For I = 0 To 11
  suffix = CStr(I + 1)
  Poprange(I) = "!C" & suffix
Next

But I get "Error number 13: Type mismatch" when I run it. I've experimented with CInt and CStr conversion functions but to no avail. I assume I am not able to form the field name !C1 etc and get VBA to interpret it as such!

Is there a (better) way to do this?

Best Regards, Phil

[Stewart Ross]

Hi Phil. I'm assuming that you have an open recordset to refer to (which you haven't listed in your code segment above). As a shorthand for it I'm referring to recordset below to represent an open recordset object variable.

The key part of implementing what you want to achieve is to know that there are several ways to refer to fields, of which the recordset!field name way is perhaps the least flexible. Underlying each recordset is a fields collection, and when you refer to a field using the bang notation above there is internal parsing done which means you can't use a concatenated variable name to access the field. To access the field on a computed basis use either

recordset(field no)

where fieldno is an integer representing the position of the field in the collection (a zero-based value), or more usefully

recordset(field name)

where the fieldname is a string value that can come from a computed expression.

In the example you give, your code would become something like:

Dim I As Integer
Dim Poprange(11) As Integer
Dim RS as DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SomeQueryName")
For I = 0 To 11
  suffix = CStr(I + 1)
  Poprange(I) = RS("C" & suffix)
Next
RS.Close

-Stewart

[Phil Davis]

Many thanks Stewart, that has worked perfectly. I think I need to dig deeper into the documentation but its so scattered and hard to find!

For example, I'd now like to sort the array just populated. The on-line MS VB reference suggests using Array.Sort(arra yname) along the lines of:

Private Sub sortAnimals()
Dim zooAnimals(2) As String
zooAnimals(0) = "lion"
zooAnimals(1) = "turtle"
zooAnimals(2) = "ostrich"
Array.Sort(zooA nimals)
End Sub

but that produces a compile error in my (MS Access) environment. I suspect I'm missing access to Mscorlib.dll and the System namespace as the book also suggests but where to look to find how you fix that is proving very difficult.

Do you have recommendations for reading matter that will provide a basic understanding of what is going on please?

Best Regards, Phil

[Stewart Ross]

Hi Phil. You'll find there are features in the Visual Basic language which are not available in VBA (Visual Basic for Applications), and I am sure that built-in sorting of arrays is one of them!

Arrays are not objects as such in VBA, so they don't have built-in methods.

Although VB and VBA have many similarities what is available in VBA is more of a subset of VB-like nature. It is unfortunate that the differences can constrain what is done with VBA (not that you can't sort an array, just that it is not a built-in object property). If you want to sort your array you can always resort to simple but inefficient bubble-sort, or any of the other more-efficient ways to do it, but you'll need to code it yourself.

A reference text I can recommend is the VBA Developers Handbook by Getz and Gilbert. Very thorough and wide-ranging coverage - my copy has proven invaluable over the last few years.

In a different way, so has Building Microsoft Access Applications by John L. Viescas, which focuses more on a range of smaller-scale applications to demonstrate generic techniques.

-Stewart

[Phil Davis]

Hi Stewart, Ah I see. Looking closer at the on-line reference the description of Array.Sort it is VB & not VBA specific. It's those little assumptions that are getting in the way. I'll order those books asap !!

BTW I fixed my sort problem in the underlying SQL !! but its good to know how to implement in VBA for future reference.

Many thanks for your help - you've saved quite a few of the last remaining brain cells & follicles today !!

Best Rgds
Phil