Pausing code? (repost of previous rambling message)

  • Thread starter Thread starter Meg
  • Start date Start date
M

Meg

Is it possible to make your code pause to wait until the
preceding step has been fully completed before moving to
the next line?

I am running the following code:

Private Sub Expenditure_AfterUpdate()

Dim CalculatedActivityCost As Currency

Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost

Forms![Project Details]![Project Deliverables
Subform].Form![Cost] = Me.TaskTotalExpenditure

End Sub

Problem is that the msgbox (and my subform field) displays
a null becuase the database is still undergoing the
requery.

This problem only occurs on my laptop - not my desktop
(which itself is strange since my laptop has a faster chip
and more ram?!)

Alternatively I could retrieve the value I need via SQL,
but I dont know how to get the code to do this!

Any suggestions greatly appreciated!!
Thanks
 
Thanks Ken....but unfortunately no joy.
-----Original Message-----
No guarantee of success, but try putting
DoEvents

after the Me.Requery line.

--

Ken Snell
<MS ACCESS MVP>

Is it possible to make your code pause to wait until the
preceding step has been fully completed before moving to
the next line?

I am running the following code:

Private Sub Expenditure_AfterUpdate()

Dim CalculatedActivityCost As Currency

Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost

Forms![Project Details]![Project Deliverables
Subform].Form![Cost] = Me.TaskTotalExpenditure

End Sub

Problem is that the msgbox (and my subform field) displays
a null becuase the database is still undergoing the
requery.

This problem only occurs on my laptop - not my desktop
(which itself is strange since my laptop has a faster chip
and more ram?!)

Alternatively I could retrieve the value I need via SQL,
but I dont know how to get the code to do this!

Any suggestions greatly appreciated!!
Thanks
Click to expand...


.
Click to expand...
 
not sure, but you can try to make a loop with doevents inside until not
isnull(Me.TaskTotalExpenditure)
but be careful, mke sure loop not be endless

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Meg said:
Thanks Ken....but unfortunately no joy.
-----Original Message-----
No guarantee of success, but try putting
DoEvents

after the Me.Requery line.

--

Ken Snell
<MS ACCESS MVP>

Is it possible to make your code pause to wait until the
preceding step has been fully completed before moving to
the next line?

I am running the following code:

Private Sub Expenditure_AfterUpdate()

Dim CalculatedActivityCost As Currency

Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost

Forms![Project Details]![Project Deliverables
Subform].Form![Cost] = Me.TaskTotalExpenditure

End Sub

Problem is that the msgbox (and my subform field) displays
a null becuase the database is still undergoing the
requery.

This problem only occurs on my laptop - not my desktop
(which itself is strange since my laptop has a faster chip
and more ram?!)

Alternatively I could retrieve the value I need via SQL,
but I dont know how to get the code to do this!

Any suggestions greatly appreciated!!
Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost
Click to expand...

Let me see if I have this right.

TaskTotalExpenditure is the name of a control on the form (Me) and it is
presumably bound to a field of the same name in the underlying query.

Expenditure is simlarly a control and a field too. When you change the
value of Expenditure, the entire recordset is re-evaluated and this
produces a new value in TaskTotalExpenditure. This can only happen if
TaskTotalExpenditure is a calculated value in the query -- presumably you
know what the underlying calculation is, so would it not be easier just to
redo that (not to mention kinder on the network).

Forms![Project Details]! _
[Project Deliverables Subform].Form! _
[Cost] = Me.TaskTotalExpenditure
Click to expand...

Now, this bit suggests that you have another field in another table that is
storing the result of the aforementioned calculation.

The reason that you are finding this hard to do is that it is (nearly
always) a Bad Thing. Trying to find foolproof methods of keeping two (or
more) instances of the same data item synchronised is Doomed To Failure.
The whole point of R theory, furthermore, is to demonstrate that it is
unneccessary, providing you have correctly designed tables in the first
place.

Hope that helps


Tim F
 
Thanks Tim,
This helps me to understand my issues better - but not to
solve them!

My TaskTotalExpenditure field is a calculated control in
the footer of a continuous subform, based on a table
of "Activities." (=sum([expenditure])

The reason I wish to copy the result to a different table
is basically a check on the user's data entry. Each
Activity may have many "Tasks" associated with it, each of
which also has a [Cost]. I want to check that the cost of
all of the sub-tasks adds up to the proposed Activity
total. If the numbers are different, I want a warning to
occur (or probably conditional formatting to make it bold
and red).
The issue probably also has something to do with the
structure of my form (which I dont want to change). Main
form is single form for "Projects". A continuous subform
lists the "Activities" associated with each project.
Another continuous subform is linked via a field on the
main form to disply the "Tasks" associated with the chosen
Activity.

I realise that there is probably a much better way of
doing this, maybe involving recordsets? But as you have
probably guessed I am an amateur!

Thanks again for your help!
cheers,
Meg
1
-----Original Message-----
Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost
Click to expand...

Let me see if I have this right.

TaskTotalExpenditure is the name of a control on the form (Me) and it is
presumably bound to a field of the same name in the underlying query.

Expenditure is simlarly a control and a field too. When you change the
value of Expenditure, the entire recordset is re- evaluated and this
produces a new value in TaskTotalExpenditure. This can only happen if
TaskTotalExpenditure is a calculated value in the query -- presumably you
know what the underlying calculation is, so would it not be easier just to
redo that (not to mention kinder on the network).

Forms![Project Details]! _
[Project Deliverables Subform].Form! _
[Cost] = Me.TaskTotalExpenditure
Click to expand...

Now, this bit suggests that you have another field in another table that is
storing the result of the aforementioned calculation.

The reason that you are finding this hard to do is that it is (nearly
always) a Bad Thing. Trying to find foolproof methods of keeping two (or
more) instances of the same data item synchronised is Doomed To Failure.
The whole point of R theory, furthermore, is to demonstrate that it is
unneccessary, providing you have correctly designed tables in the first
place.

Hope that helps


Tim F


.
Click to expand...
 
Thanks Tim,
This helps me to understand my issues better - but not to
solve them!

My TaskTotalExpenditure field is a calculated control in
the footer of a continuous subform, based on a table
of "Activities." (=sum([expenditure])

The reason I wish to copy the result to a different table
is basically a check on the user's data entry. Each
Activity may have many "Tasks" associated with it, each of
which also has a [Cost]. I want to check that the cost of
all of the sub-tasks adds up to the proposed Activity
total. If the numbers are different, I want a warning to
occur (or probably conditional formatting to make it bold
and red).
The issue probably also has something to do with the
structure of my form (which I dont want to change). Main
form is single form for "Projects". A continuous subform
lists the "Activities" associated with each project.
Another continuous subform is linked via a field on the
main form to disply the "Tasks" associated with the chosen
Activity.

I realise that there is probably a much better way of
doing this, maybe involving recordsets? But as you have
probably guessed I am an amateur!

Thanks again for your help!
cheers,
Meg
1
-----Original Message-----
Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost
Click to expand...

Let me see if I have this right.

TaskTotalExpenditure is the name of a control on the form (Me) and it is
presumably bound to a field of the same name in the underlying query.

Expenditure is simlarly a control and a field too. When you change the
value of Expenditure, the entire recordset is re- evaluated and this
produces a new value in TaskTotalExpenditure. This can only happen if
TaskTotalExpenditure is a calculated value in the query -- presumably you
know what the underlying calculation is, so would it not be easier just to
redo that (not to mention kinder on the network).

Forms![Project Details]! _
[Project Deliverables Subform].Form! _
[Cost] = Me.TaskTotalExpenditure
Click to expand...

Now, this bit suggests that you have another field in another table that is
storing the result of the aforementioned calculation.

The reason that you are finding this hard to do is that it is (nearly
always) a Bad Thing. Trying to find foolproof methods of keeping two (or
more) instances of the same data item synchronised is Doomed To Failure.
The whole point of R theory, furthermore, is to demonstrate that it is
unneccessary, providing you have correctly designed tables in the first
place.

Hope that helps


Tim F


.
Click to expand...
 
Thanks Tim,
This helps me to understand my issues better - but not to
solve them!

My TaskTotalExpenditure field is a calculated control in
the footer of a continuous subform, based on a table
of "Activities." (=sum([expenditure])

The reason I wish to copy the result to a different table
is basically a check on the user's data entry. Each
Activity may have many "Tasks" associated with it, each of
which also has a [Cost]. I want to check that the cost of
all of the sub-tasks adds up to the proposed Activity
total. If the numbers are different, I want a warning to
occur (or probably conditional formatting to make it bold
and red).
The issue probably also has something to do with the
structure of my form (which I dont want to change). Main
form is single form for "Projects". A continuous subform
lists the "Activities" associated with each project.
Another continuous subform is linked via a field on the
main form to disply the "Tasks" associated with the chosen
Activity.

I realise that there is probably a much better way of
doing this, maybe involving recordsets? But as you have
probably guessed I am an amateur!

Thanks again for your help!
cheers,
Meg

-----Original Message-----
Me.Requery

CalculatedActivityCost = Me.TaskTotalExpenditure
MsgBox CalculatedActivityCost
Click to expand...

Let me see if I have this right.

TaskTotalExpenditure is the name of a control on the form (Me) and it is
presumably bound to a field of the same name in the underlying query.

Expenditure is simlarly a control and a field too. When you change the
value of Expenditure, the entire recordset is re- evaluated and this
produces a new value in TaskTotalExpenditure. This can only happen if
TaskTotalExpenditure is a calculated value in the query -- presumably you
know what the underlying calculation is, so would it not be easier just to
redo that (not to mention kinder on the network).

Forms![Project Details]! _
[Project Deliverables Subform].Form! _
[Cost] = Me.TaskTotalExpenditure
Click to expand...

Now, this bit suggests that you have another field in another table that is
storing the result of the aforementioned calculation.

The reason that you are finding this hard to do is that it is (nearly
always) a Bad Thing. Trying to find foolproof methods of keeping two (or
more) instances of the same data item synchronised is Doomed To Failure.
The whole point of R theory, furthermore, is to demonstrate that it is
unneccessary, providing you have correctly designed tables in the first
place.

Hope that helps


Tim F


.
Click to expand...
 
This helps me to understand my issues better - but not to
solve them!
Click to expand...

Happens a lot on the NGs!! We should be able to get you a bit further on
though.
My TaskTotalExpenditure field is a calculated control in
the footer of a continuous subform, based on a table
of "Activities." (=sum([expenditure])
Click to expand...
I want to check that the cost of
all of the sub-tasks adds up to the proposed Activity
total. If the numbers are different, I want a warning to
Click to expand...

I probably don't understand enough for a complete solution, but I think the
answer goes something along the lines of


' Get the current total for the current Activity
dblTotal = DSUM("Expenditure", "Tasks", _
"ActivityNumber = " & Me.ActivityNumber)

' and put it into the textbox
Me.txtTaskTotalExpenditure = dblTotal

' If it's not on the form, look up the expected
' expendituure
dblExpected = DLookUp("ProposedCost", "Activities", _
"ActivityNumber = " & Me.ActivityNumber)

' Now compare them and warn the user if necc
If dblTotal > dblExpected Then
msgbox "This is an overspend!!"
me.txtTaskTotalExpenditure.BackColor = c_Red

ElseIf dblTotal = dblExpected Then
msgbox "Exactly on budget"

Else ' dblTotal < dblExpected Then
msgbox "Underspend: you have " & dblExpected - dblTotal & " left"

End If

This way there is no duplicated storage to look after, and the DSUM will
always be correct for the current activities regardless of who has updated
them and how.
I realise that there is probably a much better way of
doing this, maybe involving recordsets? But as you have
probably guessed I am an amateur!
Click to expand...

No harm in being an amateur! It sounds like you have put in a lot of work
already.

Hope it helps


Tim F
 
Thanks very much Tim,

With some small modifications to your code I have it
working perfectly....and have also got a check on the
start and end dates of each task being within the set
timeframe for the overall activity.

Thanks again...your help is greatly appreciated!
Hope you have a great day!

Meg

-----Original Message-----
This helps me to understand my issues better - but not to
solve them!
Click to expand...

Happens a lot on the NGs!! We should be able to get you a bit further on
though.
My TaskTotalExpenditure field is a calculated control in
the footer of a continuous subform, based on a table
of "Activities." (=sum([expenditure])
Click to expand...
I want to check that the cost of
all of the sub-tasks adds up to the proposed Activity
total. If the numbers are different, I want a warning
Click to expand...
to

I probably don't understand enough for a complete solution, but I think the
answer goes something along the lines of


' Get the current total for the current Activity
dblTotal = DSUM("Expenditure", "Tasks", _
"ActivityNumber = " & Me.ActivityNumber)

' and put it into the textbox
Me.txtTaskTotalExpenditure = dblTotal

' If it's not on the form, look up the expected
' expendituure
dblExpected = DLookUp("ProposedCost", "Activities", _
"ActivityNumber = " & Me.ActivityNumber)

' Now compare them and warn the user if necc
If dblTotal > dblExpected Then
msgbox "This is an overspend!!"
me.txtTaskTotalExpenditure.BackColor = c_Red

ElseIf dblTotal = dblExpected Then
msgbox "Exactly on budget"

Else ' dblTotal < dblExpected Then
msgbox "Underspend: you have " & dblExpected - dblTotal & " left"

End If

This way there is no duplicated storage to look after, and the DSUM will
always be correct for the current activities regardless of who has updated
them and how.
I realise that there is probably a much better way of
doing this, maybe involving recordsets? But as you have
probably guessed I am an amateur!
Click to expand...

No harm in being an amateur! It sounds like you have put in a lot of work
already.

Hope it helps


Tim F

.
Click to expand...
 
Back
Top