Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Unanswered: do not save until fields are unique

    I have a form with a button that copies data on main form and sub forms
    and enters this into the tables. I used the instructions from the link
    below and it works great!!
    http://support.microsoft.com/kb/208824

    I have 2 tables and a form with a sub form.


    Table1 " tCustImpacts" has fields ...
    CustImpactID - Autonumber - common to Table2
    KPIProjID - Number
    KPIPMID - Number
    ReportDTID - Number
    Scope - Memo
    Invoicing - Text
    Table1 "tCustImpacts" is used for the main form.


    Table2 "tCustImpactsDetails" has fields...
    DetailsID - Autonumber
    CustImpactsID - Number - common to Table1
    CustImpactCatID - Number
    CustImpactDetails - Memo
    Table2 "tCustImpactsDetails" is used for the sub form


    When the save button is clicked I need to NOT save if there are records
    that already exist in Table1 that have the common fields of
    KPIProjID - Number
    KPIPMID - Number
    ReportDTID - Number


    The problem comes is that this allows duplicate data.
    I need the KPIProjID , KPIPMID and ReportDTID to be a unique record set
    and not allow the tables to copy until the ReportDTID is changed to a
    new date.


    How can I block the SAVE function if there is a duplicate recordset
    until the ReportDTID is changed (which creates a unique recordset)?


    KPIPMID KPIProjID ReportDTID Scope
    Scott (1) Dallas (32) Jun06(6) no
    Dave (2) Houston (49) Jul06 (7) no
    Ben (3) Austin (74) Jul06 (7) yes
    Scott(1) Dallas(32) Jul06 (7) yes


    Therefore the record cannot be saved because this data (KPIPMID,
    KPIProjID and ReportDTID, (Scope and other fields are not part of the
    criteria)) already exists in table.
    I need the record to not be saved and a message to change the report
    date.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could have a go with DCount() and pass in your current criteria to see if anything already exists.

    Alternately you could define each column as UNIQUE using table constraints...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a go at something like this...
    - Use an SQL statment to see fi there are any results that match the criteria you're trying to save
    - Test if there are any values in the array of results (.EOF || .BOF)

    Code:
    Dim var As Array
    Dim SQL As String
    
    SQL = "SELECT * " &_
             "FROM [table1] " &_
             "WHERE [table1].[field1] = [ThingToTest]" &_
             "AND ....repeat
    
    var.Open SQL
    
    If var.EOF Then
        perform your code
    End If
    I'm probably way off here but it's a start!
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually - run with Teddies idea - much simpler!
    Just fall back on mine if all else fails :P

    (I had a problem similar to this today when debugging an asp webpage so I just fiddled with what already exists and tested the arrays for values...
    not important jsut explains where my long winded idea came from :P)
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2003
    Posts
    8
    Teddy,

    I am not to great at coding.

    Can you give me an run at the dcount example?

Posting Permissions

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