[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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

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."
0
SteveL13
Asked:
SteveL13
  • 6
  • 4
  • 2
1 Solution
 
SheilsCommented:
Try

On Error Goto Exit1

DoCmd.RunSavedImportExport "ImportWonEstimate"

Exit Sub

Exit1:

msgbox "Your Message"

End Sub
0
 
Jeffrey CoachmanCommented:
<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
0
 
SteveL13Author 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.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
SheilsCommented:
The build in mesage will have an error number. Replace the n in the code submittted by Jeff by that number
0
 
SteveL13Author Commented:
I do not see an error number... screen shot
0
 
SheilsCommented:
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
0
 
SheilsCommented:
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
0
 
SteveL13Author 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
0
 
Jeffrey CoachmanCommented:
sb9, I'll let you take it from here to avoid confusion...

Jeff
0
 
SheilsCommented:
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

0
 
SheilsCommented:
sorry forgot to execute the last query.
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" 

CurrentDB.Execute strSQL  'Delete the new record from the temporary table'

End Sub

Open in new window

0
 
SteveL13Author Commented:
Thank you for being patient.  I appreciate the help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now