Question about Run-to cursor--I want to "start at cursor"

  • Thread starter Thread starter Dea
  • Start date Start date
D

Dea

Is there a way to start an Access Module at any other place other than the
top. Similar to "go to". I have seen the run to cursor option where it
stops at the cursor, I want it to "start at cursor"
 
Dea said:
Is there a way to start an Access Module at any other place other than the
top. Similar to "go to". I have seen the run to cursor option where it
stops at the cursor, I want it to "start at cursor"
Click to expand...

The command you need is 'Set next statement', which actually means 'set next
statement to be executed', so:

1. Place the cursor on the required code line
2. Select the menu command 'Debug | Set next statement'
 
How does the "Set next datement" get activated? I put my cursor where I
wanted the module to start and my "set next statement" is greyed out on
the
Debug menu.
Click to expand...

You need to be in debug mode for it to be active. Just put your cursor at
the sub you want to run and hit F8, then goto the line you want to start
from and do Ctrl+F9 (set next statement). You can also just put a breakpoint
at the line in question and hit F5. The difference will be that using the
breakpoint, all the code from the beginning of the proc will run whereas
using set next statment, it doesn't.

HTH

Matt
 
This is what I really want to accomplish:

I have an Access module that has 62 "strsql" statements that generate files,
performs calculations, bring in extract fields, etc. The end result of this
very long module is that it generates a large file. Sometimes I want to run
the module starting at a different place other than the top (or first strsql
statement). If I know that the all the files are created up to a certain
point and don't want to have the whole module run...maybe just run to
recreate the last 3 strsql statments, then I want to start at that point--to
just, say, run the last 3 sql statements. I will not necessarily be in debug
mode at this point.
 
Dea said:
This is what I really want to accomplish:

I have an Access module that has 62 "strsql" statements that generate
files,
performs calculations, bring in extract fields, etc. The end result of
this
very long module is that it generates a large file. Sometimes I want to
run
the module starting at a different place other than the top (or first
strsql
statement). If I know that the all the files are created up to a certain
point and don't want to have the whole module run...maybe just run to
recreate the last 3 strsql statments, then I want to start at that
point--to
just, say, run the last 3 sql statements. I will not necessarily be in
debug
mode at this point.
Click to expand...


One approach would be to change the procedure so that it takes an optional
argument telling it where to start. The code would examine that argument to
determine which of the steps can be skipped. Then if you want to start at
step 3, you would call it like this:

DoMyStuff 3

If you want to do everything, you could leave off the argument:

DoMyStuff

.... or pass the argument representing the first step:

DoMyStuff 1
 
Great suggestion. I know enough JetSQL get by, so I need to bother you with
a further question on DoMyStuff, etc.

How do program the procedure so that it takes an optional argument? Is it a
Docmd.? Also, would I put the "DoMyStuff 3" at the point where I want the
code to run? I understand the argument being a 1 or 3, but just don't really
understand how to programmatically get 'er done.

Example, here is part of my code for two steps getting High school in one
step and Legacy in another; If I want to start at the legacy step and run to
the end where would I put the "DoMyStuff 3....:

DoCmd.RunSQL strSQL

'QM_High_School

strSQL = " SELECT T_Fall_Admits.SARADAP_PIDM INTO T_High_School" & _
" FROM T_Fall_Admits
DoCmd.RunSQL strSQL

'QM_Legacy_Admits

strSQL = " SELECT T_Admits.SARAPPD_PIDM " & _
" FROM T_Admits ;"
DoCmd.RunSQL strSQL
 
Assuming your procedure is named do my stuff then

PUBLIC Sub DoMyStuff (Optional StartWhere as Long = 0)

IF StartWhere < 1 Then
Do step 1
End IF

If startWhere <2 then
do step 2
End if

If StartWhere < 3 then
do step 3
END IF

....

END SUB

Then when you want to do all the steps you call
DoMyStuff

When you want to start with step 20
DoMyStuff 20

If you wanted to get fancy and be able to specify a range of step you could
have two optional values
PUBLIC Sub DoMyStuff (Optional StartWhere as Long = 0, Optional EndWhere as
Long = 10000)

If StartWhere <1 AND EndWhere >= 1 Then
Do step 1
End IF

If StartWhere <2 AND EndWhere >= 2 Then
Do step 1
End IF
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Dea said:
Great suggestion. I know enough JetSQL get by, so I need to bother you
with
a further question on DoMyStuff, etc.

How do program the procedure so that it takes an optional argument? Is it
a
Docmd.?
Click to expand...

