Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Help with duplicate key

    Hello..
    I'm having a little problem with the attached code. Frequently, but not always, Access returns a validation rule error when saving a record. I can click through the error screens and it saves the record fine. For some records I use the AutoGenerate number, and for other records I use a unique assigned number. (the unique assigned number comes from another system and must be used for reference) Now I know that on occasion the Autogenerated number may generate a duplicate number, I know that and simply change the auto number by 1 or so and that works fine. (why do I do it that way... because I needed to generate a number and that was the only way I knew to do it - AutoNumber is not an option because I need to use the unique number mentioned above at times)

    The field name is CaseNumber, set to text, required = yes, allow zero length = no, and Indexed = yes(no duplictes). Additionally I have no validation properties in either the Validation Rule or text properties of the CaseNumber control, nor do I have any validation info in the table/field properties of CaseNumber.

    When the error occurs the MsgBox fires and I click through it, then an Access error box appears stating "The value in the field or record violates the validation rule for the record or field". I click through that also and can continue down the form and finally file the record when completed.

    Any thoughts?

    Thanks...
    Larry
    _________________________________________________


    Private Sub CaseNumber_BeforeUpdate(Cancel As Integer)


    'Check for duplicate case number
    If DCount("[CaseNumber]", "tbl_DisputeCases") <> 0 Then
    'Case Number exists, choose another number

    Cancel = True
    MsgBox "Case Number already on file, please choose another number" Me.CaseNumber.Undo

    Else
    Cancel = False

    End If

    End Sub
    __________________________________________________ _______________________________________

    Private Sub cmd_AutoGenerate_Click()

    Dim MyValue
    Randomize 'Initialize Random Number generator

    MyValue = Int((100000 * Rnd) + 1) 'Generate random value between 1 and 100000

    MyValue = "A" & right("000000" & CDbl((100000 * Rnd())), 6)

    'Place number in form

    Me.CaseNumber = MyValue
    Me.Agent.SetFocus


    End Sub

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Try changing the order in the sub

    If DCount("[CaseNumber]", "tbl_DisputeCases") =1 Then
    'Case Number exists, choose another number

    MsgBox "Case Number already on file, please choose another number"
    Me.Undo
    Cancel = True
    End If

  3. #3
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks DJN... I'll give it a try.

    Larry

  4. #4
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello DJN;

    Unfortunately that didn't work. I tried several variations but no luck. Any other thoughts?

    Larry

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    The way your code is now, your DCount function will simply return the total number of records that contains a value in the CaseNumber field located in the tbl_DisputeCases table. You will need to be a little more specific with this function and supply some criteria to compare to. For example:

    Code:
    Private Sub CaseNumber_BeforeUpdate(Cancel As Integer)
       'Check for duplicate case number
       If DCount("[CaseNumber]", "tbl_DisputeCases", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then
          'Case Number exists, choose another number
          Cancel = True
          MsgBox "Case Number already on file, please choose another number"
          Me.CaseNumber.Undo
       End If
    End Sub
    Or you can have things auto generate a number automatically for you....perhaps like this:

    Code:
    Private Sub CaseNumber_BeforeUpdate(Cancel As Integer)
    Recheck:
       'Check for duplicate case number
       If DCount("[CaseNumber]", "tbl_DisputeCases", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then
          'Case Number exists, choose another number
           MsgBox "Case Number already on file, please choose another number"
          Me.CaseNumber = CStr(Val(Me.CaseNumber) + 1)  'Assuming the CaseNumber contains no Alpha characters.
          Call cmd_AutoGenerate_Click
          Goto Recheck
       End If
    End Sub
    or sumtin like dat....

    .
    Last edited by CyberLynx; 03-10-06 at 21:57.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello CyberLynx;

    Thank you for the reply, and code. I pasted the code below into the BeforeUpdate function and it returned a syntax error in the line
    If DCount("[CaseNumber]", "tbl_DisputeCases", [CaseNumber]='" & Me.CaseNumber & "'") > 0 Then on load of the form. There is no code in the OnLoad or OnOpen function of the form. I was unable to determine the problem. Your thoughts??

    Thanks..

    Larry

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    missing "
    If DCount("[CaseNumber]", "tbl_DisputeCases", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then


    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Whoops....thanks Izy.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  9. #9
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks guys... that worked great. BTW... did someone go back in and change the first post of the code, because its exactly like corrected code - or am I going nuts?

    Also, its not important, but the second piece of code with the "Recheck:" in it, did not work. It returned a runtime error of "The BeforeUpdate or Validation Rule property for this field is preventing the database from saving the data in the field". The line causing the error was shown to be "Me.CaseNumber = ...". I can't use that function in this instance because if the number is found to be duplicated, it must have been the result of operator entry error because the system generating that number will never duplicate a number, and adding 1 to it would render the record to be out of sync with the independant database generating the number. However it appears to be a useful piece of code in other areas if I can get it working.

    You mention in the comments that 'Assuming the CaseNumber contains no Alpha characters. You may notice that in my random generated number routine that I use in another function, it utilizes a leading "A". Does that count as a Alpha number? Assuming so, what would need to be done with the Recheck code to adapt it to work with the Alpha number?

    Thanks...

    Larry

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmmm i'm slightly lost here

    'A' is not a number
    isnumeric("A12345") = False
    ...so it's string

    the line
    If DCount("[CaseNumber]", "tbl_DisputeCases", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then
    is treating CaseNumber as string ... those two ' do it.


    i didn't notice any "Recheck code"
    ...remind me - what was the question??

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello izyrider;

    The code below returns this error: "The BeforeUpdate or Validation Rule property for this field is preventing the database from saving the data in the field".
    The line causing the error was shown to be "Me.CaseNumber = ...".

    Private Sub CaseNumber_BeforeUpdate(Cancel As Integer)
    Recheck:
    'Check for duplicate case number
    If DCount("[CaseNumber]", "tbl_DisputeCases", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then
    'Case Number exists, choose another number
    MsgBox "Case Number already on file, please choose another number"
    Me.CaseNumber = CStr(Val(Me.CaseNumber) + 1) 'Assuming the CaseNumber contains no Alpha characters.
    Call cmd_AutoGenerate_Click
    Goto Recheck
    End If
    End Sub


    I'm not very good with VBA but it looks like if the CaseNumber is already on file, the code adds 1 to it and tries another check. I have two seperate routines, one uses strictly numeric and the other uses an alphanumeric (A1234)(completely different forms). The above code returns the error indicated in my last post. I was using it on the strictly numeric CaseNumber just to see if it worked. If it had I could use it elsewhere because I can not use it on the strictly numeric number due to not being able to simply add 1 to a number found to be on file already.

    Your post indicates what needs to be done to make it alphanumeric by way of telling me the "'" needs to come OUT for alphanumeric. However I don't think that will take care of the error its returning.

    Thanks...
    Larry

  12. #12
    Join Date
    Nov 2003
    Posts
    1,487

    Post

    Oh boy....to many long hours . Not very automatic with a Message box in it, is it. In any case....No Larry, you're not going crazy, I did edit my first post and no....I'm not gonna test the following code either...Heh, just like the last. (I thought there was a smilie in here that sticks its tougue out - What up with that Teddy?):

    Code:
    Private Sub CaseNumber_BeforeUpdate(Cancel As Integer)
    Recheck: 'Check for duplicate case number If DCount("[CaseNumber]", "tbl_DisputeCases", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then ' Obviously the Case Number already exists within the DB ' sooooo, now knowing that the Case Number does indeed ' contain AlphaNumeric characters (sorry I missed that) lets ' pull out all but the last character from the new case number ' and keep it as String. On the same stroke, we will take the ' last character of the case number (which is suppose to be ' numberal) and convert it from String to Number. We then ' add 1 to it, then convert IT back to string and put it ALL ' together to generate our new and hopefully unique Case Number. Me.CaseNumber = Left$(Me.CaseNumber,Len(Me.CaseNumber) - 1) & cstr(Val(Right$(Me.CaseNumber, 1)) + 1) ' Now lets run this mess through your Random Generator... Call cmd_AutoGenerate_Click ' OK...cool...Let's now see if that sucka exists in the DB as well... ' We go back to the top of this routine and check things again. Goto Recheck End If ' If not then allow the data End Sub
    The concern earlier about the Alpha characters thingy is because of the code used to put out a new case number...in particular, see the Val(Me.CaseNumber) in the code line? The Val function (which is coming obsolete nowadays) converts a string with numbers like "1234" to a number like 1234. If you throw, say the letter A into the mix, you're not going to get what you expect. What you can expect is 0. Not the sort of thing you want (I hope).

    This is going on far to long...it's starting to sound like a lot of Bull Sh#t.

    In any case...you'll probably have to play with the code and if all else fails...throw the code into the LostFocus event
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  13. #13
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thank you CyberLynx... I think I got the hint!

    Larry

  14. #14
    Join Date
    Nov 2003
    Posts
    1,487
    Quote Originally Posted by Larryg
    Thank you CyberLynx... I think I got the hint!

    Larry
    No Hint there Larry....to me, my last post looked like a lot of rambling. Hope you didn't take things the wrong way.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  15. #15
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello CyberLynx;

    Unfortunately, I think I did. My apologies if there was no intent. Chalk it up the limitations of the written word. This is a rough crowd at times and perhaps I jumped too fast to a conclusion based on my own insecurities of being a beginner at this. Something that seems so simple to an experienced programmer, is complicated to me right now, although not as complicated as when I first started thanks to this forum and others such as yourself who give of their time. I am truly appreciative of all the help I have received, and am sensitive to the generosity of those that take time away from their other interests to help me.

    Thnak you...
    Larry

Posting Permissions

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