loops

[jacob94]

Here is some code I wrote that I need help adding a loop.


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryWaiting")

strWaiting = rs!Waiting

If strWaiting > 0 Then

DoCmd.OpenForm "frmSplashScreen"
DoCmd.Close acForm, "frmAutoExec"

Else

Call Shell("c:\Step1.bat", 1)
Call Shell("c:\Step2.bat", 1)

DoCmd.SetWarnings True

Application.Quit


End If


here is the frmsplashscreen code
Public intCount As Integer

Private Sub Form_Timer()

If intCount = 500 Then


Call Shell("c:\Step1.bat", 1)
Call Shell("c:\Step2.bat", 1)

DoCmd.SetWarnings True

Application.Quit

Else
intCount = intCount + 1
Me![labelname].Visible = Not Me![labelname].Visible
End If

End Sub


I need to add a counter so that when strwaiting is > 0
I need to loop strwaiting many times.

The problem is I need to have a 60 + second pause so the previous batch files I am calling have time to complete. This is why I open the frmsplash form because it has a counter in it. Can you help add a counter when the strwaiting > 0 condition is met?

 

[jacob94]

to further explain.

1) check what strwaiting is.
2) it returns 3
3) I need to loop 3 times executing the step 1 and 2 .bat files.

when the first step 1 and 2 run, i need to wait 60 seconds before i run the second step 1 and step 2 bat files and so on so forth.

I did not know how to write it in all one form so I used a seperate splash form and used a time interval counter.

 

[formerTexan]

Hello Jacob,

The first thing I see is that it appears you are making a comparison of string and numeric values ( If strWaiting > 0 Then). This is always a dodgy proposition. If "Waiting" is a non-numeric data type, then convert it (Clng, Cint, etc) to a numeric value before using these values in a numeric comparison. Warning: This will require that "Waiting" has a value that can be converted to a numeric data type.

Since it appears you want to loop through the Call Shell() procedure 3 times, then perhaps placing this in a seperate procedure will work more effectively. Then just call this procedure from wherever needed.

iLoop = Cint(rs!Waiting)
Call LoopShell(iLoop)

Private sub LoopShell(iLoop as integer)
DIm x as integer
x = 1
For x = 1 to iLoop
Call Shell("c:\Step1.bat", 1)
Call Shell("c:\Step2.bat", 1)
x = x + 1
Next x
End Sub

And finally, you can use a timer routine within your procedures without calling a splash form. But using the splash form may be a good idea to keep the user occupied during the wait.

Cheers,
Bill

 

[jacob94]

can you help me format it into this code. I am not sure where to place the loop. the strwaiting will always be a number 0 to 1000 +

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qryWaiting")

strWaiting = rs!Waiting

If strWaiting > 0 Then

DoCmd.OpenForm "frmSplashScreen"
DoCmd.Close acForm, "frmAutoExec"

Else

Call Shell("c:\Step1.bat", 1)
Call Shell("c:\Step2.bat", 1)

DoCmd.SetWarnings True

Application.Quit


End If

 

[jacob94]

i tested just the loop part and it seems like if strwaiting = 6 it runs three times??? i need it to run 6 times

 

[formerTexan]

Hi Jacob,

The loop should be placed in a seperate function (within the same module) as indicated previously.

From the looks of your code, it seems best to call the function after you open the "qryWaiting" based recordset.

Set rs = CurrentDb.OpenRecordset("qryWaiting")
iLoop = Cint(rs!Waiting)
Call LoopShell(iLoop)

And if you are still using strWaiting as a string variable in your loop, then you weren't paying close attention to my first suggestion.

Try it as suggested and then check back.

Cheers,
Bill

 

[jacob94]

Bill it seems to be working but I have one final issue. I need to some how recheck the strwaiting variable when the code is done looping. It is in that time frame that the waiting number can increase and I need to tell the loop to run x amount more.

 

[Zion7]

a couple of extraneous things, guys.

Jacob, why a string variable for a counter?(as Tex said 2x)
especially when attempting a numeric comparison
If strWaiting > 0 ...will NEVER be true!

Tex, why increment a counter(x = x + 1), within a For loop?
are you incrementing by 2?
Maybe, For x = 1 To intLoop Step 2

Jacob, why open a recordset?
intLoop = DLookUp("Waiting","qryWaiting")

 

[formerTexan]

Jacob,

First, my bad. Zion7 is rather more alert and absolutely correct in pointing out that the (x = x + 1) counter is out of place in the loop I suggested. Drop this line of code.

If you want the loop to again cycle (x) number of times, then:

iLoop = x   'replace x with whatever number[\code]
[code]Call LoopShell(iLoop)[\code]

If you want to visually check the value of a variable, then insert the following code line wherever needed in your procedures and then open the Immediate Window (View > Immediate Window) to see the results after the code is run:

[code]Debug.print NameOfVariable[\code]

Or you can place a breakpoint on a code line and when the code halts, open the Locals window (View > Locals Window) and have a look at all of your variables and their current values.

Cheers,
Bill

 

[jacob94]

formertexan:
That is what you first suggested and loop shell still has the x=x+1 in it???

Private sub LoopShell(iLoop as integer)
DIm x as integer
x = 1
For x = 1 to iLoop
Call Shell("c:\Step1.bat", 1)
Call Shell("c:\Step2.bat", 1)
x = x + 1
Next x
End Sub



zion: thanks for the pointers how do I rewrite the loop to loop this many time:

intLoop = DLookUp("Waiting","qryWaiting")

 

[Zion7]

... as Tex said, remove the "x = x + 1"

__________________________________________________
...
Dim intLoop As Integer

intLoop = Nz(DLookUp("Waiting","qryWaiting"),0)

If intLoop <> 0 Then Call LoopShell(intLoop)
...

________________________________________________


Private sub LoopShell(intLoop as integer)
DIm x as integer

For x = 1 to intLoop
Call Shell("c:\Step1.bat", 1)
Call Shell("c:\Step2.bat", 1)
Next x
End Sub

______________________________________________________