Re: Return message based on query result

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ken Snell (kthsneisllis9_at_ncoomcastt.renael)
Date: 05/20/04 

Date: Thu, 20 May 2004 00:20:08 -0400

First question: you can use the DCount function to identify if a query
contains any records (which is what I assume you mean by saying the query is
Null). Macro code would use a condition statement, something like this for
your macro setup:

MacroName
    Condition: DCount("*", "qry_integrity") = 0
        Action: MsgBox
            Message: There are integrity issues.

    Condition: . . .
        Action: StopMacro

    Condition: (blank)
        Action: MsgBox
            Message: OK

Second question: you cannot trap for an error using macros. To do what you
seek in this question, you would need to use VBA code to run the append
query and then to test for the presence of an error and act accordingly
depending upon the result. For example, suppose that you want to run append
query "qry_AppendQuery" when you click a button. You could put code similar
to this on the button's OnClick event:

Private Sub cmdButtonName_Click()
On Error GoTo ErrorLabel
    DoCmd.SetWarnings False
    CurrentDb.Execute "qry_AppendQuery", dbFailOnError
    MsgBox "Record added successfully."
ExitLabel:
    DoCmd.SetWarnings True
    Exit Sub

ErrorLabel:
    MsgBox "The record could not be added."
    Resume ExitLabel

End Sub 

-- 
        Ken Snell
<MS ACCESS MVP>
"Bruce" <anonymous@discussions.microsoft.com> wrote in message
news:B2041833-68B8-46F1-903C-77D1203665BF@microsoft.com...
> How do I you code to give some meaningful error message based on a queries
result. I have 2 situations to that I would like help on.
>
> I have a series of select queries that essentially are checking data
intergrity in my database. (n.b. Not referential Integrity).
> Essentially, if the query result is NULL then integrity is intact. However
if not then the records with discrepancies are returned.
>
> rather that open all these queries and view myself, is there a way to do
this from code that basically tells me which queries are in error.
>
> e.g.
> if qry_integrity is not null then
> msgbox("There are integrity issues".)
> End
> Else
> msgbox("Ok")
>
>
>
> The second I would like to do I add some code to a macro that runs an into
into (Append) query). I have turned off set warnings.
> Basically is the append is successful it should tell the use "Record added
successfully". If not "The record could not be added".
>
> Any help appreciated
>
> Bruce
>
>
>
Quantcast