UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Handling Error When Using Db.execute, Access 2016    
 
   
LagoDavid
post Jul 6 2018, 12:48 PM
Post#1



Posts: 386
Joined: 12-October 03
From: Texas


I have the following code to execute an update query. I need to change the "dbFailOnError" to where instead of failing when a duplicate record addition is attempted, a message box comes up informing the user that he cannot create a duplicate, and then he is returned to the form. I don't know how/where to put in the error trapping code.



CODE
Private Sub cmdAppendNewSamples_Click()
   Set dbs = CurrentDb()
  
   dbs.Execute "qryAppendUniqueSamplesImported", dbFailOnError
   lngRowsAffected = dbs.RecordsAffected
  
   MsgBox _
      Prompt:="New Samples Added = " & lngRowsAffected, _
      Buttons:=vbOKOnly Or vbInformation, _
      Title:="New Samples Added"
  
   Me.lstAppendedSamples.Requery

  
   dbs.Close
   Set dbs = Nothing

End Sub
Go to the top of the page
 
theDBguy
post Jul 6 2018, 12:53 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,703
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Just one person's humble opinion but rather than "handling" the error, I would personally "avoid" it, if at all possible.

For example, if we know a duplicate record will cause an error, then we can check for a potential duplicate record first before running the "execute" line, to avoid the error in the first place.

You can check for potential duplicate records in various ways, one being the use of either DCount() or DLookup().

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Jul 6 2018, 02:51 PM
Post#3



Posts: 386
Joined: 12-October 03
From: Texas


Your advice has always been good to me, so here is what I am trying (so far unsuccessfully)

CODE
Private Sub cmdAppendNewSamples_Click()
'check for duplication before running the append query to append samples
   Dim LookupFilter As String
   LookupFilter = "SamplePointID = tmptblSamplesLIMS.tmpSamplePointID and "
   LookupFilter = LookupFilter & "SampledDateTime = tmptblSamplesLIMS.tmpSampledDateTime and "
   LookupFilter = LookupFilter & "LIMSSampleID = tmptblSamplesLIMS.tmpLIMSSampleID"
   Debug.Print "lookup filter in cmdAppendNewSamples is " & LookupFilter
  
   If Not IsNull(DLookup("SampleID", "tblSamplesLIMS", LookupFilter)) Then
      LookupFilter = Null
      MsgBox _
      Prompt:="You are attempting to import a sample which is already in the database.", _
      Buttons:=vbOKOnly Or vbInformation, _
      Title:="Duplicate Sample Import"
  
   Else
      LookupFilter = Null
      Set dbs = CurrentDb()
  
      dbs.Execute "qryAppendUniqueSamplesImported", dbFailOnError
      lngRowsAffected = dbs.RecordsAffected
  
      MsgBox _
         Prompt:="New Samples Added = " & lngRowsAffected, _
         Buttons:=vbOKOnly Or vbInformation, _
         Title:="New Samples Added"
  
      Me.lstAppendedSamples.Requery
  

      Me.cmdAppendResults.Enabled = True
  
      dbs.Close
      Set dbs = Nothing
      
   End If

End Sub
Go to the top of the page
 
theDBguy
post Jul 6 2018, 03:04 PM
Post#4


Access Wiki and Forums Moderator
Posts: 72,703
Joined: 19-June 07
From: SunnySandyEggo


Hi,

How many records are you trying to append? If more than one, I'd use a query; otherwise, DCount() (or DLookup) should suffice.

