Results 1 to 4 of 4
  1. #1
    Marlene23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    14

    Prevent lines with duplicate entries across 3 fields

    Good Day

    I have a challenge and tried a few things but cannot get the end result I am looking for. Hoping someone can assist and give guidance.

    I have 3 tables:



    Main Table with the following fields: Financial Year, Financial Period, Date, Amount

    Lookup Table for Financial Year with the fields: FY2018, FY2019, etc.
    Lookup Table for Financial Periods with the fields: P1, P2, P3 ....., P12

    The Main Table is linked to my Companies Table - There are multiple companies and every month the new Amount must be entered for that specific Financial Year and Period.

    The Companie table sits on my Companies form with a Subform which is the Main Table referred to above.

    How can I prevent the user from entering rows with duplicate data across fields Financial Year, Period and Date? Example:

    Financial Year Period Date Amount
    FY2019 P1 1 Oct 2018 X
    FY2019 P2 1 Nov 2018 X
    FY2019 P3 1 Dec 2018 X
    FY2019 P3 1 Dec 2018 X - Duplicate Line

    Any assistance and or guidance will be greatly appreciated!
    Thank you in advance!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if you want to check before the user tries to update, depends on the order in which users enter data, but assuming date is the last entry you could put something like this in the date beforeupdate event

    Code:
    if dcount("*","MainTable","FYear='" & me.FYear & "' AND Period='" & me.period & "' AND FDate=#" & format(me.Fdate,"mm/dd/yyyy") & "#")<>0 then
      msgbox "This date has already been entered for this year/period, enter a different one"
      cancel=true
    end if
    Note Date is a reserved word and should not be used as a field name

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Marlene, in your case, I would give to the user the opportunity to change the amount, if it differs from the saved.
    In an unbound form, with the corresponding fields and a button called "cmdSubmit", there could be a procedure like this:
    Code:
    Private Sub cmdSubmit_Click()
        Dim strMsg As String
        Dim rs As Recordset
    
        'At this point need a validation code for properly filled fields of the form."
        'For example:
        'If Not IsReadyToSave then '"IsReadyToSave" could be a user defined function for required fields validation.
            'MsgBox "You have to fill properly all required fields!", vbExclamation, "Financial Record"
            'Exit Sub
        'End If
        On Error GoTo ErrH
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM MainTable " _
                                         & "WHERE FYear='" & Me.FYear & "' AND FPeriod='" & Me.FPeriod _
                                         & "' AND CLng(FDate)=" & CLng(Me.FDate), dbOpenDynaset)
        With rs
            If (.BOF And .EOF) Then
                'New record.
                .AddNew
                !FYear = Me.FYear
                !FPeriod = Me.FPeriod
                !FDate = Me.FDate
                !FAmount = Me.FAmount
                .Update
                MsgBox "Success!" & vbCrLf & vbCrLf & "You have a new Financial Record!", vbInformation, "New Financial Record"
            Else
                'Record exist.
                If !FAmount = Me.FAmount Then
                    'All fields have the same values.
                    MsgBox "This record already exists.", vbInformation, "Financial Records"
                Else
                    'There is record with only a different amount. Give to user the chance to update the amount."
                    strMsg = "A record with the same date properties and an amount of " _
                             & Format(!FAmount, "Currency") & " already exists." & vbCrLf & vbCrLf _
                             & "Whould you like to update the amount to " & Format(Me.FAmount, "Currency") & "?"
                    'Ask the user for the update.
                    If MsgBox(strMsg, vbQuestion + vbYesNoCancel + vbDefaultButton2, "Update Financial Record") = vbYes Then
                        .Edit
                        !FAmount = Me.FAmount
                        .Update
                        MsgBox "Financial Record updated successfully!", vbInformation, "Update Financial Record"
                    End If
                End If
            End If
        End With
        'At this point prepare the form for the next data entry. Clear contents of fields etc.
    ExitHere:
        On Error Resume Next
        rs.Close
        Set rs = Nothing
        Exit Sub
    ErrH:
        MsgBox "Error: " & Err & vbCrLf & vbCrLf & Err.Description, vbExclamation, "Financial Record"
        Resume ExitHere
    End Sub
    Hope helps.

    Cheers,
    John

    PS.: I don't know if the & "' AND CLng(FDate)=" & CLng(Me.FDate) works properly in your system(s) as it works in mine.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Prevent Duplicate Entries
    By Abhijeet in forum Forms
    Replies: 2
    Last Post: 08-23-2015, 12:15 AM
  2. Prevent duplicate entries in linked table
    By arothacker in forum Forms
    Replies: 5
    Last Post: 02-11-2014, 11:08 PM
  3. Replies: 19
    Last Post: 04-05-2013, 01:28 PM
  4. Problem with code to prevent duplicate entries
    By ResearchRN in forum Programming
    Replies: 5
    Last Post: 11-08-2011, 12:12 PM
  5. Prevent Duplicate Values on Combination of Two Fields
    By LornaM in forum Database Design
    Replies: 8
    Last Post: 05-05-2009, 11:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums