We help IT Professionals succeed at work.

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

SteveL13
SteveL13 asked
on
Medium Priority
372 Views
Last Modified: 2013-11-05
I have a saved import routine that will import an Excel file into a table.  But if the key field already exists I want the user to be warned that the record already exists and then end the procedure.

Here's what I have so far...

    DoCmd.RunSavedImportExport "ImportWonEstimate"  (this works fine.  but if the record already exists I want a different msgbox then this...  

If it does not see this record, then this msg box is fine...
   
    MsgBox "The estimate data has been imported.  Proceed to import estimate into job schedule."
Comment
Watch Question

Commented:
Try

On Error Goto Exit1

DoCmd.RunSavedImportExport "ImportWonEstimate"

Exit Sub

Exit1:

msgbox "Your Message"

End Sub
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<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

Author

Commented:
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.

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

Author

Commented:
I do not see an error number... screen shot

Commented:
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

Commented:
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

Author

Commented:
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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

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

Jeff

Commented:
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

Open in new window

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you for being patient.  I appreciate the help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.