Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: confusing error handling problems

    i have... the most interesting problem, and I can not figure out why my code is DOING THIS. I'm trying not to freak out here...

    I have some code in one of my data entry forms to check the entry to make sure it is not a duplicate before entering the information.

    Observe a snippet of my code:

    Dim Count As Single
    Dim err As Boolean

    'I have assigned the value of the Me.txtEmpID text box to Provider already
    Count = DCount("intEmpID", "tblContractors", intEmpID = Provider)

    If Count > 0 Then
    MsgBox "A record for the employee Id you have entered already esists.", vbCritical, "Duplicate Entry"
    Me.txtEmpID.SetFocus
    err = True
    End If

    If Not Err Then

    <insert a bunch of lines of code that enters the new information to tblContractors here>

    End If

    My problem is, when I test this, I enter an employee Id value that I of course know is already in the table. the FIRST time I enter this value, I get some cockamamie message from VB telling me about duplicate records, or for some reason a related record is required int his other table, yada yada.

    so, I hit END as opposed to DEBUG and it just takes me right on back to the form, where all of my data is still the same.

    i press enter again.

    Voila, the error message I programmed comes up and tells me the id already exists, I click okay, it sets the focus back to txtEmpID, and the program skips the execution of the insert statements.

    WHY is it that the code runs properly the second time I hit enter on the form and it doesn't work the first time!?!?

  2. #2
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Just a little note...

    I fixed it!

    hooray for break points.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  3. #3
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    It sounds like you're using a bound form and your table does not allow duplicates on this field and has referential integrity enforced on a relationship with another table. Your post does not indicate when this code is running. It is most likely running after access tries to write the record rather than before. If this is the case, Access would come across the table level validation prior to running your code.

  4. #4
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Lightbulb

    Quote Originally Posted by rogue
    It sounds like you're using a bound form and your table does not allow duplicates on this field and has referential integrity enforced on a relationship with another table. Your post does not indicate when this code is running. It is most likely running after access tries to write the record rather than before. If this is the case, Access would come across the table level validation prior to running your code.
    i'm sorry I wasn't specific.

    This code executes within the onclick event for the "enter" command button, along with some other code that sets a boolean error flag to True if any of the necessary fields are blank.

    The code to write the new record only occurs if the error flag is False. and no this table does not have referential integrity enforced on any other relationships, no, I'm not using a bound form, and yes, there can not be duplicates inside the primary key field. That's the point of this piece of code. To keep it from trying to write the record if the employee ID already exists in the table.

    My problem was actually a problem with the variables, where they were declared, and the values being used to evaluate the DCount() function. I actually solved the problem shortly after making the original post.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

Posting Permissions

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