Method For Catching Key Violations when Appending Data to A Table

[catbeasy]

I have SQL code that appends data to a table. The table has an associate id as a primary key so that two identical associate id's cannot be appended to the table.

However, when the append code is run, Access doesn't provide an error message I can use in code to inform the user that they cannot append that particular record to the table (since it already exists in the table). The record just doesn't append.

Was looking for suggestions on how I can make Access tell me that the record already exists and so I can let the user know that they must re-enter a different associate id..

Thanks for any suggestions..

 

[HiTechCoach]

It would help to see what code you are using.

My first though would be to run a query that looks for matching records. If found, then alert the user, otherwise append the data.

Is there some reason that you as not entering the data directing without using a temp table? This would avoid the need to append the data and also alert the user as soon as the data was entered. This would also be more efficient.

 

[catbeasy]

It would help to see what code you are using.

My first though would be to run a query that looks for matching records. If found, then alert the user, otherwise append the data.

Is there some reason that you as not entering the data directing without using a temp table? This would avoid the need to append the data and also alert the user as soon as the data was entered. This would also be more efficient.

I am using a temp table so as to avoid having multiple people save to the same table. So each time a user selects their id, the code creates a temp table that accepts all their data and their data only (it is a unique table name based on their id and the date/time). When done the data from that table is sent via email, appended to an archive and then the temp table is deleted..

I suppose I can use a Dlookup to see if anything matches the records, but was hoping there was some system generated error that would make it a little simpler..

 

[HiTechCoach]

I am using a temp table so as to avoid having multiple people save to the same table. So each time a user selects their id, the code creates a temp table that accepts all their data and their data only (it is a unique table name based on their id and the date/time). When done the data from that table is sent via email, appended to an archive and then the temp table is deleted..

I suppose I can use a Dlookup to see if anything matches the records, but was hoping there was some system generated error that would make it a little simpler..

I suppose I can use a Dlookup to see if anything matches the records, but was hoping there was some system generated error that would make it a little simpler..

I would not use a DLookup!

There may be a way, but without seeing your VBA code, thee is know way to know.

I do lots of importsing and data conversions. I have found that when you do want to rapp the errors, that it best to process the data to find issues before the append than wait to try to trap the error after the fact.

If you want to trap the error after the fact, then you will need to append one re4ocr at a time.

I really see no reason for the temp table based on your explanation. IMHO, it is best to avoid moving data between tables whenever possible. In fact, I believe your explanation and what you need done helps make a stronger case for not using a temp table.

 

[gemma-the-husky]

you are probably using

docmd.runsql or docmd.openquery with
warnings off

paul (pbaldy) taught me many moons ago that

currentdb.execute sqlstatement is better, as it does although you to intercept errors.It used to be transactional (nothing got written if there were errors) , but i think i noted a posting that said in A2007, it doesnt now cancel the whole task, if there are any failures.

try it and see

 

[catbeasy]

I would not use a DLookup!

There may be a way, but without seeing your VBA code, thee is know way to know.

I do lots of importsing and data conversions. I have found that when you do want to rapp the errors, that it best to process the data to find issues before the append than wait to try to trap the error after the fact.

If you want to trap the error after the fact, then you will need to append one re4ocr at a time.

I really see no reason for the temp table based on your explanation. IMHO, it is best to avoid moving data between tables whenever possible. In fact, I believe your explanation and what you need done helps make a stronger case for not using a temp table.

Here is my code. Note, it is not finished yet. There are still some procedures to add.

The dlookup is used prior to saving the record to the temp table. It takes the value of the associate id entry and checks it against what's already in the table. If its there, a msg box returns and says they can't add this associate without deleting the record first (assuming they are re-entering due to a mistake in the first entry).

As far as the use of the temp table. I did try using one table the first time I made this app (this is "Mark II" of an app). It did work, but I had to do some things that I don't do using a temp table. There doesn't appear, at this point, to be any disadvantages creating a temp table. However, this isn't an app where there are multiple users continually using the app concurrently.

Note, the code: Call sub_save_record_pr, saves the record to the temp table and is an INSERT sql statement run by: d.execute.


Private Sub cmd_save_rec_pr_Click()
Dim d As Database
Dim r As Recordset
Dim Assoc_ID As String
Set d = CurrentDb
Set r = d.OpenRecordset(str_obj_name_tbl, dbOpenDynaset)
Assoc_ID = Me.cbo_assoc_id
Dim str_msg_assoc_exists As String
Dim str_msg_confirm_save As String
Dim str_msg_confirm_next As String
Dim str_plural_chk As String

str_msg_assoc_exists = "You have already entered a record for this associate. This record will be canceled. Please enter a record for a new associate." _
& vbCrLf & vbCrLf & "If you entered the original associate record in error, please delete that record first and then re-enter the associate."

str_msg_confirm_save = "Click YES to save this record. You can then elect to continue to enter records, or if done, send this record to yourself on email" _
& vbCrLf & vbCrLf _
& "Click NO if you do not wish to save this record"

str_msg_confirm_next = "Record saved. You have now " & r.RecordCount & " saved record(s)" & vbCrLf & vbCrLf _
& "Click YES if you want to add another Associate record" & vbCrLf & vbCrLf _
& "Click NO if you are done entering Associates."

If Assoc_ID = DLookup("[assoc_id]", str_obj_name_tbl, "assoc_id = '" & Assoc_ID & "'") Then
MsgBox str_msg_assoc_exists
Call sub_cxl_assoc_rec
Else
If fcn_confirm(str_msg_confirm_save) = True Then
Me.cmd_set_focus.SetFocus
Call sub_save_record_pr 'saves the record to the requester's table..
Call sub_reset_controls 'resets the form controls to null
Call sub_ctl_visibility_false 'makes controls invisible
r.Requery
r.MoveLast

If r.RecordCount > 1 Then
str_plural_chk = " saved records"
Else
str_plural_chk = " saved record"
End If

str_msg_confirm_next = "Record saved. You now have " & r.RecordCount & str_plural_chk & vbCrLf & vbCrLf _
& "Click YES If you want to Add another record." & vbCrLf & vbCrLf _
& "Click NO if you are done adding records."

If fcn_confirm(str_msg_confirm_next) = True Then
MsgBox "Please select another record"
Else
MsgBox "Code goes here to send this record to requester email."
End If

Else
MsgBox "You have chosen to cancel the save procedure."
End If
End If
End Sub