All -

I'm attempting to validate data being entered by using a DCount to check if a record exists that matches data and I would then like to check each entry that matches for a value in a particular column. I have sample code that checks if there is a number of records that match which are greater than 0 but I'm not sure how to process each record that matches for some criteria.

See the comments in the code for the big question on what I'm trying to do.

Private Sub cboSelectMember_AfterUpdate()
Dim MemberID As String
Dim memberIDLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

'Determines if the SSN is a new record or not
If Me.NewRecord Then

If Me.cboSelectMember.Value & "" = "" Then
Me.Undo
Exit Sub
Else
MemberID = Me.cboSelectMember.Value
memberIDLinkCriteria = "[MemberID]=" & "'" & MemberID & "'"

'Check tblstudents table for duplicate SSN
If DCount("MemberID", "tblEnrollment", _
memberIDLinkCriteria) > 0 Then
'================================================= ====================
'If the DCount returns an entry, we need to check each row to see if
'those rows actually have the "Graduated" column in the tblEnrollment
'marked as yes or no. If yes, we can add another enrollment. If no,
'that means they are in an active class and can't be added to another.
'================================================= ====================

'Undo the duplicate entry
Me.Undo

'Message box warning of duplication
MsgBox "Warning Student Number " _
& MemberID & " is already enrolled in an active class." _
& vbCr & vbCr & "They are not able to attend more than one class concurrently.", _
vbInformation, "Duplicate Information"

It looks like you didn't post all the code as the DAO recordset isn't used anywhere, but the easier and more efficient way to do this is to move the code into the BeforeUpdate event. This event occurs before you update the recordset, so you don't have to handle all the "undo" stuff.

Private Sub cboSelectMember_BeforeUpdate()

If DCount(cboSelectMember.Value, "tblEnrollment", "MemberID='" & MemberID & "'" > 0 Then
MsgBox "Warning Student Number " _
& MemberID & " is already enrolled in an active class." _
& vbCr & vbCr & "They are not able to attend more than one class concurrently.", _
vbInformation, "Duplicate Information"
Exit Sub
End If

End Sub

Note that no variable declaration is necessary. Additionally, you don't have to check for a NULL Value as this can be controlled by Access for you. Go into the table's design mode and set "Allow Zero Length" to No. Set the Validation Rule to "Is Not Null" (no quotes) and in the Validation Rule, put the message you want to come up when the user tries to enter nothing into the SSN field.

A few other notes. If you only have a single form open, using the explicit call "Me.<controlname>" is not necessary. I also cleaned up your code a little bit as you had extemporaneous ampersands in there (in the criteria for the DCount). What you had would work, but was way overkill. And finally, with controls on a form, you don't want to have the identical name of the field. You have a field called MemberID as well as a control named MemberID. Change the form control to be txtMemberID or something similar, so that you can differentiate between them.

Mike,

In an attempt to answer the question, I thought I would just give a sample that might help. If I was going to do what you're doing, and I had to adapt the style of code writing that you have already got here, this is what I would write to accomplish it:Private Sub cboSelectMember_AfterUpdate()
Dim MemberIDSearch As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

'Determines if the SSN is a new record or not
If Me.NewRecord Then

If Me.cboSelectMember.Value & "" = "" Then
Me.Undo
Exit Sub
Else

with rsc
.movefirst

do until .nomatch = True

.FindNext ("MemberID = MemberIDSearch")

If !Graduated = 0 Then
goto Message
end if

Loop

end with
exit sub

Message:
Me.Undo

'Message box warning of duplication
MsgBox "Warning Student Number " _
& MemberIDSearch & " is already enrolled in an active class." _
& vbCr & vbCr & "They are not able to attend more than one class concurrently.", _
vbInformation, "Duplicate Information"

end if
end if

End Sub

Mike,

In an attempt to answer the question, I thought I would just give a sample that might help. If I was going to do what you're doing, and I had to adapt the style of code writing that you have already got here, this is what I would write to accomplish it:

Awesome. This snippet did the trick.

Thanks!

Well it almost did the trick. It won't let me add new records either, even if they aren't duplicates. I've posted below what I'm using and a screenshot of the query.

Private Sub cboSelectMember_AfterUpdate()
Dim db As Database
Dim MemberID As String
Dim MemberIDSearch As String
Dim MemberIDTable As String
Dim rsc As DAO.Recordset

Set db = CurrentDb()
Set rsc = db.OpenRecordset("qryEnrollment")

cboMemberID = Me.cboSelectMember.Value
MemberIDSearch = "[MemberID]=" & "'" & cboMemberID & "'"
MemberIDTable = [MemberID]

If Me.NewRecord Then

If Me.cboSelectMember.Value & "" = "" Then
Me.Undo
Exit Sub
Else

With rsc
.MoveFirst

Do Until .NoMatch = True

.FindNext (MemberIDTable = cboMemberID)

If rsc![Graduated] = False Then

GoTo Message
End If
Loop
End With
Exit Sub

Message: Me.Undo

MsgBox "The Member you tried to add to is already enrolled." _
& vbCr & vbCr & "in another class and hasn't been marked as graduated.", _
vbInformation, "Duplicate Enrollment"
End If
End If 'Closes Me.NewRecord
End Sub

I think it has to do with my criteria. My query is based on several tables and MemberID shows up twice; in the tblMembers table and tblEnrollment table. I think if I can define it somehow I can correct the situation.

I've been trying :

MemberIDTable = rsc![tblEnrollment].[MemberID]

And no luck. Says it can't find that reference.

I'm clueless.