By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

Please help: Query uses _old_ value, but I am using _AfterUpdate_

P: n/a
Hi all,

I created a function that updates a certain status field. The status can be
influenced by four different other fields. So, on the form where I edit
these fields I call the function (see below) from the AfterUpdate events of
each of the four fields.

The problem is, that the query in the function uses the _old_ value of the
updated field! I want it to use the _updated_ value and thought the
_AfterUpdate_ event would do the trick.

Who can tell me what I am doing wrong?
The function I call from the AfterUpdate event is:

Function ReadyForTest(obsid As Integer) As Boolean
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

Set dbs = CurrentDb()
sSQL = "SELECT Count(*) As MyCount FROM qryReadyForTest " & _
" WHERE Observation_ID = " & obsid
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If rst!MyCount = 0 Then
MsgBox "Do Action"
Else
MsgBox "Ready for test!"
End If
Set rst = Nothing
Set dbs = Nothing

' Succeeded
ReadyForTest = True
End Function

(As you can see there is no status updated yet, but this is only used as an
example)

Kind regards,

Koen
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Koen wrote:
Hi all,

I created a function that updates a certain status field. The status can be
influenced by four different other fields. So, on the form where I edit
these fields I call the function (see below) from the AfterUpdate events of
each of the four fields.

The problem is, that the query in the function uses the _old_ value of the
updated field! I want it to use the _updated_ value and thought the
_AfterUpdate_ event would do the trick.
How are you passing the value to the function? It appears you are sending an
integer value to it. Are you sure you know what the value is?
Who can tell me what I am doing wrong?
Impossible if we don't know how you are calling the function. Who knows what
qryReadyForTest is.

Who knows. Maybe you need to save the record first before running the
function.
Docmd.RunCommand accmdSaveREcord

The function I call from the AfterUpdate event is:

Function ReadyForTest(obsid As Integer) As Boolean
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

Set dbs = CurrentDb()
sSQL = "SELECT Count(*) As MyCount FROM qryReadyForTest " & _
" WHERE Observation_ID = " & obsid
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If rst!MyCount = 0 Then
MsgBox "Do Action"
Else
MsgBox "Ready for test!"
End If
Set rst = Nothing
Set dbs = Nothing

' Succeeded
ReadyForTest = True
End Function

(As you can see there is no status updated yet, but this is only used as an
example)

Kind regards,

Koen


Nov 12 '05 #2

P: n/a
Salad <oi*@vinegar.com> wrote in news:3F**************@vinegar.com:

The problem is, that the query in the function uses the _old_ value
of the updated field! I want it to use the _updated_ value and
thought the _AfterUpdate_ event would do the trick.


How are you passing the value to the function? It appears you are
sending an integer value to it. Are you sure you know what the value
is?


This is how I pass the value to the function:
Call ReadyForTest(Me.txtObservation_ID)

Who can tell me what I am doing wrong?


Impossible if we don't know how you are calling the function. Who
knows what qryReadyForTest is.

Who knows. Maybe you need to save the record first before running the
function.
Docmd.RunCommand accmdSaveREcord


Yes! This works! Thanks!
Is it this simple? it looks an odd solution...
But thanks!

Koen

Nov 12 '05 #3

P: n/a
Koen <no@spam.nl> wrote in
news:Xn********************@194.109.133.20:
Salad <oi*@vinegar.com> wrote in
news:3F**************@vinegar.com:

The problem is, that the query in the function uses the
_old_ value of the updated field! I want it to use the
_updated_ value and thought the _AfterUpdate_ event would do
the trick.


How are you passing the value to the function? It appears
you are sending an integer value to it. Are you sure you
know what the value is?


This is how I pass the value to the function:
Call ReadyForTest(Me.txtObservation_ID)

Who can tell me what I am doing wrong?


Impossible if we don't know how you are calling the function.
Who knows what qryReadyForTest is.

Who knows. Maybe you need to save the record first before
running the function.
Docmd.RunCommand accmdSaveREcord


Yes! This works! Thanks!
Is it this simple? it looks an odd solution...
But thanks!

Koen

Yes it is that simple, the reason is that your function reads the
values stored in the table. Until the values in the form are
saved to the table, either by closing the form, going to another
record, or by forcing a save via command. the table still has the
old values in it.

Bob Q
Nov 12 '05 #4

P: n/a
DFS
Issue a Me.Refresh command before calling the function.

"Koen" <no@spam.nl> wrote in message
news:Xn*********************@194.109.133.20...
Hi all,

I created a function that updates a certain status field. The status can be influenced by four different other fields. So, on the form where I edit
these fields I call the function (see below) from the AfterUpdate events of each of the four fields.

The problem is, that the query in the function uses the _old_ value of the
updated field! I want it to use the _updated_ value and thought the
_AfterUpdate_ event would do the trick.

Who can tell me what I am doing wrong?
The function I call from the AfterUpdate event is:

Function ReadyForTest(obsid As Integer) As Boolean
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

Set dbs = CurrentDb()
sSQL = "SELECT Count(*) As MyCount FROM qryReadyForTest " & _
" WHERE Observation_ID = " & obsid
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If rst!MyCount = 0 Then
MsgBox "Do Action"
Else
MsgBox "Ready for test!"
End If
Set rst = Nothing
Set dbs = Nothing

' Succeeded
ReadyForTest = True
End Function

(As you can see there is no status updated yet, but this is only used as an example)

Kind regards,

Koen

Nov 12 '05 #5

P: n/a
Koen wrote:
This is how I pass the value to the function:
Call ReadyForTest(Me.txtObservation_ID)


That looks OK. CALL is usually for subroutines. Subroutines don't pass
back values. Functions do. Ex:
Dim blnOK As Boolean
blnOK = ReadyForTest(variable)
If blnOK Then...
or
If ReadyForTest(variable) Then...

Subs usually perform an action then return control back to the routine that
called them and continues. No value is returned. Since your Sub, if it
were a function, would always return True, it does not need to be a
function.
Who knows. Maybe you need to save the record first before running the
function.
Docmd.RunCommand accmdSaveREcord


Yes! This works! Thanks!


That's good.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.