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

[Koen]

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

[Salad]

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

[Koen]

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

[Bob Quintal]

Koen <[e-mail]> 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

[DFS]

Issue a Me.Refresh command before calling the function.

"Koen" <[e-mail]> 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

[Salad]

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.