Results 1 to 8 of 8
  1. #1
    agosfernandes is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    10

    vba question

    Hi,

    I'm new here and in VBA as well and was wondering if you can help .

    I´ve created a simple database to register visitors. The main table is called Visitors. In that table there´s a YES/NO field data (AccessDenied) for denied access.
    Whenever there's a recurring visit (same LastName, FirstName and DOB) and the denied access box is selected, I'd like a popup message triggered in this denied access data.
    So far, this code is working to detect duplicate data (names and DOB) but not for the YES/NO field data...


    this is to be used in a data insertion form.

    Thank you in advance!

    here's the code




    Private Sub DOB_AfterUpdate()


    Call CheckData
    End Sub

    Private Sub FirstName_AfterUpdate()
    Call CheckData
    End Sub

    Private Sub LastName_AfterUpdate()
    Call CheckData
    End Sub

    Private Sub Nationality_AfterUpdate()
    Call CheckData
    End Sub



    Sub CheckData()

    Dim criteria As String
    criteria = "LastName='" & LastName & "' And FirstName='" & FirstName & "'" & " And DOB=#" & _
    Format(DOB, "dd/mm/yyyy") & "#"


    If Not IsNull(LastName) And Not IsNull(DOB) And Not IsNull(FirstName) Then

    If IsNull(VisitorID) Then
    VisitorID = DMax("VisitorID", "Visitors") + 1
    End If

    If DCount("VisitorID", "Visitors", criteria) > 0 Then


    MsgBox "This visitor has been denied access!”

    End If

    End If

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try adding

    And AccessDenied = True

    to the criteria. I'm surprised the date works, as normally it has to be formatted in US format.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    agosfernandes is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    10
    thanks!

    like this?

    criteria = "LastName='" & LastName & "' And FirstName='" & FirstName & "'" & " And DOB=#" & _
    Format(DOB, "dd/mm/yyyy") & "#" And AccessDenied = True

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Close; move the last double quote to the end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    agosfernandes is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    10
    not following, can you be more specific?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    criteria = "LastName='" & LastName & "' And FirstName='" & FirstName & "'" & " And DOB=#" & _
    Format(DOB, "dd/mm/yyyy") & "# And AccessDenied = True"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    agosfernandes is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    10
    thank so much! it's working!
    cheers

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2012, 07:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums