Web Development

Ask something
Discussion Locked

Loop-D-Loop- Access 2000 VBA.

0 helpful
5 replies

by haycoconsulting

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'm writing an application in VBA for Access 2000. I'm having trouble with some nested 'Do Until' loops which include some nested If Then statements. A quick little example of what I can do with a Call or GoTo or GoSub..Return from within the above would be a great help. Where it will return under what conditions, etc. Yes an idiots guide.

5 replies

by sl-campbell

Try this (Note: this is only for testing purposes - a timer loop would never be written like this).

PART #1:

Public Function TestLoop1() As Boolean

Dim iMaxLoop1 As Integer
Dim iMaxLoop2 As Integer
Dim iMaxLoop3 As Integer

Dim iMainCounter As Integer
Dim lSubCounter2 As Long
Dim bExitLoop2 As Boolean
Dim bReturnFlag3 As Boolean
Dim bFirstTime3 As Boolean

'this will keep looping until a limit of 18 seconds has elapsed -'iMaxLoop1 x iMaxLoop2 x iMaxLoop3
iMaxLoop1 = 2 'Number of loops for the 1st loop
iMaxLoop2 = 3 'Number of loops for the 2nd loop
iMaxLoop3 = 3 'Number of seconds for the 3rd Loop

Debug.Print "Start Time: " & Time

'this will keep looping for a limit of iMaxLoop3(iMaxLoop2 x iMaxLoop1)
For iMainCounter = 1 To iMaxLoop1

bExitLoop2 = False
lSubCounter2 = 0
bReturnFlag3 = False

'this will keep looping for a limit of iMaxLoop3(iMaxLoop3 x iMaxLoop2) Do Until bExitLoop2

bFirstTime3 = True

'this will keep looping for a limit of seconds set in iMaxLoop3
Do
bReturnFlag3 = TestLoop3(bResetLoop:=bFirstTime3, lRequestedCounterMax:=iMaxLoop3)

DoEvents
bFirstTime3 = False

If bReturnFlag3 Then Exit Do
Loop

GoSub SubTest

bExitLoop2 = bReturnFlag3 And lSubCounter2 >= iMaxLoop2
Loop

Next iMainCounter

ExitProccedure:

Debug.Print "End Time: " & Time
TestLoop1 = True

Exit Function

SubTest:
lSubCounter2 = lSubCounter2 + 1
Return

End Function

by sl-campbell

Private Function TestLoop3(Optional ByVal bResetLoop As Boolean = False, Optional ByVal lRequestedCounterMax As Long) As Boolean

'TestLoop2 returns True if the number of loops are complete

Static lCounter As Long
Static lCounterMax As Long

Dim sngStartTime As Single

TestLoop3 = False

'At the callers request, reset the counter
If bResetLoop Then
lCounter = 0
lCounterMax = lRequestedCounterMax

End If

lCounter = lCounter + 1
sngStartTime = Timer

'Wait 1 second
Do While Timer - sngStartTime < 1
DoEvents
Loop

'If the function has been called the requested number of times,
'then inform the caller that operation is complete
TestLoop3 = lCounter >= lCounterMax

End Function

by sl-campbell

You may overlook it, because I forgot to add an empty line at the beginning, but in part #2 the words:

Private Function

come before the word TestLoop3

So it should read:

Private Function TestLoop3(Optional ByVal bResetLoop As Boolean = False, Optional ByVal lRequestedCounterMax As Long) As Boolean

.
.
.

This discussion is closed to new replies.