locked
BeforeUpdate validation using values from related tables RRS feed

  • Question

  • I need assistance in figuring out the best way to perform a specific type of BeforeUpdate data validation.

    The form has Work Record information; it is based on a table that contains this information: tblWorkRecords.

    The user selects a volunteer from the drop-down list, enters the date worked, hours worked, and selects the program that the volunteer worked in from the WorkCategory drop-down.  Before the record is saved, validation needs to occur to check if the volunteer's background check is not more than a year old AND the program is a youth program.  

    I need help in figuring out the best way to pull the corresponding BackgroundCheckDate value from tblVolunteers, and the YouthProgram value from tblWorkCategories.

    =======================

    Edit:  Thanks to the assistance I received, I have solved a portion of my problem.  However, I still need to check if the BackgroundDate field is empty.  If it is, then an error message needs to be displayed.  Here is what I am currently using.  When I attempt to add a new record or update an existing one for a volunteer who does not have a background check on file (empty BackgroundDate field), Access simply refuses to allow the update to be made.  No error message is displayed, and the only way to get "unstuck" is to hit the Esc key and back out my changes.

    ==================================

    Edit:  Here is the working code, in case anyone ever needs something like it:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
       
        Dim DateWorked As Date
        Dim oldDate As Date
        Dim bkgdCheckDate As Variant
        Dim YouthProgram As Integer
              
        DateWorked = Forms!frmWorkRecordEdit!DateWorked
              
        oldDate = DateAdd("yyyy", -1, DateWorked)
              
        bkgdCheckDate = DLookup("[BkgrdCheckDate]", "tblVolunteers", "[Volunteer_ID] = " & Forms!frmWorkRecordEdit!Volunteer)
       
        YouthProgram = DLookup("[YouthProgram]", "tblWorkCategories", "[Category_ID] = " & Forms!frmWorkRecordEdit!WorkCategory)
       
        If YouthProgram < 0 Then
            If (bkgdCheckDate < oldDate) Then
                MsgBox ("Volunteer's background check has expired!" & _
                vbNewLine & "Last background check was on " & bkgdCheckDate)
            ElseIf IsNull(bkgdCheckDate) Then
                MsgBox ("Volunteer does not have a background check on file!")
            Else
                'do nothing
            End If
        End If
       
    End Sub


    Monday, March 3, 2014 6:29 PM

