Solved: Help with a loop

[FrymanTCU]

Can someone show me how to clean up my code with a loop or next function. I repeat a sub procedure for these four fields from my recordset; MissingValue1, MissingValue2, MissingValue3, MissingValue4. I've tried to dim i as 1 to 4 then reference them but keep getting an error...


If Fu2RST!MissingValue1 <> "" Then
CurMisValue$ = Trim(Fu2RST!MissingValue1)
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$
If Fu2RST!MissingValue2 <> "" Then
CurMisValue$ = Trim(Fu2RST!MissingValue2)
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$
If Fu2RST!MissingValue3 <> "" Then
CurMisValue$ = Trim(Fu2RST!MissingValue3)
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$
If Fu2RST!MissingValue4 <> "" Then
CurMisValue$ = Trim(Fu2RST!MissingValue4)
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$
End If
End If
End If
End If


Side note: This would be a good VB Knowledge base topic. I have found tons of acticles on creating faster and efficient scripts in Excel but I have not found any for access that deal with recordsets and the other unique objects.

Thanks in advance.

-Rich

[Demosthine]

Hey Rich.

Would you post the sample database. The code itself isn't quite clear and by having a sample database, we can test to ensure our recommended code works before we submit it.

Scott

[ottoS13]

check the syntax on this, but the loop will be something like



dim i

i = 1

while i <= 4
CurMisValue$ = Trim(Fu2RST("MissingValue" & i))
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$

i = i + 1
wend

[CreganTur]

I agree with Otto's basic idea, but I would not use a While...Wend loop. It is an old loop structure that's only available in Access for backwards compatability. It's very limited when you compare it to any of the other available loop structures (see my article if you would like some more information).

Using the For...Next loop below should meet your needs.

Dim i

For i = 1 To 4
CurMisValue$ = Trim(Fu2RST("MissingValue" & i))
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$

Next


This code is untested, without warranty.

[FrymanTCU]

Randy,

Like always you're a rock star, that worked perfectly. There's another step I would like to add, I have a public boolean variable which if become True I would like to break the loop, how would I do that?


If AcctChgd = True then
i = 4
end if

[CreganTur]

You can use the Exit For statement- this allows you to break out of a For...Next loop. This is another reason not to use a While...Wend structure- there is no valid way to break out of that type of structure, unlike For...Next and Do...Loop structures. If your boolean is involved in the loop from above, then maybe something like this:



Dim i
For i = 1 To 4
If AcctChgd = True Then Exit For
CurMisValue$ = Trim(Fu2RST("MissingValue" & i))
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$
Next


You just need to decide how you want it to work in the loop. If the value is true the very first time you iterate through this loop, do you want the rest of the loop to run once or not? The above example will not run the code if your boolean is true.

If you want it to run once even if the boolean is true, then use:



Dim i
For i = 1 To 4
CurMisValue$ = Trim(Fu2RST("MissingValue" & i))
Check_CPS_Financials Me, Session$, Fu2RST!BranchID, Fu2RST!AcctID, CurMisValue$
If AcctChgd = True Then Exit For
Next


HTH:thumb

[FrymanTCU]

Randy,

Again thank you, I was just reading your article on loops and found the answer on the second page, do exit or in this case exit for.

[FrymanTCU]

I marked this as solved but wanted to piggy back a new Loop question...

I have 5 questions with yes/no checkboxes; Q1yes, Q2no, Q2yes, etc. and want to loop through them then assign their value to a seperate variable, intQ1, intQ2...

But I don't know how to use the i for the intQ & i part of the loop... Like always thanks for the help in advance.

Dim i
For i = 1 To 5
checkQ = Trim(Me("Q" & i & "Yes"))
If checkQ = -1 Then
intQ1 = 1
Else
checkQ = Trim(Me("Q" & i & "No"))
If checkQ = -1 Then
intQ1 = 2
Else
intQ1 = 0
End If
End If
i = i + 1
Next

[CreganTur]

You can't dynamicly work with variables like that. Trying to reference the variable intQ1 as intQ & i, where i = 1 will not work. VBA will kick it out, especially when you try to assign a value to a variable like that.

You'd be better served using an array here, since you could declare the array position (if intQ() is your array) using the position number like: intQ(i)

HTH:thumb

[FrymanTCU]

I swear this forum will teach you more than any community college night class ever could...

So I set up my array... But I dont know if there is a way to assign the variable to the intQx. Would using the select case function be an easy way to do this?

Dim varQ As Variant
varQ = Array(intQ1, intQ2, intQ3, intQ4, intQ5)

[CreganTur]

No, no- don't load your variables into the array; use the array in place of the variables.

Dim intQ(5)

Dim i As Integer

For i = 0 To 4
checkQ = Trim(Me. & "Q" & i + 1 & "Yes")
If checkQ = -1 Then
intQ(i) = 1
Else
checkQ = Trim(Me. & "Q" & i + 1 & "No")
If checkQ = -1 Then
intQ(i) = 2
Else
intQ(i) = 0
End If
End If
Next


All of your values will be loaded into the array intq(). Now, array by default start numbering at 0- unless you are using Option Base 1. I am assuming that you are not using Option Base 1, so I change your number from 1 to 5, to 0 to 4. You'll see I added 'i + 1' in a few places so that the correct objects are references, but they are loaded into the array in the correct order.

Also, I adjusted how you are referencing your objects- you had the 'Me' in place, but you were lacking the dot (.), which is necessary.

I hope this works for you.

[FrymanTCU]

checkQ = Trim(Me. & "Q" & i + 1 & "Yes") gives me a Complie Error, expected identifier or bracketed expression... Do I need to convert the integer i => into a string? The error goes away when I do Trim(Me("Q" & i +1 & "Yes")) but then the variable is not being assigned properly. I think I'm just going to end up writing this out the long way and skip the whole loop thing. Its too complicated!:think:

[CreganTur]

Okay- the cause for this is that you cannot mix the Me. keyword with concatenated variables... it just doesn't work... at least from what I've attempted so far.

My first thought was to create an object array, but VBA doesn't support object arrays. There may be a way to do this via a custom class... but I'm not certain yet.

If you need this now, then a workaround would be to explicitly reference each object.

I'll let you know if I come up with something.