Error trapping for "MS Access can't append all the records in the append query"

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Hi
I'm sure this has been asked before, but I can't find any references. How
do I trap the error
"MS Access can't append all the records in the append query" if there are no
records to append. The
built in error msg is a bit "scary" to users and I would like to customize
the msg.

Thanks
 
Dale said:
Hi
I'm sure this has been asked before, but I can't find any references. How
do I trap the error
"MS Access can't append all the records in the append query" if there are no
records to append. The
built in error msg is a bit "scary" to users and I would like to customize
the msg.

Thanks


Use the Execute method instead of RunQuery...
Click to expand...
 
Dale said:
Hi
I'm sure this has been asked before, but I can't find any references. How
do I trap the error
"MS Access can't append all the records in the append query" if there are no
records to append. The
built in error msg is a bit "scary" to users and I would like to customize
the msg.
Click to expand...

Either docmd.setwarnings false or adjusting the setting "action queries"
in Tools->Options, tab Edit
 
Thank you all
I would turn warnings off for action queries temporarily if I could figure
out how to trigger my own error message when there is no data to append.
With docmd.setwarnings False, I could not generate my own error message. I
will try the Execute method, although one of the queries has 2
parameters...more research! how to include the 2 parameters and execute
statement.

All your help is appreciated..Happy New Year
 
Dale said:
Thank you all
I would turn warnings off for action queries temporarily if I could figure
out how to trigger my own error message when there is no data to append.
With docmd.setwarnings False, I could not generate my own error message. I
will try the Execute method, although one of the queries has 2
parameters...more research! how to include the 2 parameters and execute
statement.
Click to expand...

No records to append... hmm... you can use the RecordsAffected property
of a QueryDef object. That means that you have to set it first, like

dim qd as DAO.querydef
set qd=currentdb.querydefs("yourQuery")
qd.execute
if qd.recordsaffected=0 then msgbox "Nothing updated!"
 
Thank you all
I would turn warnings off for action queries temporarily if I could figure
out how to trigger my own error message when there is no data to append.
With docmd.setwarnings False, I could not generate my own error message. I
will try the Execute method, although one of the queries has 2
parameters...more research! how to include the 2 parameters and execute
statement.
Click to expand...

You can set the Parameters of a querydef object:

Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("your-action-query")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name) ' e.g. if the name of the parameter is
' [Forms]![yourform]![somecontrol]
' use Eval to find what's in that control
Next prm
qd.Execute dbFailOnError
....
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case xxxx ' the "could not be added" error number
Resume Next ' just go on without complaining
Case Else
MsgBox "Error " & Err.Number & " in <whatever>:" _
& vbCrLf & Err.Description
End Select
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
 
Thank you Thank you....Have I told you how great you guys are...awesome
doesn't even come close!

All the best in the new year and may your year be bountiful with trouble
free programming!

Seems the execute method is not very well documented at least in A97?



John Vinson said:
Thank you all
I would turn warnings off for action queries temporarily if I could figure
out how to trigger my own error message when there is no data to append.
With docmd.setwarnings False, I could not generate my own error message.
I
will try the Execute method, although one of the queries has 2
parameters...more research! how to include the 2 parameters and execute
statement.
Click to expand...

You can set the Parameters of a querydef object:

Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("your-action-query")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name) ' e.g. if the name of the parameter is
' [Forms]![yourform]![somecontrol]
' use Eval to find what's in that control
Next prm
qd.Execute dbFailOnError
...
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case xxxx ' the "could not be added" error number
Resume Next ' just go on without complaining
Case Else
MsgBox "Error " & Err.Number & " in <whatever>:" _
& vbCrLf & Err.Description
End Select
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
Click to expand...
 
Back
Top