Duplicate Data Entry

[Chineham]

Is it possible for a database to recognise that duplicate has been input on a specific date? For example, my database has the same information input onto one particular field daily, however, the other fields do change. Want I want to know is if a user goes in and enters the details more than once can this be detected automatically by the database?

 

[David R]

Can you be more precise/give an example of what you mean by 'the same information input daily'. The same type of information, like the current user name or current date? Or the same "piece" of information every day, like "Microsoft Corp."?

In any case, I think what you want is the Dcount function. In the BeforeUpdate event of your field put something similar to this:

    If DCount("[ParticipantID]", "tableParticipants", "[ParticipantID]= '" & Me![ParticipantID] & "'") = 1 Then
        MsgBox "This is a duplicate Participant ID."
        Me.Undo
        Cancel = True
    End If

Note that in this case the ID is text so it has to be enclosed in single quotes: ' If your data is numeric you can omit these quotes in the Dcount statement.

HTH,
David R

 

[Chineham]

Thanks for your help, I'm gonna see if it works. The information that will input daily is from a combo box, so that is the 'same information daily'. I need it to realise that it is not a duplicates when looking at the table, because, obviously this information will be duplicated over and over again. A date is inpout onto the form when the users are inputting the information. So it needs to look at the date and then realise that this information has been input on that date previously.

Does this make more sense, I hope so

 

[Chineham]

I've tried that but I'm getting an error message of:~

Compile Error:
Expected: expression

and highlights the ' character.

Can you help?

 

[David R]

Is your data numeric? Eliminate the single quotes (') from the expression, they only have to be there if the values are text.

Also if you're checking a date as well the expression gets a bit more complex:

DCount("*", "tableName", "[tableDateField]= " & Me![DateFieldControl] & " AND [tableComboField] = " & Me![comboBoxControl]) = 1

David R


[This message has been edited by David R (edited 04-15-2002).]

 

[Chineham]

I've just found a really simple way of doing it!!

Use the ' Fund Duplicates Query' from the Query Wizard and away you go. Easy as 1, 2, 3!

 

[David R]

You're right, that will work when you run the query. However would it not be better to detect them before they're stored?

The Dcount method can be used as someone's entering a record. If you're unclear how I can give you an example from my database.

 

[Chineham]

I see your point.

Would it be possible to have an example?

 

[David R]

Private Sub comboBoxControl_BeforeUpdate(Cancel As Integer)
 
   If(DCount("*", "tableName", "[tableDateField]= " & Me![DateFieldControl] & " AND [tableComboField] = " & Me![comboBoxControl])) Then
        MsgBox "You have already entered this value for this date. Please pick another."
        Cancel = True
    End If

End Sub

HTH,
David R

[This message has been edited by David R (edited 04-16-2002).]