Set query result as the Array

[L33]

Hi,

I am very new to Access VBA, but I'm trying to change a line of code in a macro so that it's more dynamic:

Data = Array("Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "Name7", "Name8")

Basically, I'm adding 'names' to a table every so often, but currently I'd have to physically amend this line of code to accomodate each new one.

What I want is a simple query that returns a list of all the names present, and then for VBA to pick up that list as the array.

Hope this makes sense!

 

[DCrake]

From the small amount of info you have given I think you need to look at the Split() function to create a dynamic array.

More info would help, not hinder.

David

 

[L33]

Thanks for your response.

The VBA is used to run an append query several times. The append query requires a parameter. The macro runs the query for as many times as there are names in the array, cycling through each name as the parameter each time. So the code after setting up the array is:

For i = LBound(Data) To UBound(Data)
qdf.Parameters("[Enter name]").Value = Data(i)
qdf.Execute dbFailOnError
Next i

Thanks again!