Error Handling not working properly

[mls via AccessMonster.com]

I have the following code to load into db when the button is clicked after
loading it show the successful completion message box. I have composite key
so if some one tries to upload again ot should fail with error message.
In my case it show the Err desciption but again it shows the successful
msgbox again. Can you dedug please?

Thanks

Private Sub cmd_load2010_new_Click()


Dim stDocName As String
On Error GoTo violation_err

stDocName = "Insert_RESULTS_into_2010_RRT"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox "Upload completed Successfully"


Exit_ModuleName:
Exit Sub

violation_err:
MsgBox Err.Description
Resume Exit_ModuleName
End Sub

 

[Douglas J. Steele]

Instead of using the OpenQuery method, use the Execute method of the query
so that a trappable error is created.

Private Sub cmd_load2010_new_Click()
On Error GoTo violation_err

Dim qdfInsert As DAO.QueryDef

Set qdfInsert = CurrentDb.QueryDefs("Insert_RESULTS_into_2010_RRT")
qdfInsert.Execute dbFailOnError
MsgBox "Upload completed Successfully"

Exit_ModuleName:
Exit Sub

violation_err:
MsgBox Err.Description
Resume Exit_ModuleName

End Sub

I'm pretty sure that the error you're seeing isn't coming from your error
trapping, which is why it's not working the way you want.

 

[mls via AccessMonster.com]

I got compile error: user-defined type not defined at Dim qdfInsert As DAO.
QueryDef.
I am using ACCESS 2000 so I changed the DAO to ADO and still give the same
error.
any idea?

Instead of using the OpenQuery method, use the Execute method of the query
so that a trappable error is created.

Private Sub cmd_load2010_new_Click()
On Error GoTo violation_err

Dim qdfInsert As DAO.QueryDef

Set qdfInsert = CurrentDb.QueryDefs("Insert_RESULTS_into_2010_RRT")
qdfInsert.Execute dbFailOnError
MsgBox "Upload completed Successfully"

Exit_ModuleName:
Exit Sub

violation_err:
MsgBox Err.Description
Resume Exit_ModuleName

End Sub

I'm pretty sure that the error you're seeing isn't coming from your error
trapping, which is why it's not working the way you want.

I have the following code to load into db when the button is clicked after
loading it show the successful completion message box. I have composite

[quoted text clipped - 21 lines]

Resume Exit_ModuleName
End Sub

 

[Dirk Goldgar]

I got compile error: user-defined type not defined at Dim qdfInsert As DAO.
QueryDef.
I am using ACCESS 2000 so I changed the DAO to ADO and still give the same
error.
any idea?

It would have been ADODB, not just ADO, but anyway ADO has no QueryDef
object. Use DAO, as Doug oosted, but add a references via the Tools ->
References... dialog to the Microsoft DAO 3.6 Object Library.

 

[mls via AccessMonster.com]

I added the reference but see this error message

Actually mine is a warning messgae, Are warning messages handled in MS ACCESS?

 

[Dirk Goldgar]

I added the reference but see this error message

What message? The same as you stated in your original post?

Actually mine is a warning messgae, Are warning messages handled in MS
ACCESS?

I don't understand your question. The message you mentioned originally was
definitely a compile-time error message, not a warning message. Please post
your exact code and the exact message(s) you're getting.

 

[mls via AccessMonster.com]

