MS Access VBA: Using variables to build Dim statements

Question

I need to build multiple Dim statements I have:

Dim p As String
Dim purch As String
Dim counter As Integer
counter = 0
Do While rst.EOF = False
counter = counter+1
p = "Purchase" & counter
purch = "Dim " & p & " As Purchase"
purch
rst.MoveNext
Loop

This throws a compile error: Expected Sub, Function or Property

The loop is building my command correctly but I can't get the command to run from the variable where it is stored.

Eh... What do you expect this to actually do? Do you move that variable later on? What's As Purchase? — Erik A
– Erik A, Commented Jun 14, 2018 at 18:52
As Purchase refers to a User Defined type. Type Purchase cardHolderID As String cardHolderName As String department As String statementDate As Date budgetCode As String transactionDate As Date vendor As String purchaseAmount As Currency requestedBy As String description As String End Type — John Ring
– John Ring, Commented Jun 14, 2018 at 18:55
Dim statements aren't executable. What are you trying to achieve exactly? — Mathieu Guindon
– Mathieu Guindon, Commented Jun 14, 2018 at 18:56
Back up a few steps and explain what you're trying to do, overall. You can redimension variables if necessary but I suspect that's not the case here either. — ashleedawg
– ashleedawg, Commented Jun 14, 2018 at 18:56
Looks like you need to learn about arrays and/or collections. Given that the number of iterations isn't known in advance, I'd go with a Collection. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 14, 2018 at 18:57

Mathieu Guindon · Accepted Answer · 2018-06-14 19:10:08Z

Code isn't a bunch of executable strings. You can't "execute" or "invoke" a string literal.

purch

Isn't a valid executable instruction, regardless of whether the value of that variable contains perfectly valid VBA code - as far as VBA is concerned, that's not code, that's a local variable, nothing more... the compiler is telling you that it doesn't know what to do with an instruction that does nothing.

I am attempting to break up the row into a Purchase object for each budgetCode

You'll soon learn that a Type isn't an object, and won't play well with a Collection. Also, with 370 columns in a pivoted recordset, you're making your life much harder than it needs to be. If you mean to iterate budget codes, make your query return the budget codes and iterate that.

You'll need a class module - in its simplest form, it could be just a bunch of public fields:

'class PurchaseItem
Public ID As Long
Public Name As String
Public PurchaseDate As Date
Public BudgetCode As String
Public TransactionCode As String
'...

Now as you iterate your recordset, you create a New PurchaseItem every time, and Add it to a Collection:

Dim items As Collection
Set items = New Collection

Dim current As PurchaseItem
Do While Not rst.EOF
    Set current = New PurchaseItem
    current.ID = rst.Fields("ID").Value
    current.Name = rst.Fields("Name").Value
    current.PurchaseDate = rst.Fields("Date").Value
    current.BudgetCode = rst.Fields("BudgetCode").Value
    current.TransactionCode = rst.Fields("TransactionCode").Value
    '...
    items.Add current
    rst.MoveNext
Loop

And now you have all your items in the items collection.

Thank you. I'm very much a VBA newb. I was not aware of collections.

Collectives™ on Stack Overflow

MS Access VBA: Using variables to build Dim statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related