Preventing duplicates in a subform with DLOOKUP

[Remster]

Folks



I'm trying to use the following in the Before Update event of a combo box within a subform to prevent duplicates:

 
If DLookup("[Category]", "CategoryTable", [DocumentID] = _
    Forms![DocumentForm]![DocumentID]) = Me.Category.Value Then
    MsgBox "No duplicates!"
End If

It works fine for any entries that duplicate the first record in the subform, but it doesn't work for any of the others. How do I check all the records?

Remster

[RuralGuy]

All arguments for the DLookup() function are strings: http://www.mvps.org/access/general/gen0018.htm

[Remster]

Thanks for replying, but I'm afraid I don't understand what bearing that has on what I'm trying to achieve. Can you elaborate please?

By the way, I've discovered another way of achieving the same result:

 
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("CategoryTable", dbOpenSnapshot)
rs.FindFirst "Category = """ & Me![Category] & """"
If Not rs.NoMatch Then
    Cancel = True
    MsgBox "Duplicate!"
End If

The problem is that while this works perfectly for fields where the row source is a value list (as above), I can't get it to work in this case:

 
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("LinkTable", dbOpenSnapshot)
rs.FindFirst "DocumentID_LinkFrom = """ & Me![DocumentID_LinkFrom] & """"
If Not rs.NoMatch Then
    Cancel = True
    MsgBox "Duplicate!"
End If

Here the row source is (for the sake of the example) 'SELECT DocumentID, DocumentName FROM DocumentTable'. The bound column is 1 (a number) and the visible column is 2 (text). What do I need to add? Please help!

[RuralGuy]

Start getting in the habit of specifying your library when you dim DAO stuff.
Dim rs As DAO.Recordset
...it will save you some grief later on.
As for your ComboBox column issue, if you need the 2nd column data you need to use the .Column property of the cbo.
DocumentID_LinkFrom = """ & Me![DocumentID_LinkFrom].Column(1) & """"
FYI: it is zero based so 0=column1, 1=column2, etc.

[Remster]

Damn, I've just discovered that this approach doesn't work anyway. It prevents me from selecting the same category for different documents.

Back to the method I started this thread with. Can you help? How do I look at all the records in the table to check whether I'm trying to create a duplicate DocumentID-Category pair?

[RuralGuy]

How about using DCount() instead?

[Remster]

How would I do that? I've tried a few things that haven't worked. I presume this much:

 
If DCount("[Category]", "CategoryTable", [DocumentID] = _
    Forms![DocumentForm]![DocumentID] ... ) > 0 Then
    MsgBox "No duplicates!"
End If

But what do I add to indicate that I'm interested in the number of cases where a particular Category, viz. the one that's just been selected, already appears in the same record as the DocumentID in question?

[RuralGuy]

Your 3rd argument is *not* a string!

[Remster]

You're so cruel, giving hints but no answers! You should be a schoolteacher.

Anyway, after much pain, I've come up with this, which seems to work:

 
If DCount("Category", "CategoryTable", _
    "DocumentID = " & Me.Parent!DocumentID.Value & "And Category = '" & Me!Category.Value & "'") _
    > 0 Then
    MsgBox "You have entered a duplicate value."
    Cancel = True
End If

Does that look all right?

[RuralGuy]

Except that you should have a space before the "And Category = '" so it reads:
" And Category = '". I try to remain try to my signature. Anyone coud give you fish!

[Remster]

Can you please tell me (or even just give me a hint!) what's wrong with the following?

DCount("Reference", "DocumentTable", "Reference = '" & Me!Reference.Value & "'" & " And Series = " & Me!Series.Value & " And Version = " & Me!Version.Value)

I'm getting a syntax error message telling me there's an operator missing. For info, Reference is a text field, and Series and Version are number fields.

[RuralGuy]

The .Value property is the defauly property of a Control so you do not need to specify it to save some typing. Your expression can be written as:
DCount("[Reference]", "DocumentTable", "[Reference] = '" & Me.Reference & "' And [Series] = " & Me.Series & " And [Version] = " & Me.Version)
DCount returns a value so this should not be the complete expression. If you use the period "." rather than the bang "!" intellisense helps by looking up the value for you.

[Remster]

Many thanks for your tips. The DCOUNT wasn't my completed expression, but I've worked out what the problem was. I was leaving the Series field blank in my testing, so Me!Series.Value was null/empty (I'm damned if I know the difference). So I've embedded it all in a If Not IsNull clause, and it works fine.

It's been a long slog, but I've now finished my form! No doubt I'll see you in the queries forum in the coming weeks ...

[RuralGuy]

Glad to hear you got it sorted. See how much wiser you are now? Doesn't it feel good?

[Remster]

It does a bit.