No, it's in the Sub or Function header. There are several ways to implement
optional arguments, but essentially you just declare the argument as
Optional. Depending on the declared data type of the argument, it will have
a predefined value if it wasn't specified: numeric types have a value of 0,
string arguments have a value of "" (zero-length string), and Variant
arguments get a special value indicating that the argument is missing. You
can also specify in the declaration of the argument what value you want it
to have if the caller doesn't pass it, overriding the default for the data
type.

For your purposes, it's probably simplest if you use a numeric argument
specifying the step you want to start with, and allow the default value of 0
to indicate that you should start at the begining. If you currently have a
Sub procedure named DoMyStuff, you might declare it like this:

Sub DoMyStuff(Optional StartStep As Integer)
Also, would I put the "DoMyStuff 3" at the point where I want the
code to run? I understand the argument being a 1 or 3, but just don't
really
understand how to programmatically get 'er done.
Click to expand...

It isn't clear to me how you intend to invoke the procedure in the first
place. If this is something you are just doing manually and have no form
event to trigger it (like clicking a command button), you can always invoke
it from the Immediate Window. You would press Ctrl+G if necessary to open
the Immediate Window, and enter the procedure call there:

DoMyStuff 3
Example, here is part of my code for two steps getting High school in one
step and Legacy in another; If I want to start at the legacy step and run
to
the end where would I put the "DoMyStuff 3....:

DoCmd.RunSQL strSQL

'QM_High_School

strSQL = " SELECT T_Fall_Admits.SARADAP_PIDM INTO T_High_School" & _
" FROM T_Fall_Admits
DoCmd.RunSQL strSQL

'QM_Legacy_Admits

strSQL = " SELECT T_Admits.SARAPPD_PIDM " & _
" FROM T_Admits ;"
DoCmd.RunSQL strSQL
Click to expand...

Here's an example of how the procedure might look:

'------ start of example code ------
Sub DoMyStuff(Optional StartStep As Integer)

On Error GoTo Err_Handler

Dim strSQL As String
Dim intCurrStep

' Step1: QM_High_School
intCurrStep = 1
If StartStep <= intCurrStep Then
strSQL = _
"SELECT T_Fall_Admits.SARADAP_PIDM INTO T_High_School" & _
" FROM T_Fall_Admits
DoCmd.RunSQL strSQL
End If

' Step2: QM_Legacy_Admits
intCurrStep = 2
If StartStep <= intCurrStep Then
strSQL = _
"SELECT T_Admits.SARAPPD_PIDM " & _
" FROM T_Admits ;"
'*** NOTE: THE ABOVE SQL IS NOT VALID,
'*** THOUGH IT IS WHAT YOU POSTED.
'*** YOU CAN'T "RUNSQL" A SELECT QUERY.
'*** THIS SHOULD PROBABLY BE AN APPEND QUERY.
DoCmd.RunSQL strSQL
End If

Exit_Point:
Exit Sub

Err_Handler:
MsgBox _
"An error occurred in step " & intCurrStep & _
". The error message was:" & vbCr & vbCr & _
Err.Description & _
vbCr & vbCr & "The current SQL statement was:" & _
vbCr & vbCr & strSQL, _
vbExclamation, _
"Error " & Err.Number

Resume Exit_Point

End Sub
'------ end of example code ------

Does that make the approach clear? Note that the above procedure only has
two steps, so "DoMyStuff 3" doesn't really make sense with that version.
 
This is what I really want to accomplish:

I have an Access module that has 62 "strsql" statements that generate
files,
performs calculations, bring in extract fields, etc. The end result of
this
very long module is that it generates a large file. Sometimes I want to
run
the module starting at a different place other than the top (or first
strsql
statement). If I know that the all the files are created up to a certain
point and don't want to have the whole module run...maybe just run to
recreate the last 3 strsql statments, then I want to start at that
point--to
just, say, run the last 3 sql statements. I will not necessarily be in
debug
mode at this point.
Click to expand...


You could do something like this:


Sub tester()

Test 3

End Sub

Sub Test(Optional lStart As Long)

Select Case lStart
Case 1: GoTo 1
Case 2: GoTo 2
Case 3: GoTo 3
Case 4: GoTo 4
Case 5: GoTo 5
End Select

1
Debug.Print "Testing 1"
2
Debug.Print "Testing 2"
3
Debug.Print "Testing 3"
4
Debug.Print "Testing 4"
5
Debug.Print "Testing 5"

End Sub

