How check for existing key field in table when performing an import

Try

On Error Goto Exit1

DoCmd.RunSavedImportExport "ImportWonEstimate"

Exit Sub

Exit1:

msgbox "Your Message"

End Sub

<No Points wanted>

...or modify sb9's code for the specific error number.
Something like this roughly:

On Error GoTo Err_SomeError

********** Your Code Goes Here **********

Exit_SomeError:
    Exit Sub

Err_SomeError:
    If Err.Number = n Then
        Resume Exit_SomeError
    Else
        MsgBox "There was an error executing the command." _
        & vbCrLf & "Error " & Err.Number & ": " _
        & vbCrLf & Error, vbExclamation
        Resume Exit_SomeError
    End If

Here "n" will be replaced by your specific "ID already exists" error number.


JeffCoachman

Here is what I have but I am not getting the friendly message box if the record already exists...

On Error GoTo Err_SomeError

    DoCmd.RunSavedImportExport "ImportWonEstimate"
   MsgBox "The estimate data has been imported.  Proceed to import estimate into job schedule."

Exit_SomeError:
    Exit Sub

Err_SomeError:
    If Err.Number = n Then
        Resume Exit_SomeError
    Else
        MsgBox "There was an error executing the command." _
        & vbCrLf & "Error " & Err.Number & ": " _
        & vbCrLf & Error, vbExclamation
        Resume Exit_SomeError
    End If

Please note that I am getting the built in message if the key already exists.

The build in mesage will have an error number. Replace the n in the code submittted by Jeff by that number

I do not see an error number...

I thing you have to change the set warning

DoCmd.SetWarnings False

On Error Goto Exit1

DoCmd.RunSavedImportExport "ImportWonEstimate"

Exit Sub

Exit1:

msgbox "Your Message"

End Sub

There is a list of error numbers on this page

http://www.fmsinc.com/MicrosoftAccess/Errors/ErrorNumber_Description2007.html

If you can find you error you can incorporate Jeff's code

I'm just not geting this.  I'm sorry.  Maybe if I start over.  I have a routine that will import one record into tblEstimates.  That table has a key field named EstN (a text field if that matters).  If that key does not already exist I want the import to take place and have a msgbox appear that says, "The estimate data has been imported.  Please proceed."

However, if the key already exists, I do not want the record to import, it would't anway because I have it set for no duplicates. and a differnt msgbox to appear that says, "This record already exists."

I do not want the built in error message to appear at all.

??? -- Steve

sb9, I'll let you take it from here to avoid confusion...

Jeff

OK If you are importing only one record I will suggest an alternative approach which involves testing the record before add it to the table. And I suggest first importing the data into a temporary table.

So here we go:

Create a table with the same field as tblEstimates, let call it tblTempEstimate

The code is as follows:



 

DoCmd.RunSavedImportExport "ImportWontblTempEstimate" 'Import the new record into the temp table'


Dim StrID as string

StrID=DLookup("EstN ","tblTempEstimate") 'Find the value of the New Key'

Dim i as Integer

i=DCount("EstN ","tblEstimate","EstN=" & chr(34) & StrID & chr(34)) 'If the new key is not in the table i will be = 0)

If i=0 then

Dim strSQL as String

strSQL= "Insert Into tblEstimate Select * From tblTempEstimate"

CurrentDB.Execute strSQL  'Add the new record to tblTempEstimate' 

MsgBox "The estimate data has been imported.  Proceed to import estimate into job schedule."

Else

MsgBox "This record already exists."
       
End If

strSQL = "Delete * From tblTempEstimate" 'Delete the new record from the temporary table'

End Sub

Select allOpen in new window

Thank you for being patient.  I appreciate the help.