Eliminate duplicate data by Dcount

[MoinMalik]

Hi,

I want to eliminate duplicate data entry by using DCount. I have 'Name', 'Date and 'Status' fields. The condition requires that only unique entry is allowed in Status field for a specific Date and Name.

I have used the following code, but encountered syntax error in Dcount statement.


Private Sub Combo10_BeforeUpdate(Cancel As Integer)
Dim tot As Integer
Dim ddd As Date
Dim sss As String

tot = DCount("[status]", "Table1", "[Date]= # ddd # and [Name]= # sss #")

If tot > 0 Then
MsgBox "This information duplicates another record."
Exit Sub
End If
End Sub

Any help will be great.

Moin

 

[pbaldy]

See if this gets you on track:

http://www.mvps.org/access/general/gen0018.htm

You also haven't set your variables to anything. Presumably you'd want to use values from a form.

 

[dkinley]

Along with Paul's advice, you might be throwing an error because if DCount does not find anything, it returns a null.

So either use the IsNull, Not IsNull or Nz to analyze the logic correctly. I've simply used the Nz and evaluated in the same manner as you. For example, ....

tot = Nz(DCount(...),0)

-dK

 

[pbaldy]

I believe you'll find that DCount will return 0 if it doesn't find anything. DLookup will return Null, and would require the treatment you describe.

 

[dkinley]

Haha! You are correct. Is what I get for trolling the boards that late.

I was thinking of the way that I check for duplicates ... DLookup. As an ego recovery mechanism, I shall explain.

I perform a DLookup to fetch and store the primary key of the record into the variable. Then I analyze it for a 0. If not, then I use another DLookup to get the 'name' or identifier of the record so that I may use that in the message box to provide feedback to the user so they may properly search for the already created entry.

-dK