DCount Form a few fields

[Jon123]

I'm trying to d a Dcount on 6 fields on a form. My problem is that The same value can be entered twice but not 3 times. Some times the 6 fields will always be different and sometimes some of them maybe the same but never no more than 2 can be entered. Trying to prevent users from entering bad data.
I thought something like this but don't know how to check all 6.
Dim counter As Long

counter = DCount("*", "duplicates", "Field1= [Forms]![frmParts]")
If counter >3 Then
MsgBox "There is a bad record ", vbExclamation, " Possible Duplication Error"
End If

End Sub

Field1
Field2
Field3
Field4
Field5
Field6

 

[plog]

Is this the same issue as this:

https://www.access-programmers.co.uk/forums/showthread.php?p=1543895#post1543895

Have you corrected your table structure? Or do you still have all your parts in their own field in one table?

 

[Mark_]

Jon123,

If you set up a child table for the "Six field", this becomes much easier. You would have a child form (I'd suggest data sheet) that would validate if another record matches instead of trying to compare the individual field entries.

You would use DCount both to make sure there are not too many records (limits to your six) AND to check if there are more than two matching entries.

Use your DCount to check for more than 5 existing records when they add. If yes, bail out and don't let them add.

Use DCount when they are trying to add the part in the child. If there are already 2 then post your message and clear the field.

Using a small child form to show the parts on your parent form will be pretty intuitive for most users. It also removes redundant fields and lets you add more "Parts" if you find out you have different versions. Much more useful when things change to.