' Use this to build your select statement much faster ;)
' just run it and copy the code from the immediate window

Sub makecode()

Dim x As Long

Debug.Print "Select Case lStart"
For x = 1 To 62
Debug.Print vbTab & "Case " & x & ":" & " goto " & x
Next
Debug.Print "End Select"

End Sub


HTH

Matt
 
Thanks to you John..I'll give it a try!

John Spencer said:
Assuming your procedure is named do my stuff then

PUBLIC Sub DoMyStuff (Optional StartWhere as Long = 0)

IF StartWhere < 1 Then
Do step 1
End IF

If startWhere <2 then
do step 2
End if

If StartWhere < 3 then
do step 3
END IF

....

END SUB

Then when you want to do all the steps you call
DoMyStuff

When you want to start with step 20
DoMyStuff 20

If you wanted to get fancy and be able to specify a range of step you could
have two optional values
PUBLIC Sub DoMyStuff (Optional StartWhere as Long = 0, Optional EndWhere as
Long = 10000)

If StartWhere <1 AND EndWhere >= 1 Then
Do step 1
End IF

If StartWhere <2 AND EndWhere >= 2 Then
Do step 1
End IF
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.
Click to expand...
 
Thanks Dirk, I will try it out.

Dirk Goldgar said:
No, it's in the Sub or Function header. There are several ways to implement
optional arguments, but essentially you just declare the argument as
Optional. Depending on the declared data type of the argument, it will have
a predefined value if it wasn't specified: numeric types have a value of 0,
string arguments have a value of "" (zero-length string), and Variant
arguments get a special value indicating that the argument is missing. You
can also specify in the declaration of the argument what value you want it
to have if the caller doesn't pass it, overriding the default for the data
type.

For your purposes, it's probably simplest if you use a numeric argument
specifying the step you want to start with, and allow the default value of 0
to indicate that you should start at the begining. If you currently have a
Sub procedure named DoMyStuff, you might declare it like this:

Sub DoMyStuff(Optional StartStep As Integer)


It isn't clear to me how you intend to invoke the procedure in the first
place. If this is something you are just doing manually and have no form
event to trigger it (like clicking a command button), you can always invoke
it from the Immediate Window. You would press Ctrl+G if necessary to open
the Immediate Window, and enter the procedure call there:

DoMyStuff 3


Here's an example of how the procedure might look:

'------ start of example code ------
Sub DoMyStuff(Optional StartStep As Integer)

On Error GoTo Err_Handler

Dim strSQL As String
Dim intCurrStep

' Step1: QM_High_School
intCurrStep = 1
If StartStep <= intCurrStep Then
strSQL = _
"SELECT T_Fall_Admits.SARADAP_PIDM INTO T_High_School" & _
" FROM T_Fall_Admits
DoCmd.RunSQL strSQL
End If

' Step2: QM_Legacy_Admits
intCurrStep = 2
If StartStep <= intCurrStep Then
strSQL = _
"SELECT T_Admits.SARAPPD_PIDM " & _
" FROM T_Admits ;"
'*** NOTE: THE ABOVE SQL IS NOT VALID,
'*** THOUGH IT IS WHAT YOU POSTED.
'*** YOU CAN'T "RUNSQL" A SELECT QUERY.
'*** THIS SHOULD PROBABLY BE AN APPEND QUERY.
DoCmd.RunSQL strSQL
End If

Exit_Point:
Exit Sub

Err_Handler:
MsgBox _
"An error occurred in step " & intCurrStep & _
". The error message was:" & vbCr & vbCr & _
Err.Description & _
vbCr & vbCr & "The current SQL statement was:" & _
vbCr & vbCr & strSQL, _
vbExclamation, _
"Error " & Err.Number

Resume Exit_Point

End Sub
'------ end of example code ------

Does that make the approach clear? Note that the above procedure only has
two steps, so "DoMyStuff 3" doesn't really make sense with that version.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Note that if all the steps can match an executable string (trough eval or
trough DoCmd.RunSQL, or trough CurrentDb.Execute, or otherwise), you can
define an array of strings:

Dim cmds(1 To 6) As String
cmds(1) = "... "
...
cmds(6) = "..."

Dim i As Long
Dim n As long : n = UBound(cmds)

For i = start to n
... evaluate the string cmds(i)
Next i



where start indicates the first command to be executed.


Another more flexible way would be to fill a Collection (with executable
strings) and execute each of them with a For Each. Sure, a real example
would include error trapping (and probably, could make use of a
transaction).



Vanderghast, Access MVP
 
Back
Top