Prevent Adding Duplicate based on 3 column uniqueness

[Rx_]

Access 2007 - Linked Tables
This is part of a Add button that also calls the double_click to add an item in the listbox to another list box.
i.e. Private Sub lstWellsByArea_DblClick(Cancel As Integer)

Goal: prevent a duplicate record from being added- of the columns ID_NEPA, ID_Wells, and Activity (where Activity = "A")

considering: before line 60, create SQL statement, test for recordcount greater than zero, if true then set Cancel = True and exit sub
In effect ignoring the request to add a duplicate.

Or: add an index for uniqueness - and trap the error - exit the subroutine
The problem with this one is: When Activity = "A" On Add New Activity = "A" on Delete Activity = "I" so, in theory, there could be multiple "I" for the three fields.


Any suggestions? What would be the best way?

Existing Code:

40        Set dbNEPA_Well = CurrentDb()
50        Set rstNEPA_WellTable = dbNEPA_Well.OpenRecordse (SQLNEPA_WellTable, dbOpenDynaset)
60        rstNEPA_WellTable.AddNew
70            rstNEPA_WellTable!ID_NEPA = Me.Recordset!ID_NEPA
80            rstNEPA_WellTable!ID_Wells = Me.lstWellsByArea.Column(0) 'ID_Wells
90            rstNEPA_WellTable!Activity = "A"  
100           rstNEPA_WellTable!Created = Now()
110           rstNEPA_WellTable.Update        
120       rstNEPA_WellTable.Close
130       Set rstNEPA_WellTable = Nothing
140       Set dbNEPA_Well = Nothing

To add an unique index to three fields, the following instructions were found:
create multifield index to enforce unique combinations
1. Open the table in Design view
2. Click Indexes on the toolbar.
3. In the first blank row in the Index Name column, type a name for the index ). You can name the index after one of the index fields, or use another name.
4. In the Field Name column, click the arrow and select the first field for the index.
5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.

 

[Rx_]

The DCount does not seem to be a solution.
The formula below works until the Activity field is added.
The two numbers plus a single character (A or I in Activity) cause a data mismatch error.

? dcount("ID_NEPA_Wells", "NEPA_Wells", "ID_NEPA & ID_Wells + Activity = " & Me.Recordset!ID_NEPA & Me.lstWellsByArea.Column(0) & "A")

 

[vbaInet]

You're back to the habit of concatenating criteria in a DLookup(). I vaguely remember having this conversation with you.

Use the correct syntax:

... "ID_NEPA = " & Me!ID_NEPA & " AND ID_Wells = ..."

 

[Rx_]

Thank You!! Your absolutely right. I was searching (in vain) for the AND and could not find it. With your name, I found the post. As an FYI to others
Here

Ended up just reusing my existing data object to throw a SQL Statement in and look at the record count. Mostly the same concept. It does work very fast with my double click.

Private Sub lstWellsByArea_DblClick(Cancel As Integer)
      Dim dbNEPA_Well                 As DAO.Database
      Dim rstNEPA_WellTable           As DAO.Recordset
      Dim SQLNEPA_WellTable           As String     ' Make copy in Associated Wells
      Dim NEPA_WellTableName          As String
      Dim i                           As Integer
10    On Error GoTo Error_CopyCurrentRecordToNEPA_Well
20        NEPA_WellTableName = "NEPA_Wells"
30        Set dbNEPA_Well = CurrentDb()
40        SQLNEPA_WellTable = "SELECT NEPA_Wells.ID_NEPA, NEPA_Wells.ID_Wells, NEPA_Wells.Activity FROM " & NEPA_WellTableName & _
          " WHERE (((NEPA_Wells.ID_NEPA)= " & Me.Recordset!ID_NEPA & ") AND ((NEPA_Wells.ID_Wells)= " & Me.lstWellsByArea.Column(0) & ") AND ((NEPA_Wells.Activity)='A'));"
50        Set rstNEPA_WellTable = dbNEPA_Well.OpenRecordset(SQLNEPA_WellTable, dbOpenDynaset)
60        If Not rstNEPA_WellTable.EOF Then  ' checks for zero record condition
70            rstNEPA_WellTable.MoveLast
80            i = rstNEPA_WellTable.RecordCount  ' count not really needed, might be useful for data cleanup later
90        Else
100           i = 0
110       End If
120       rstNEPA_WellTable.Close
130       Set rstNEPA_WellTable = Nothing
 
140       If i > 0 Then
150           Cancel = True
              ' well already exist don't add again
160       Else
              ' well does not exist for this document number - add it
170           SQLNEPA_WellTable = "SELECT " & NEPA_WellTableName & ".* FROM " & NEPA_WellTableName & ";"
180           Set rstNEPA_WellTable = dbNEPA_Well.OpenRecordset(SQLNEPA_WellTable, dbOpenDynaset)
190           rstNEPA_WellTable.AddNew
200               rstNEPA_WellTable!ID_NEPA = Me.Recordset!ID_NEPA
210               rstNEPA_WellTable!ID_Wells = Me.lstWellsByArea.Column(0) 'ID_Wells
220               rstNEPA_WellTable!Activity = "A"  ' Me.lstWellsByArea.Column(2) 'Activity - it is filterd to A - if deleted ' force "A" on 12/6/2010 change
230               rstNEPA_WellTable!Created = Now()
240           rstNEPA_WellTable.Update
 
              '     Close the NEPA_Well database
250           rstNEPA_WellTable.Close
260           Set rstNEPA_WellTable = Nothing
270           Set dbNEPA_Well = Nothing
280       End If

 

[vbaInet]

Ok, there's a block of code you don't really need. I would get rid of lines 60 to 140 and replace it with:

rstNEPA_WellTable.Close
Set rstNEPA_WellTable = Nothing

If rstNepa_WellTable.RecordCount > 0 Then

If all you're testing for is if at least a record exists then you don't need to populate the recordset with MoveLast. Saves you processing time.

Just fyi: Make use of With ... End With block for the recordset objects.

 

[Rx_]

Excellent advice!
Fully understand the value of it.

 

[vbaInet]

You're welcome!

In case you want to try out your DCount() again, here is it:

DCount("ID_NEPA_Wells", "NEPA_Wells", "ID_NEPA = " & Me!ID_NEPA & " AND ID_Wells = " & Me.lstWellsByArea.Column(0) &  " AND Activity = 'A'")

 

[Rx_]

Absolutely Right! Took out line 40 through 130 and replaced it with that

It returns a 0 or 1 That should shorten a few stepts.
Maybe I can *remember* this for the next time

For a small (and narrow) table that will never see 3,000 records, this should be a fairly efficient way to check.

i = DCount("ID_NEPA_Wells", "NEPA_Wells", "ID_NEPA = " & Me!ID_NEPA & " AND ID_Wells = " & Me.lstWellsByArea.Column(0) & " AND Activity = 'A'")

Thanks again.

 

[vbaInet]

You're welcome.