Access VBA - Dlookup for checking duplicates (composite unique key)

Question

I have a join table, that consist FK from 2 tables. These two fields are set for composite unique key index, and I need to check for duplicates with DlookUp, If possible. Here is what I tried:

Nz(DLookup("PK_JoinTable", "JoinTable", "FK_FirstTable = " & Me.FK_FirstTable & "AND FK_SecondTable= " & Me.FK_SecondTable & _
                    " AND PK_JoinTable <> " & Nz(Me.PK_JoinTable, 0)), 0)

Is possible to use DLookUp for this case ?

EDIT: Here is my Table design...

JoinTable

PK_JoinTable
FK_FirstTable
FK_SecondTable

These foreign key table fields have unique index. I have a form where I enter data that joins both tables via Combobox. And I need to prevent duplicates entries. Both in Before_Update event, and Click event of "Save" button. This is my whole code for now (click_event of button):

Dim Duplicates As Long

Duplicates = Nz(DLookup("PK_JoinTable", "JoinTable", "FK_FirstTable = " & Me.FK_FirstTable & "AND FK_SecondTable= " & Me.FK_SecondTable & _
                        " AND PK_JoinTable <> " & Nz(Me.PK_JoinTable, 0)), 0)

 If Duplicates > 0 Then

           Cancel = True
      MsgBox "Duplicate entry. This record will not be saved !, vbCritical
      DoCmd.RunCommand acCmdUndo
      Exit Sub

      Else
           ' nothing
      End If

It is possible but that's not a good approach according to me. Dlookup returns only one value and can only query one table. You would better to work with pure SQL and a recordset, or to create a Query. If you need help with this, edit your question and add your tableS structure and a data sample — Thomas G, Apr 14 '16 at 06:24
That's a bit clearer but there's obviously something wrong: in your `Dlookup`, you work on a column named `PK_JoinTable` and I don't see this column described in your table struct. So, still confusing. — Thomas G, Apr 14 '16 at 06:50
ok that's better. And if I understand correctly, the `PK_JoinTable` is always the concatenation of `FK_FirstTable` + `FK_SecondTable` ? Or its totally unrelated ? — Thomas G, Apr 14 '16 at 06:56
Your code looks fine to me, but in my eyes it's not very good practice to relay on code for DB integrity, better to relay on DB tools. I would look into creating a [multicolumn unique index](http://stackoverflow.com/questions/2127698/can-we-create-multicolumn-unique-indexes-on-ms-access-databases). Access has them too... — marlan, Apr 14 '16 at 07:10
I have allready created a multicolumn index, BUT I WANT TO AVOID Access window, tha'ts why I want to check for duplicates myself. Access window keep poping up, that's main problem. — LuckyLuke82, Apr 14 '16 at 07:23
Yup, saw that in later comment. Sorry.. What does Access window say? — marlan, Apr 14 '16 at 07:25

score 1 · Accepted Answer · answered Apr 14 '16 at 07:24

After your explanations in comments, yes it is possible to do this with a Dlookup, but you had a small mistake in yours, you missed a space before AND FK_SecondTable :

Nz(DLookup("PK_JoinTable", "JoinTable", "FK_FirstTable = " & Me.FK_FirstTable & " AND FK_SecondTable= " & Me.FK_SecondTable & _
                    " AND PK_JoinTable <> " & Nz(Me.PK_JoinTable, 0)), 0)

You can also try this approach which achieve the same thing as your Dlookup, but is clearer IMO, offers more possibilities if your query goes more complex, and is easier to debug and tweak.

Dim RST As Recordset
Dim strSQL as string

strSQL = "SELECT DISTINCT(Attribute) FROM Table_Setting WHERE Attribute NOT IN (SELECT Attribute FROM Table_Setting WHERE BookType='" & strBookType & "')"

Set RST = CurrentDb.OpenRecordset(strSQL)
If  Not RST.BOF Then

    ' Looks like we have duplicates

  RST.Close
  Set RS = Nothing

  Cancel = True
  MsgBox "Duplicate entry. This record will not be saved !", vbCritical
  DoCmd.RunCommand acCmdUndo
  Exit Sub          

End If

You were right, what a stupid mistake. Ok I'll test this one too, but my code also works. A BIG THANKS Thomas !!! — LuckyLuke82, Apr 14 '16 at 07:29

score 1 · Answer 2 · answered Apr 14 '16 at 07:29

To debug your DLookup call, construct the WHERE part in a separate string and Debug.Print it (Ctrl+G opens the Immediate window).

strSql = "FK_FirstTable = " & Me.FK_FirstTable & _
         "AND FK_SecondTable= " & Me.FK_SecondTable & _
         " AND PK_JoinTable <> " & Nz(Me.PK_JoinTable, 0)
Debug.Print strSql
Duplicates = Nz(DLookup("PK_JoinTable", "JoinTable", strSql), 0)

You'll notice that you are missing a space between Me.FK_FirstTable and AND.

Nice one Andre, I allready solved my problem, but this is nice to know. Thanks !! — LuckyLuke82, Apr 14 '16 at 07:34

Anthony Griggs · Answer 3 · 2016-04-14T06:44:16.377

0

You should just point these tables together in a query and perform a dlookup on the query if needed. If you specifically need the composite key you can concatenate the needed columns under an alias column.

All in all there's probably a much easier way to accomplish your overall goal but your short on detail... so I just answered with what was provided.

With your updated question... the simplistic way to avoid duplicates is to create a unique index based off the three columns.

edited Apr 14 '16 at 06:44

answered Apr 14 '16 at 06:25

Anthony Griggs

1,469
2
17
39

see my edited question. I hope you'll understand now. – LuckyLuke82 Apr 14 '16 at 06:38
Avoiding duplicates is allready active, from my code you see that I'm trying to avoid Access built-in msgbox. So I don't see why this should help me, Access window will still pop-up. Or did you mean something else ? – LuckyLuke82 Apr 14 '16 at 06:47

score 0 · Answer 4 · answered Apr 14 '16 at 07:32

0

Debugging will be quite complicated when database is big with Dlookup, I suggest using where function is very important as well aa try to use foreign key as much as to remove complexities in ur database. If it is big then only.

answered Apr 14 '16 at 07:32

Atul Agarawal

151
9

Access VBA - Dlookup for checking duplicates (composite unique key)

4 Answers4