trouble setting up a loop

[teach314]

My query (qryVal) output looks like this, sorted by Val1 then Val2:

Val1 Val2
1 4
1 9
1 13
...
1 42
2 5
2 9
etc...


In code module, I call function Process(Val1). Inside this function is a loop that needs to cycle through each value of Val2. For example, if I call Process(1) , the loop needs to run through values of Val2 = 4, 9, 13,.... 42.

How do I set up this loop? I'm having trouble with the syntax.
Thanks in advance.

 

[PHV]

I'm having trouble with the syntax
Which syntax ?













?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

post your code so far . What is the desired result of the loop -"run through" and do what?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[MajP]

What is the "processing"? Probably just need an update query and no code.

 

[teach314]

hi - I'm trying to do something like this:

Function Process(Val1)
Dim AvailableVal2()
Dim Val2 as Variant

??? how do I best assign values to the array AvailableVal2()
from the query shown above in the first posting.
I also need to do a count so I can ReDim the array to
match the number of Val2 values correspond to Val1.

For Each Val2 In AvailableVal2
Call CalcResults(Val1, CInt(Val2))
Next Val2

End Function


I'm fairly new to this, and I'm having trouble 1) assigning values to the array, 2) ReDimming the array.

also, if there's a better way to do this, I'd be interested.

Thanks in advance, Teach314

 

[lionelhill]

If you want a function to loop through a set of values derived from a query, it's easiest to put the query in the function, create a recordset there, and loop through it.

In effect you'd split your query and find all relevant values of Val1 in one query as you are doing at the moment, you'd pass Val1 to the function, which would build a query WHERE Val1 = value-I-passed, create a recordset based on this query, and loop through the recordset doing whatever is necessary on Val2. Does this make sense?

I find the easiest way to build queries for use in functions is to copy the sql from the query design page, and paste it into the code that sets up a string variable, with appropriate modification.

 

[teach314]

Thanks lionelhill

"Does this make sense?" - Yes, I think so. I'll give it try - I'm fairly new at this, so I appreciate the advice you've given.