Need to check for duplication

[Beth]

Good morning,
I am trying to do some validation to check for duplication in records and I
can't get it to work right.
In my form (frmStudents, based on the Students table) I am trying to assign
student numbers and they can't be duplicated within their class so I want to
have some code on the beforeupdate event for the number.
When I assign the number, I need to make sure that I warn the user if they
try to enter a studentnumber where the user is in the same class, has the
same number, and is marked active (a checkbox). If any of these criteria
don't match, it is an acceptable assignment.

Here is what I have so far, but I don't know if I am even going in the right
direction and I know the syntax is wrong.
If Dlookup(True,"Students","Me.Class=Students.Class and Me.Number =
Students.Number and Students.Active = True" then

code to run

End If


Any suggestions?

Beth

 

[SurveyorinVA via AccessMonster.com]

Hi,
I am not an MVP and there may be a simpilar way, but I do utilize this is an
application I have written.
I would first set up a query that will show only the records based upon your
criteria. I then use an AfterUpdate Event of the text box that used DCount
rather than DLookup. This will count the number of records within the query
when all criteria are met.

Dim lngMyRecord as Long

lngMyRecord = DCount("*","qryStudents")

If lngMyRecord > 0 Then
msg="Record Exists"
prompt = vbOk
title = "Student ID Exists..."
Response = MsgBox(msg,prompt,title)
Exit Sub
Else
Do Something Here
End If

I hope that helps,
CF