Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    13

    Unanswered: Access and VBA for only error checking?

    Hi all,

    Sorry to keep bugging everyone with my newbie questions, but I was just wondering if it was possible to only use the vba within access to check for errors?

    For instance, if I have an Add button, I am happy with the default functionality with the exception that it doesn't check for duplicates. Can I simply add code in VBA for the 'Add' button to check for duplicates and cancel the add when an error occurs (duplicate record)? If so how is this done? I have an idea of how to check duplicates, but not how to cancel an Add.

    Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. You can check for duplicates a couple of different ways.

    1. Write a function to return a true or false if a dup record exists.

    2. Use the dlookup function to see if a dup record exists.


    Example of a simple function to return true or false if a dup exists (in ADO) - passing the full name (1 field) to the function (excuse any syntax errors - for example only):

    Function IsDupRec(varName as variant) as boolean
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTable where FullName = '" & varName & "'"
    rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
    if rs.eof and rs.bof then
    IsDupRec = false
    else
    IsDupRec = true
    msgbox "There is a dup record."
    end if
    rs.close
    set rs = nothing
    End Function

    Then you'd call the function like this:
    Refresh (to refresh data entry)
    if IsDupRec(me!FullName) = true then
    ....
    else
    ....
    end if

    Note: use msgbox to help you show what values are passed/returned or watch values passed in debug mode.
    Last edited by pkstormy; 01-19-10 at 22:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •