Count no of rows appended

[Esmerelda]

Hi,



I’m using vba to run an append query, but where 0 rows are appended, I need
to pop up a message box to inform the user. Is there a way of counting the
number of records so I can write an if statement:

If ‘no of records’=0 then msg etc

Else

Do cmd runSQL



Or am I approaching this the wrong way?



Also another quickie, I keep seeing this in code in this forum: dbFailOnError.

I know it kicks in when there’s an error, but what exactly does it do? Does
it just stop the macro immediately when the error occurs?



Thanks

 

[Douglas J. Steele]

Assuming you're talking about a saved query:

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("NameOfQuery")
qdf.Execute
If qdf.RecordsAffected = 0 Then
MsgBox "No records were updated."
End If
Set qdf = Nothing

If you've just got a SQL statement:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "...."
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Execute
If qdf.RecordsAffected = 0 Then
MsgBox "No records were updated."
End If
Set qdf = Nothing
Set db = Nothing

 

[Jack Leach]

Use CurrentDb.Execute and check the RecordsAffected after...


CurrentDb.Execute "your sql", dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "No Recs were appended!"
End If


dsFailOnError will raise a trappable error (it will send your code to your
error handler assuming you have an error handler set up).

DoCmd.RunSQL has neither an option for FailOnError or a way to check the
number of records affected.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

Use CurrentDb.Execute and check the RecordsAffected after...


CurrentDb.Execute "your sql", dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "No Recs were appended!"
End If

Tis is a good suggestion in principle, but you can't use two separate
invocations of CurrentDb() -- you must use the same database object as the
base for .RecordsAffected, and CurrentDb() creates a new object each time.
This would work:

With CurrentDb
.Execute "your sql", dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "No Recs were appended!"
End If
End With

 

[Jack Leach]

whoops...


thanks Dirk

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)