pausing code until query completes 
Author Message
 pausing code until query completes

In an access (.mdb) database, I have a form with an event
procedure that runs two separate update queries.  I need
to use a calculated field on the form as part of the
second query.  The problem is that the first query changes
the records that the form field is calculated from.  So I
need to make sure that the first query is completed and
the form field recalculated before I perform the second
query.  Can anybody help me with this?  I'm really having
a hard time finding any help file material on this
particular subject.  Thank you!


Fri, 11 Feb 2005 23:02:13 GMT  
 pausing code until query completes
Create a function that runs your action queries, using
docmd.runSQL or db.Execute inside of it. The Function
should have the query name passed to it. This will run
your action query then pass control back to your module to
drop thru the rest of your code.

function Whatever()
~~~~~~~~

'This line should be the Function with Query Name to run
your action query. Control goes to it, then comes back to
the module it is called in, thereby, giving you a pause
effect.
call RunActionQueries("QueryName1")
call RunActionQueries("QueryName2")

'Resume with code

End Function

Quote:
>-----Original Message-----
>Message unavailable



Fri, 11 Feb 2005 23:38:13 GMT  
 pausing code until query completes
Thanks for the reply, but I tried this and I still have
the same problem.  The action query starts in the first
called procedure, but it clearly does not complete before
control passes back to the calling procedure and on to the
second called procedure.  Any other ideas out there?
Quote:
>-----Original Message-----
>Create a function that runs your action queries, using
>docmd.runSQL or db.Execute inside of it. The Function
>should have the query name passed to it. This will run
>your action query then pass control back to your module
to
>drop thru the rest of your code.

>function Whatever()
>~~~~~~~~

>'This line should be the Function with Query Name to run
>your action query. Control goes to it, then comes back to
>the module it is called in, thereby, giving you a pause
>effect.
>call RunActionQueries("QueryName1")
>call RunActionQueries("QueryName2")

>'Resume with code

>End Function

>>-----Original Message-----
>>Message unavailable
>.



Sat, 12 Feb 2005 00:49:28 GMT  
 pausing code until query completes

Quote:

>In an access (.mdb) database, I have a form with an event
>procedure that runs two separate update queries.  I need
>to use a calculated field on the form as part of the
>second query.  The problem is that the first query changes
>the records that the form field is calculated from.  So I
>need to make sure that the first query is completed and
>the form field recalculated before I perform the second
>query.

The Execute method should do what you want.  There are quite
a few options so check Help for details.  Pay particular
attention to the discussion of dbFailOnError and
RecordsAffected.

--
Marsh
MVP [MS Access]



Sat, 12 Feb 2005 03:58:14 GMT  
 pausing code until query completes
