array help

[Tony1258]

I have a table that can contain from 1 to 60 date values. I need to write code that will sequentially step thru each date value in the table and use it as a input to multiple queries set up in a macro. Not sure how to write the code to step through each value and run the queries. Any help is appreciated.

Thanks

 

[ted.martin]

Maybe something like this would help within a form.

Dim myArray as Variant
Dim iVar as Integer

ReDim myArray (1 to 60)

For iVar = 1 To 60

Ff Me.Controls("myArray" & iVar).Value = <whatever> then

Else: end if

Next iVar

 

[The_Doc_Man]

There is such a thing as opening the table with the dates and stepping through the recordset.

The catch with the macro-driven queries is that they might have an issue with that externally defined parameter.

To be honest, I wouldn't do it QUITE that way. But all is not lost. You have the macro. There is an option to convert the macro to VBA. if you did that, you could see how each of your queries is run. You could then convert each query to a PARAMETER query (q.v. in the help files).

So if you build a loop to scan the recordset with the dates, and once for each record in that controlling recordset, run the VBA code you converted, and just supply the value from the controlling recordset as a parameter for each query inside the loop, that would do it for you.

Leaving the macro AS A MACRO will eat your socks for you because of the issues involved with parameter queries in macros.

 

[Token Jester]

I have a table that can contain from 1 to 60 date values. I need to write code that will sequentially step thru each date value in the table and use it as a input to multiple queries set up in a macro. Not sure how to write the code to step through each value and run the queries. Any help is appreciated.

Thanks

Are you attempting to do this in a form? If so, you can always check a field for information then run your queries, and move to the next record.

Do while [ID FIELD] > 0
or
Do While Not me.[DATE FIELD] <> ""
'''run queries
'''You may also want to place some validation of the date field here and if it's an invalid field, then Exit Do
DoCmd.GoToRecord
Loop