Here is my code .. I am trying to insert data into database from staging
table to main table. So if the load is successful then it should display
"Successful message" . If it fails ( mainly because I have primary key in my
main table so if inserting duplicates system shows Alert saying Key violation
for the lab tech when they hit the load data command.

Private Sub cmd_load2010_rrtnew_Click()

Dim stDocName As String

stDocName = "Insert_Results_into_2010_RRTnew"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox "Upload completed Successfully"
Exit_cmd_load2010_rrtnew_Click:
Exit Sub

Err_cmd_load2010_rrtnew_Click:
MsgBox Err. Description
'MsgBox "Failed because of Key voliation, CDC Id Number and TestNo should be
unique"
Resume Exit_cmd_load2010_rrtnew_Click
End Sub

 

[Dirk Goldgar]

Here is my code .. I am trying to insert data into database from staging
table to main table. So if the load is successful then it should display
"Successful message" . If it fails ( mainly because I have primary key in
my
main table so if inserting duplicates system shows Alert saying Key
violation
for the lab tech when they hit the load data command.

Private Sub cmd_load2010_rrtnew_Click()

Dim stDocName As String

stDocName = "Insert_Results_into_2010_RRTnew"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox "Upload completed Successfully"
Exit_cmd_load2010_rrtnew_Click:
Exit Sub

Err_cmd_load2010_rrtnew_Click:
MsgBox Err. Description
'MsgBox "Failed because of Key voliation, CDC Id Number and TestNo should
be
unique"
Resume Exit_cmd_load2010_rrtnew_Click
End Sub

I thought you were using Doug's code. I agree completely with Doug; you
should be using the Execute method for this. Set a reference to DAO as
described in an earlier post, and try this version:

'------ start of revised code ------
Private Sub cmd_load2010_new_Click()

On Error GoTo Err_Handler

CurrentDb.Execute "Insert_RESULTS_into_2010_RRT", dbFailOnError
MsgBox "Upload completed Successfully"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 3022 Then
MsgBox _
"Failed because of Key voliation, CDC Id Number and " & _
"TestNo should be unique", _
vbExclamation, _
"Upload Failed"
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'------ end of revised code ------

The above is slightly different from Doug's, reflecting both my personal
preferences and more detailed error-handling.

 

[mls via AccessMonster.com]

Thank you Dirk. This works perfect.

Here is my code .. I am trying to insert data into database from staging
table to main table. So if the load is successful then it should display

[quoted text clipped - 21 lines]

Resume Exit_cmd_load2010_rrtnew_Click
End Sub

I thought you were using Doug's code. I agree completely with Doug; you
should be using the Execute method for this. Set a reference to DAO as
described in an earlier post, and try this version:

'------ start of revised code ------
Private Sub cmd_load2010_new_Click()

On Error GoTo Err_Handler

CurrentDb.Execute "Insert_RESULTS_into_2010_RRT", dbFailOnError
MsgBox "Upload completed Successfully"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 3022 Then
MsgBox _
"Failed because of Key voliation, CDC Id Number and " & _
"TestNo should be unique", _
vbExclamation, _
"Upload Failed"
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'------ end of revised code ------

The above is slightly different from Doug's, reflecting both my personal
preferences and more detailed error-handling.

 

[mls via AccessMonster.com]

Hi Dirk,
Is there a way I can modify this code to out put the ID numbers in my message
box for the failed Insert process?

Thanks,
Lakshmi

Thank you Dirk. This works perfect.
[quoted text clipped - 34 lines]

The above is slightly different from Doug's, reflecting both my personal
preferences and more detailed error-handling.

 

[Dirk Goldgar]

Hi Dirk,
Is there a way I can modify this code to out put the ID numbers in my
message
box for the failed Insert process?

Thanks,
Lakshmi

Lakshmi -

There is no direct way to determine the ID numbers of the records that
couldn't be added due to key violations. You could open a recordset on a
"find duplicates" query that would return all the records with IDs that are
in both the source table and the target table. You could then loop through
that recordset and build a list of the ID values, and display that to the
user in a message box.

Unfortunately, I don't know enough details of your tables to be able to give
you the SQL of such a query. However, the basic code for the process would
be something like this:

'------ start of code ------
Private Sub cmd_load2010_new_Click()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDuplicates As String

Set db = CurrentDb

db.Execute "Insert_RESULTS_into_2010_RRT", dbFailOnError
MsgBox "Upload completed Successfully"

Exit_Point:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Sub

Err_Handler:
If Err.Number = 3022 Then

' Make a list of the duplicate IDs.
' Assume you have defined "qryDuplicateResults" to
' return the duplicates.
Set rs = db.OpenRecordset("qryDuplicateResults", dbOpenSnapshot)
With rs
Do Until .EOF
strDuplicates = strDuplicates & ", " & !ID
.MoveNext
Loop
.Close
End With
Set rs = Nothing

MsgBox _
"Failed because of Key violation -- CDC Id Number and " & _
"TestNo should be unique. " & _
"The duplicate IDs were: " & Mid$(strDuplicates, 3), _
vbExclamation, _
"Upload Failed"

Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'------ end of code ------

 

[mls via AccessMonster.com]

Dirk, I used the following query as qryDuplicateResults and this is not
working..
SELECT First([table1].ID) AS [ID Field], First([table1].TestNo) AS [TestNo
Field], Count([table1].ID) AS NumberOfDups
FROM [table1] INNER JOIN RESULTS ON ([table1].TestNo = RESULTS.TestNo) AND (
[table1].ID = RESULTS.ID)
GROUP BY [table1].ID, [table1].TestNo
HAVING (((Count([table1].ID))>1) AND ((Count([table1].TestNo))>1));

Hi Dirk,
Is there a way I can modify this code to out put the ID numbers in my

[quoted text clipped - 3 lines]

Thanks,
Lakshmi

Lakshmi -

There is no direct way to determine the ID numbers of the records that
couldn't be added due to key violations. You could open a recordset on a
"find duplicates" query that would return all the records with IDs that are
in both the source table and the target table. You could then loop through
that recordset and build a list of the ID values, and display that to the
user in a message box.

Unfortunately, I don't know enough details of your tables to be able to give
you the SQL of such a query. However, the basic code for the process would
be something like this:

'------ start of code ------
Private Sub cmd_load2010_new_Click()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDuplicates As String

Set db = CurrentDb

db.Execute "Insert_RESULTS_into_2010_RRT", dbFailOnError
MsgBox "Upload completed Successfully"

Exit_Point:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Sub

Err_Handler:
If Err.Number = 3022 Then

' Make a list of the duplicate IDs.
' Assume you have defined "qryDuplicateResults" to
' return the duplicates.
Set rs = db.OpenRecordset("qryDuplicateResults", dbOpenSnapshot)
With rs
Do Until .EOF
strDuplicates = strDuplicates & ", " & !ID
.MoveNext
Loop
.Close
End With
Set rs = Nothing

MsgBox _
"Failed because of Key violation -- CDC Id Number and " & _
"TestNo should be unique. " & _
"The duplicate IDs were: " & Mid$(strDuplicates, 3), _
vbExclamation, _
"Upload Failed"

Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'------ end of code ------

 

[Dirk Goldgar]

Dirk, I used the following query as qryDuplicateResults and this is not
working..
SELECT First([table1].ID) AS [ID Field], First([table1].TestNo) AS [TestNo
Field], Count([table1].ID) AS NumberOfDups
FROM [table1] INNER JOIN RESULTS ON ([table1].TestNo = RESULTS.TestNo) AND
(
[table1].ID = RESULTS.ID)
GROUP BY [table1].ID, [table1].TestNo
HAVING (((Count([table1].ID))>1) AND ((Count([table1].TestNo))>1));

As usual, please define "not working". Does the above query give you the
results you want? My guess is that it doesn't, but I could be wrong. Which
table is your source table for the append operation, and which is your
target table? What constitutes a "duplicate" -- a match on both ID and
TestNo, a match on ID alone, a match on TestNo alone?

 

[mls via AccessMonster.com]

My base table is 'Table1'. My staging table is 'Results' table which has all
transactional data. I need to append 'Results' data into 'Table1'. I created
a composite key with ID and TestNo. Each individual can have 4 records at
the maxium. so I am checking on both ID and TestNo.

Dirk, I used the following query as qryDuplicateResults and this is not
working..

[quoted text clipped - 5 lines]

GROUP BY [table1].ID, [table1].TestNo
HAVING (((Count([table1].ID))>1) AND ((Count([table1].TestNo))>1));

As usual, please define "not working". Does the above query give you the
results you want? My guess is that it doesn't, but I could be wrong. Which
table is your source table for the append operation, and which is your
target table? What constitutes a "duplicate" -- a match on both ID and
TestNo, a match on ID alone, a match on TestNo alone?

 

[Dirk Goldgar]

My base table is 'Table1'. My staging table is 'Results' table which has
all
transactional data. I need to append 'Results' data into 'Table1'. I
created
a composite key with ID and TestNo. Each individual can have 4 records at
the maxium. so I am checking on both ID and TestNo.

I'm not sure I understand what you mean when you say "each individual can
have 4 records at the maximum". Is it necessary to do some sort of count --
heretofore unmentioned -- or can we simply match records based on ID and
TestNo, and say that any record in Results with the same ID and TestNo as a
record in Table1 is a duplicate? That's what I expected, but I don't want
to steer you wrong.

 

[mls via AccessMonster.com]

That's exactly what I want to do..
simply match records based on ID and TestNo, and say that any record in
Results with the same ID and TestNo as a record in Table1 is consider as
duplicate.

 

[Dirk Goldgar]

That's exactly what I want to do..
simply match records based on ID and TestNo, and say that any record in
Results with the same ID and TestNo as a record in Table1 is consider as
duplicate.

Try this as the SQL for qryDuplicateResults:

SELECT Results.*
FROM
Results
INNER JOIN
Table1
ON Results.ID = Table1.ID
AND Results.TestNo = Table1.TestNo

 

[mls via AccessMonster.com]

Thank you Dirk. With this you taught me how I can write free hand SQL queries
in ACCESS. I am new to ACCESS (using for the past one month), so initially I
tried writting an a update query with CASE STATEMENT and spent literally 2
hours to debug with the parenthesis, so I gave up and started using built in
functionality. Now I am trying to learn programming in ACCESS and thank you
very much for all your encouragement to debug my first program.