Hey Marsh-
Thanks for your reply!  I'm afraid that I don't understand
what you have in mind though.
I am using the Execute method, and I looked at the Help
file on it, particularly the dbFailOnError and
RecordsAffected options, but I don't understand how you
think I can solve my problem.  I put the code below to
clarify the problem (sorry it's so ugly):
When the update query in line 10 executes, the records
from which the validAssay text box's value is calculated
are changed.  However, before these changes can appear on
the form, the code proceeds to line 11 and assigns
validAssayStr the value of validAssay PRIOR to the changes
appearing on the form.  This value is then used in the
next update query (line 12), when what I need to be used
in this line is the value of validAssay AFTER the changes
appear on the form.

1. Private Sub dataChangeUpdate()
2.   Dim assayNumberStr As String
3.   Dim slopeStr As String
4.   Dim interceptStr As String
5.   Dim validAssayStr As String
6.   Set dbs = CurrentDb
7.   assayNumberStr = Me!assayNumber.Value
8.   slopeStr = CStr(Me!slope.Value)
9.   interceptStr = CStr(Me!intercept.Value)
10. dbs.Execute "UPDATE assayControls " & _
        "SET controlDetermination = (rawData1 - " &
interceptStr & ") / " & _
        slopeStr & " * 39.65 " & _
        "WHERE assayNumber = " & assayNumberStr & " " & _
        "AND assayControlID = '1'"
11. validAssayStr = CStr(Me!validAssay.Value)
12. dbs.Execute "UPDATE assaySamples " & _
        "SET sampleDetermination = (rawData1 - " &
interceptStr & ") / " & _
        slopeStr & " * 39.65, " & _
        "validDetermination = " & validAssayStr & " " & _
        "WHERE assayNumber = " & assayNumberStr
13.End Sub

Thanks again for anything you can do!
-warren

Quote:
>-----Original Message-----
>The Execute method should do what you want.  There are
quite
>a few options so check Help for details.  Pay particular
>attention to the discussion of dbFailOnError and
>RecordsAffected.

>--
>Marsh
>MVP [MS Access]



Sat, 12 Feb 2005 23:58:54 GMT  
 pausing code until query completes

Quote:

>I am using the Execute method, and I looked at the Help
>file on it, particularly the dbFailOnError and
>RecordsAffected options, but I don't understand how you
>think I can solve my problem.

The point I was trying to make is that the Execute method
run synchronously with the rest of your code.  This means
that the statement after the Execute can work with the
updated records.  Unfortunately for your assumptions,
updating the form's recordset and recalculating control
values are asynchronous operations.

Quote:
>                            I put the code below to
>clarify the problem (sorry it's so ugly):
>When the update query in line 10 executes, the records
>from which the validAssay text box's value is calculated
>are changed.  However, before these changes can appear on
>the form, the code proceeds to line 11 and assigns
>validAssayStr the value of validAssay PRIOR to the changes
>appearing on the form.  This value is then used in the
>next update query (line 12), when what I need to be used
>in this line is the value of validAssay AFTER the changes
>appear on the form.

>1. Private Sub dataChangeUpdate()
>2.   Dim assayNumberStr As String
>3.   Dim slopeStr As String
>4.   Dim interceptStr As String
>5.   Dim validAssayStr As String
>6.   Set dbs = CurrentDb
>7.   assayNumberStr = Me!assayNumber.Value
>8.   slopeStr = CStr(Me!slope.Value)
>9.   interceptStr = CStr(Me!intercept.Value)
>10. dbs.Execute "UPDATE assayControls " & _
>        "SET controlDetermination = (rawData1 - " &
>interceptStr & ") / " & _
>        slopeStr & " * 39.65 " & _
>        "WHERE assayNumber = " & assayNumberStr & " " & _
>        "AND assayControlID = '1'"
>11. validAssayStr = CStr(Me!validAssay.Value)
>12. dbs.Execute "UPDATE assaySamples " & _
>        "SET sampleDetermination = (rawData1 - " &
>interceptStr & ") / " & _
>        slopeStr & " * 39.65, " & _
>        "validDetermination = " & validAssayStr & " " & _
>        "WHERE assayNumber = " & assayNumberStr

The problem you're having is caused by Access control
recalculation being asynchronous from the code you're
running.  It's best to leave the controls for display
purposes and not rely on them to be calculated at any
specific point in time.  Do the calculation (instead of line
11) in your code.  (At a minimum, the form will have to be
requeried before it will see the updated values.)

In this case, you may(?) be able to get away with doing a
Me.Requery (to force the form to retrieve the updated
records) followed by a Me.Recalc and possibly one or more
DoEvents (to give the form time to do the calculations).
This is NOT a solution to the problem, but it might work
(most of the time).

--
Marsh
MVP [MS Access]



Sun, 13 Feb 2005 03:18:53 GMT  
 pausing code until query completes
Did you try stepping through the code when you had it set
to call functions?
At what point does it leave the function and continue on
the calling code?

Tim Hansen

============================

Quote:
>-----Original Message-----
>Thanks for the reply, but I tried this and I still have
>the same problem.  The action query starts in the first
>called procedure, but it clearly does not complete before
>control passes back to the calling procedure and on to
the
>second called procedure.  Any other ideas out there?
>>-----Original Message-----
>>Create a function that runs your action queries, using
>>docmd.runSQL or db.Execute inside of it. The Function
>>should have the query name passed to it. This will run
>>your action query then pass control back to your module
>to
>>drop thru the rest of your code.

>>function Whatever()
>>~~~~~~~~

>>'This line should be the Function with Query Name to run
>>your action query. Control goes to it, then comes back
to
>>the module it is called in, thereby, giving you a pause
>>effect.
>>call RunActionQueries("QueryName1")
>>call RunActionQueries("QueryName2")

>>'Resume with code

>>End Function

>>>-----Original Message-----
>>>Message unavailable
>>.

>.



Sun, 13 Feb 2005 04:55:43 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. How to pause until action completed?

2. pause code until event

3. Pausing code until a form is closed?

4. ADO-Suspend code until update is completed??

5. Stopping code until a form is complete (beginner stuff here)

6. Pause until form is closed

7. Pausing loop until printing finished

8. pause until cmdButton click

9. Help Pausing VB4 execution until DOS app finishes (Not Windows App)

10. Pause program execution until other application finishes

11. Pausing until a key is pressed

12. Pausing loop until printing finished

 

 
Powered by phpBB® Forum Software