PS. Actually, I would probably still use DCount() on said query (in case there's more than one duplicate).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Jul 6 2018, 03:28 PM
Post#5



Posts: 386
Joined: 12-October 03
From: Texas


Yes, I just realized that I am trying to append multiple records, rather than just one, and my incorrect code would only check one record if it worked at all.


I don't understand "if more than one I would use a query". I am sure your idea is better if I could understand what you mean.


I am importing Excel data and I first import the data into two temporary tables, tmptblSamplesLIMS and tmptblLIMSSampleResults, before inserting them into the permanent tables, tblSamplesLIMS and tblLIMSSampleResults. I have been using the db.execute method to run a predefined append query to append new records from tmptblSamplesLIMS to tblSamplesLIMS. However, when the samples are a duplicate it fails ugly with no hint as to why it is failing. So I tried to check for duplicates as you suggested using the DLookup function.


I apologize, but I need more direction.
Go to the top of the page
 
theDBguy
post Jul 6 2018, 03:38 PM
Post#6


Access Wiki and Forums Moderator
Posts: 72,703
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Okay, so to check if there a duplicate between the two tables, you can create a query joining them. For example:

SELECT * FROM tblSamplesLIMS
INNER JOIN tmptblSamplesLIMS
ON ... list here all the fields to match to be considered a duplicate record

For example, let's say a records is considered duplicate if the SampleID field is the same, then:

ON tblSamplesLIMS.SampleID=tmptblSamplesLIMS.SampleID

Or, if the SampleID, and the SampleDate fields have to match to be considered duplicate, then:

ON tblSamplesLIMS.SampleID=tmptblSamplesLIMS.SampleID AND tblSamplesLIMS.SampleDate=tmptblSamplesLIMS.SampleDate

If you open this query and there's no record showing, then there's no duplicate, and you can freely append the imported data. However, if there's even just one record showing in the query, then it's a duplicate and will cause an error.

To see if there's any record in the query using code, you can use DCount(). For example, if we called the query "qryCheck4Dups", then something like:

CODE
If DCount("*","qryCheck4Dupes")=0 Then
    CurrentDb.Execute "AppendQueryName", dbFailOnError
Else
    MsgBox "Found duplicate records in imported data. Please fix it first.", vbInformation, "Dups Found!"
End If


Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Jul 6 2018, 03:38 PM
Post#7



Posts: 386
Joined: 12-October 03
From: Texas


and yes, typically if there is duplication, there will be multiple duplicate records, not just one.
Go to the top of the page
 
LagoDavid
post Jul 6 2018, 04:34 PM
Post#8



Posts: 386
Joined: 12-October 03
From: Texas


Apparently I cannot create the query in code and use it in DCount?
Or do I somehow give the query a name in code and then use that name in DCount?
I am just trying to avoid yet another named query in my database.

CODE
strSelect = _
      "SELECT tblSamplesLIMS.SamplePointID "
   strFrom = _
      "FROM tblSamplesLIMS INNER JOIN tmptblSamplesLIMS ON " & _
      "tblSamplesLIMS.LIMSSampleID = tmptblSamplesLIMS.tmpLIMSSampleID " & _
      "AND tblSamplesLIMS.SampledDateTime = tmptblSamplesLIMS.tmpSampledDateTime " & _
      "AND tblSamplesLIMS.SamplePointID = tmptblSamplesLIMS.tmpSamplePointID "
   strSQL = strSelect & strFrom
  
   If DCount("*", strSQL) > 0 Then
      MsgBox _
      Prompt:="You are attempting to import a sample which is already in the database.", _
      Buttons:=vbInformation, _
      Title:="Duplicate Sample Import"
Go to the top of the page
 
theDBguy
post Jul 6 2018, 04:41 PM
Post#9


Access Wiki and Forums Moderator
Posts: 72,703
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If you don't want to create a permanent query, then you can use a Recordset. Then, you won't need to use DCount(). Just use RecordCount.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Jul 6 2018, 05:20 PM
Post#10



Posts: 386
Joined: 12-October 03
From: Texas


OK thank you. With your help I was able to get it to work using the record set method.
Go to the top of the page
 
theDBguy
post Jul 6 2018, 05:22 PM
Post#11


Access Wiki and Forums Moderator
Posts: 72,703
Joined: 19-June 07
From: SunnySandyEggo


Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 11:19 AM