Access 2003 duplicate record in subform want to autodelete and let user know

Posted on 2011-04-21
Last Modified: 2012-05-11
I have a subform on a form. There are two fields that show on the form.

The first field is a Main Account number and what this "Main" account number listed only once. If the user types in this main account number again on the subform i want a pop up to tell them this is a duplicate and delete it.
Question by:Pdeters

    Author Comment

    I want to exit the field without saving
    LVL 119

    Expert Comment

    by:Rey Obrero
    try this in the beforeupdate event of the control for main account (txtAccount in this code)

    private sub txtaccount_beforeUpdate(cancel as integer)

    if dcount("*","tablesubform","accountnumber=" & me.txtaccount)>0 then
      msgbox "duplicate account"
    end if

    end sub

    Author Comment

    Is there a way to not save the record and jump back to the previous record in the subform?
    LVL 119

    Accepted Solution

    yes, that can be done

    me.undo  'will cancel the entered items in the form

    to go to the previous record, you need more vba codes.
    * you can use the FindFirst command to goto to the previous record


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now