Answers

  • There a several ways to pull the information from other tables, but in your case it's probably easiest to use the DLookup function.  You might want to use it in code vaguely like this:

    If DLookup("BackgroundCheckDate", "tblVolunteers", "volunteerID = " & Me.volunteerID) _
                                             < DateAdd("yyyy", -1, Me.dateworkedfield) _
             AND DLookup("YouthProgram ", "tblWorkCategories", "volunteerID = " & Me.volunteerID) _   Then
          MsgBox("Needs  more recent background check")
    End If

    Monday, March 3, 2014 7:14 PM
  • I need help in figuring out the best way to pull the corresponding BackgroundCheckDate value from tblVolunteers, and the YouthProgram value from tblWorkCategories.

    I am guessing that your data entry form is an unbound form (note: data entry forms must be unbounded to any tables) then if you are submitting the record from a button on a form you can perform a date difference check in the VBA code behind the button as follows:

    Sub btnSubmit_Click()
      Dim bgckDt As Date, curEntryDate As Date
      Dim dayCnt as Integer, volName As String

      volName = ComboVolunteernames
      bgckDt = DLookUp("BackgroundCheckDate", "tblVolunteers", "volunteerName = '" & volName "')
      CurEntryDate = txtCurEntryDate
      dayCnt = DateDiff("d", bgckDt, CurEntryDate)
     
      If dayCnt < 365 Then
        '--proceed with submitting the record
        ...
      Else
        Msgbox "The backgroundcheck is out of date for " & volName
      End If
    End Sub



    Rich P

    Monday, March 3, 2014 7:33 PM
  • You will need to declare the bkgdCheckDate variable as Variant data type to allow for the Null.

    Ken Sheridan, Stafford, England

    Monday, March 3, 2014 10:41 PM

All replies

  • There a several ways to pull the information from other tables, but in your case it's probably easiest to use the DLookup function.  You might want to use it in code vaguely like this:

    If DLookup("BackgroundCheckDate", "tblVolunteers", "volunteerID = " & Me.volunteerID) _
                                             < DateAdd("yyyy", -1, Me.dateworkedfield) _
             AND DLookup("YouthProgram ", "tblWorkCategories", "volunteerID = " & Me.volunteerID) _   Then
          MsgBox("Needs  more recent background check")
    End If

    Monday, March 3, 2014 7:14 PM
  • I need help in figuring out the best way to pull the corresponding BackgroundCheckDate value from tblVolunteers, and the YouthProgram value from tblWorkCategories.

    I am guessing that your data entry form is an unbound form (note: data entry forms must be unbounded to any tables) then if you are submitting the record from a button on a form you can perform a date difference check in the VBA code behind the button as follows:

    Sub btnSubmit_Click()
      Dim bgckDt As Date, curEntryDate As Date
      Dim dayCnt as Integer, volName As String

      volName = ComboVolunteernames
      bgckDt = DLookUp("BackgroundCheckDate", "tblVolunteers", "volunteerName = '" & volName "')
      CurEntryDate = txtCurEntryDate
      dayCnt = DateDiff("d", bgckDt, CurEntryDate)
     
      If dayCnt < 365 Then
        '--proceed with submitting the record
        ...
      Else
        Msgbox "The backgroundcheck is out of date for " & volName
      End If
    End Sub



    Rich P

    Monday, March 3, 2014 7:33 PM
  • Thanks!  I was able to use this to get close to where I need to be.  My next question is how to check if a volunteer doesn't have a background check on file (no date in BackgroundCheck field).

    Here's what I have so far:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        
        Dim DateWorked As Date
        Dim oldDate As Date
        Dim bkgdCheckDate As Date
        Dim youthProgram As Integer
               
        DateWorked = Forms!frmWorkRecordEdit!DateWorked
               
        oldDate = DateAdd("yyyy", -1, DateWorked)
               
        bkgdCheckDate = DLookup("[BkgrdCheckDate]", "tblVolunteers", "[Volunteer_ID] = " & _ Forms!frmWorkRecordEdit!Volunteer)
        
        youthProgram = DLookup("[YouthProgram]", "tblWorkCategories", "[Category_ID] = " & _ Forms!frmWorkRecordEdit!WorkCategory)
        
        If (youthProgram < 0) Then
            If (bkgdCheckDate < oldDate) Then
                MsgBox ("Volunteer's background check has expired!" & _
                vbNewLine & "Last background check was on " & bkgdCheckDate)
            End If
        End If
        
    End Sub

    I need to add an ElseIf (bkgdCheckDate IS NULL) Then MsgBox("no date").  However, When I try the following, it doesn't work.  I've tried IsNull(bkgdCheckDate ), and len(bkgdCheckDate = 0), and these both break the function.  And I've tried (bkgdCheckDate = ""), but that doesn't pick up the sample record that I have that doesn't have a date on file.

    If (youthProgram < 0) Then
            If (bkgdCheckDate < oldDate) Then
                MsgBox ("Volunteer's background check has expired!" & _
                vbNewLine & "Last background check was on " & bkgdCheckDate)
    ElseIf(IsNull(bkgdCheckDate)) Then
    MsgBox("Volunteer has no background check on file!")
    End If
    End If

    Monday, March 3, 2014 9:36 PM
  • You will need to declare the bkgdCheckDate variable as Variant data type to allow for the Null.

    Ken Sheridan, Stafford, England

    Monday, March 3, 2014 10:41 PM
  • Ah!  Thank you so much!  Everything is working perfectly now.
    Monday, March 3, 2014 10:44 PM