Return message based on query result

[Guest]

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".
En
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 appreciate

Bruc

 

[Ken Snell]

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>

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