Message box appear for no records returned in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is the code where I have an append query whereas if there are no
records to append, a message box will appear. I can't seem to get it to
recognize the fact that there are no records. I have tried "count", "If
Count = 0", etc. Please help.


Public Function Error_Cd()
On Error GoTo Err_Execute

Dim db As Database
Set db = CurrentDb

CurrentDb.QueryDefs("Check for Changed Dates").Execute dbFailOnError

Set db = Nothing

Err_Execute:
If Count = 0 Then
MsgBox "There are no changed dates"

End If

End Function
 
First, no records being updated will not necessarily be caused by an error.

Try:

Public Function Error_Cd()
On Error GoTo Err_Execute

Dim db As Database

Set db = CurrentDb

db.QueryDefs("Check for Changed Dates").Execute dbFailOnError
If db.RecordsAffected = 0 Then
MsgBox "There are no changed dates"
End If

Exit_Execute:
Set db = Nothing
Exit Sub

Err_Execute:
Msgbox "Error occured: Number " & Err.Number & ", Description: " &
Err.Description
Resume Exit_Execute

End Function
 
I need to explain. I am running a query that checks for changed dates. If
it finds any, it will append those records to a table called an error table
so that they can be researched. If there are no error, then I want the
message box to appear and state the obvious.
 
What exactly is "Check for Changed Dates" then? If it's only supposed to
return a count of records, you can't use Execute to run it: Execute only
works with Action queries (INSERT INTO, UPDATE, DELETE FROM)
 
The check for changed dates is a query that checks 2 tables from different
time periods to see if the dates have changed. If the dates have changed,
then those records are appended to an existing table, which we are going to
call the error table. We are going to have additional checks like, all new
records appended to this file also. This table is going to be for the user
to research these records and run the query until there are no records.

I thought it would be easier or less steps if I made the query an append
query instead of running one query to check and if there are any records, put
an "Append" command in the code but maybe that would be the solution. Ideas??
 
I'm sorry, it's really not clear to me what you've got and what you want.

The sample code I gave assumed that "Check for Changed Dates" was an append
query. If it is, in fact, an append query, did you try the code I suggested?
 
Yes I did and it still displayed the message box whether there were records
or not. I am going to work on it additionally tonight and hopefully it will
come to me.

Thank you for your help.
 
Doug,
Just to let you know, I finally made my original code work. Thank you for
your help though.

Here is the code as it is today.

Public Function Error_Cd()
On Error GoTo Err_Execute

Dim db As Database

Set db = CurrentDb

CurrentDb.QueryDefs("Check for Changed Dates").Execute dbFailOnError

Set db = Nothing

Err_Execute:
If DCount("[Account Number]", "Account_Geography") > 0 Then
MsgBox "There are changed dates"
End If

End Function
 
Back
Top