Results 1 to 6 of 6

Thread: VBA Events

  1. #1
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Events

    I am a real VBA newbie! I am writing a small application containing several tables and forms. For one form in particular, I want to prevent the creation of duplicate records. I have coded a dlookup command in the "Before Update" event for a combo box control. This is working correctly. My problem is that even though I am setting cancel = -1 when I detect that a record has been found via the dlookup, I can't seem to cancel the insertion of the record.

    Can anyone point me in the direction of a good web resource that discusses all VBA events and the order in which they are triggered? I really need to read up on this to understand fully. Any suggestions to cancel the insertion of the duplicate record would also be greatly appreciated.

    Thanks, in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Events

    This belongs here in Access if that's what you're using, since you don't actually have tables in the database sense of the word unless you're working with a database product. If you're using Access rather than SQL Server for your tables, just create a unique index in the table and include the field or fields that you want to occur no more than once in the table. You don't have to write any code to do that.

    As for why your code isn't working, you'll have to post it so someone can tell you.
    Charlotte

  3. #3
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Events

    Charlotte:

    Thanks for the reply.

    I had initially done that but couldn't figure out how to handle the resulting error programatically. I would like to display my own message box. Because I couldn't figure out how to trap the particular error code, I finally ended up writing my own code.

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Events

    Generally, what you do is trap the error in code, either by using an On Error Goto labelname at the top of your routine (where labelname is the name of the err handler label, or by using an On Error Resume Next. In either case, you have to test the err value to see what error, if any, you're handling and based on that value, you return your own message to the user.
    Charlotte

  5. #5
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Events

    Thanks again for the reply.

    I tried the suggested code but it does not seem to trap a runtime error 3022. Perhaps I inserted it in the wrong event. Do you have any further suggestions on how I can trap this error?

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Events

    Post the code you're working with. Otherwise, no one has any idea of what might to triggering the error. Where is the new record being inserted and how? A record isn't saved (thus triggering the error) in the beforeupdate event of a control, so you'll have to post your code to get any useful suggestions. Error 3022 will trap as well as any other error, so the problem has to be in where or when you're trying to do it.
    Charlotte

Posting Permissions

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