Use queries within IF...THEN

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

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
dlesandrini@hotmail.com
http://amazecreations.com


"Koen" <no@spam.nl> wrote in message
news:Xns9448639AF3B5ERubends@194.109.133.20...[color=blue]
> Hi all,
> I have created a nice working database for keeping track of helpdesk[/color]
calls,[color=blue]
> 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[/color]

-----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:
[color=blue]
> 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[/color]

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.)