Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Validation problem (A97)

    Facts:
    Table: tblCostCodes
    Fields:
    CostCodeId (AutoNum) (Key)
    CompCode (Text 4)
    AcctCode (Text 5)
    CostCentre (Text 6)
    JobNumber (Text 7)

    The current rule should be that the combination of all the above fields (excluding CostCodeId) is unique.

    Also, where there is a CostCentre, there is no JobNumber and vice versa. (This validation rule works fine)

    However, I have been trying to create a validation Rule to prevent a duplication of the four parts of the code. I can't combine them to create a unique key, because either CostCentre or JobNumber can contain nulls which is not allowed. I also can't set each Index property to "Yes (No Duplicates)" because of the duplication of Null fields.

    Can anyone suggest a Validation rule or even a VBA solution, that would prevent the user from entering a duplicate entry into the table.

    TIA

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

    Re: Validation problem (A97)

    You're allowed to have nulls in unique keys, just not in primary keys. The catch is that if you allow nulls, there is no way to automatically keep out duplicates where the same field is null and all other values are the same.

    Your best bet is to do the validation in a form rather than at the table level. That would permit you to use the BeforeUpdate event of the form to test for another record already in the table with all other values the same and a null in the pertinent field.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Validation problem (A97)

    Hmm that sounds as though it would work. Just one question though, to test validation from the form, would you use something like DLookup and use a number of '&' to string the text together for comparrison or is there an easier way.?

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

    Re: Validation problem (A97)

    I would use DCount, creating a Where string something like this:

    <pre>Private Function BuildWhere() As String
    Dim strWhere As String
    Dim intLoop as Integer
    Dim strField As String
    Dim txt As TextBox

    For intLoop = 1 to 4
    strField = Choose(intLoop, "CompCode", "AcctCode", "CostCentre", "JobNumber")
    Set txt = me(strField)
    If Len(txt & "") > 0 Then
    strWhere = strWhere & "[" & strField & "]='" & txt.Value & "' "
    Else
    strWhere = strWhere & "[" & strField & "] Is Null "
    End If

    If intLoop < 4 Then strWhere = strWhere & "AND "
    Next intLoop

    BuildWhere = strWhere
    End Function</pre>


    Then I would use it like this in the BeforeUpdate event:

    Dim strWhere As String

    strWhere = BuildWhere()
    If Dcount("*","tblCostCodes", strWhere) <> 0 Then
    Cancel = True
    MsgBox "This record already exists!"
    ... etc.

    Edited to correct Choose() syntax and clarify use of BuildWhere() - Charlotte
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Validation problem (A97)

    Brilliant! This works great.

    I have sung your praises here in this small government department in Sydney. You will be very welcome here anytime you care to visit.

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

    Re: Validation problem (A97)

    I've always wanted to visit Oz. Maybe I should look for consulting work Down Under! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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
  •