Use queries within IF...THEN

[Koen]

Hi all,
I have created a nice working database for keeping track of helpdesk calls,
corrective actions etc.

To determine a certain status (for example what calls are ready for test,
after all actions have been finished) I created queries. Because these
statusses are depending on more than one parameter, I would like to
automate the status setting.

So my idea is to put IF...THEN logic underneath AfterUpdate events of all
fields that can influence a certain status.

For example, one call could have many actions. An action could be critical
(Y/N) for retest (meaning it should always be finished before the call is
retested) and an action could be Finished (Y/N). When I change these two
boolean fields, I would like to trigger some IF...THEN logic that uses my
query. Something like

Private Sub Finished_AfterUpdate()
If Me.Call_ID NOT EXIST IN qryReadyForRetest Then

etc.

End Sub

This is not working.
How do I do this?
In the end I would like a status field in tblCall be updated after a field
in tblAction is updated and it meets my criteria.

Hope someone understands and can help.

Thanks,

Koen

[Danny J. Lesandrini]

How about this?

Private Sub Finished_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

Set dbs = Currentdb()
sSQL = "SELECT Count(*) As MyCount FROM qryReadyForRetest " & _
" WHERE Call_ID = " & Me.Call_ID
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If rst!MyCount = 0 Then
' do stuff here
End If
Set rst = Nothing
Set dbs = Nothing
End Sub

--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com
"Koen" <[e-mail]> wrote in message
news:Xn*********************@194.109.133.20...

Hi all,
I have created a nice working database for keeping track of helpdesk calls, corrective actions etc.

To determine a certain status (for example what calls are ready for test,
after all actions have been finished) I created queries. Because these
statusses are depending on more than one parameter, I would like to
automate the status setting.

So my idea is to put IF...THEN logic underneath AfterUpdate events of all
fields that can influence a certain status.

For example, one call could have many actions. An action could be critical
(Y/N) for retest (meaning it should always be finished before the call is
retested) and an action could be Finished (Y/N). When I change these two
boolean fields, I would like to trigger some IF...THEN logic that uses my
query. Something like

Private Sub Finished_AfterUpdate()
If Me.Call_ID NOT EXIST IN qryReadyForRetest Then

etc.

End Sub

This is not working.
How do I do this?
In the end I would like a status field in tblCall be updated after a field
in tblAction is updated and it meets my criteria.

Hope someone understands and can help.

Thanks,

Koen

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to return the results of a query in a Recordset. Your
example indicates that the query "qryReadyForRetest" should be the
data source in another query, and assumes that qryReadyForRetest
returns a single column "Call_ID."

' Sets up Call_ID as a numeric data type
' If it is an alphanumeric data type use
' Call_ID = '" & Me.Call_ID & "'"

dim strSQL As String
strSQL = "SELECT Call_ID FROM qryReadyForRetest " & _
"WHERE Call_ID=" & Me.Call_ID

dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset(strsql)

If not rs.eof then ' the Call_ID exists
... etc. ...
HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9Dju4echKqOuFEgEQKjVgCfckyrrBGG/InOM6hwiDdxGZLsnS4AoKC9
2tNyXoyGI6IbFnZCA2IgZXij
=9+lW
-----END PGP SIGNATURE-----

Koen wrote:

Hi all,
I have created a nice working database for keeping track of helpdesk calls,
corrective actions etc.

To determine a certain status (for example what calls are ready for test,
after all actions have been finished) I created queries. Because these
statusses are depending on more than one parameter, I would like to
automate the status setting.

So my idea is to put IF...THEN logic underneath AfterUpdate events of all
fields that can influence a certain status.

For example, one call could have many actions. An action could be critical
(Y/N) for retest (meaning it should always be finished before the call is
retested) and an action could be Finished (Y/N). When I change these two
boolean fields, I would like to trigger some IF...THEN logic that uses my
query. Something like

Private Sub Finished_AfterUpdate()
If Me.Call_ID NOT EXIST IN qryReadyForRetest Then

etc.

End Sub

This is not working.
How do I do this?
In the end I would like a status field in tblCall be updated after a field
in tblAction is updated and it meets my criteria.

Hope someone understands and can help.

Thanks,

Koen

[Pieter Linden]

Koen,
you need to open a recordset based on your query, and then you can do
something with it. You'd have to do something like pass the CallID
into the query as a parameter and then open a recordset based on that.
Then you could do a recordcount or whatever and then respond to
that...

If rst.RecordCount=0 Then
'Do one thing
Else
'Do another
End If

Also, you cannot use EXISTS outside of a subquery. (Well, as far as I
know anyway... I could not get it to work the one time I tried it.)