preventing duplicate records where two field are the same

[Rockape]

Hi,

I've checked many other posts regarding this same issue but I'm still having probs. I would prefer a Before Update event procedure.



The code I'm using is :

If DCount("*", "tbl_XXX", "[ID]="" & "[NOM]= Y") > 0 Then

where
tbl_XXX is the table being updated
[ID] is one of the fields
[NOM] is a y/n field (i.e if its toggled it is Yes)

I am using A03

Grateful for the correction

Thanks

 

[Jeanette Cunningham]

Private Sub MySub()
Dim lngCount As Long
Dim strCriteria As String

strCriteria = "[Nom]" = True
strCriteria = strCriteria & " AND [ID] = " & Me.[ID]
Debug.Print strCriteria
lngCount = DCount("*", "XXX", strCriteria)
Debug.Print lngCount

'other code here to use the value returned from lngCount
If lngCount >0 Then
' a duplicate
End If
End Sub

 

[Rockape]

Hi,

the code worked, however it disabled my undo button which would have reset all the fields in the form.

Also because I cant reset I have to close the form, and when I look at the table the entries have been saved. This code is only advising me that a duplicate exists but does not prevent me from saving it.

 

[Jeanette Cunningham]

Yes.
Put the code in the before update event of the form.
If lngCount is greater than 0, then use the cancel parameter built in to the before update event.

Something like this:
If lngCount >0 Then
Cancel = True
End If

Of course, you will need to add some code that checks if the user has entered something in the fields ID and Nom before you run the DCount code, otherwise you will get errors when you try to get the DCount.

The above is a good start to your code to prevent duplicates.

 

[Rockape]

Hi,

I am having problems with the code. Are you still available to help me out?

Yes.
Put the code in the before update event of the form.
If lngCount is greater than 0, then use the cancel parameter built in to the before update event.

Something like this:
If lngCount >0 Then
Cancel = True
End If

Of course, you will need to add some code that checks if the user has entered something in the fields ID and Nom before you run the DCount code, otherwise you will get errors when you try to get the DCount.

The above is a good start to your code to prevent duplicates.

 

[MyTech]

Put the following into the BeforeUpdate:

Dim lngCount As Long
Dim strCriteria As String

strCriteria = "[Nom]" = True
strCriteria = strCriteria & " AND [ID] = " & Me.[ID]
lngCount = DCount("*", "XXX", strCriteria)

    If lngCount >0 Then
        Cancel = True
    End If

 

[Jeanette Cunningham]

Yes, of course. Post back with the code you have written and show the bits that don't work.

 

[Rockape]

Hi,

Tried this code and still it did not work!

Put the following into the BeforeUpdate:

Dim lngCount As Long
Dim strCriteria As String
 
strCriteria = "[Nom]" = True
strCriteria = strCriteria & " AND [ID] = " & Me.[ID]
lngCount = DCount("*", "XXX", strCriteria)
 
    If lngCount >0 Then
        Cancel = True
    End If

 

[Rockape]

Hi,

I have amended my conditions. It is still two fields that I want to check. They are the ID and I also want it to check the text in a particular field (rather than the Y/N toggle).

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim lngCount As Long
Dim strCriteria As String

strCriteria = "[Status]" = "Active"
strCriteria = strCriteria & " AND [ID] = " & Me.[ID]
lngCount = DCount("*", "XXXX", strCriteria)

If lngCount > 0 Then
Cancel = True

End If

End Sub

 

[Rockape]

Hi,

I have also tried this code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngCount As Long
Dim strCriteria As String

strCriteria = [Status] = "Active"
strCriteria = strCriteria And [ID] = Me.[ID]

Debug.Print strCriteria

lngCount = DCount("*", "XXXX", strCriteria)

Debug.Print lngCount

If lngCount > 0 Then

MsgBox "message...", vbExclamation + vbOKOnly, "Potential data duplication..."
Me.Undo
'Cancel = True

End If

End Sub

 

[MyTech]

Can you send the file or create a sample of it and send it?

 

[Rockape]

Ok...will do so tomorrow....thanks for the offer.

Can you send the file or create a sample of it and send it?

 

[Rockape]

Hi,

Sorry for the delay....

Got around the problem in another way.

Rather than have the user enter all the data before the On Update was triggered, I set up a little routine (make table query) that would check the ID and the status of the ID being inputted.

I based the input form on a query. The routine would then create a table. I then checked this table using dcount and if the result was greater than one , ie. that a record existed which had met the criteria then the routine would stop further entry!

Anyway thanks for the assistance.