DCount and "For Each" record help

[padlocked17]

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"

 

[Moniker]

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.

 

[ajetrumpet]

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

 

[padlocked17]

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!

 

[padlocked17]

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

 

[padlocked17]

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.