Results 1 to 6 of 6
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Before Insert to check on if all ready exist?

    Hi all,
    I have a continous form with two combo on it that use an after update SQL Insert to insert into a many to many relationship table.
    I am trying to make this code work for a before insert event to check if the two selected values of the combo boxes are all ready in the
    table and if they are to canel, msgbox, and exit so not to be able to add the same record. I have spent all day on this code and cannot
    get it to work with the combo boxes? I have similar code in for some text boxes on different form which works well so would be really


    great if someone could tell me what I am doing wrong.

    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
        Dim CY As String
        Dim CT As String
        Dim stLinkCriteria As String
        Dim rsc As DAO.Recordset
        Dim CYkey As Long
        Dim CTkey As Long
     
        Set rsc = Me.RecordsetClone
        CY = (Me.CboCompany)
        CT = (Me.CboCompanyType)
        CYkey = Nz(DLookup("CompanyID", "Company2CompanyTypeTbl", "CompanyID=" & CY), 0)
        CTkey = Nz(DLookup("CompanyTypeID", "Company2CompanyTypeTbl", "CompanyTypeID=" & CT), 0)
        
        
        'both exist and are in same existing record
        If CYkey > 0 And CYkey = CTkey Then
            Me.Undo
            'Cancel = True
            MsgBox "Warning!  Company Name of " _
            & CY & " and the Company Type " & CT & " are already in Database." _
            & vbCr & vbCr & "You need to choose a diffeerent Company Type.", _
            vbInformation, "Duplicate Information"
            Exit Sub
        End If
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What does 'not work' mean - error message, wrong result, nothing happens?

    You are comparing CY with CompanyID (a number ?) but according to the MsgBox CY has company name (text ?). Exactly what is value of CboCompany?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sumosoftware is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    1
    You logic looks a little weird, though it may be correct.

    If CYkey > 0 And CYkey = CTkey
    is basically saying
    if the CompanyID selected is greater than 0, And the CompanyID selected equals the CompanyTypeID selected.

    what about checking duplicates by
    If CYkey > 0 And CTkey > 0

    Another way you could check is something like this, which runs 1 query to db.

    dim rs as dao.recordset
    set rs = currentDb.OpenRecordset("Select 1 from Company2CompanyTypeTbl where [CompanyID]=" & CY & " and [CompanyTypeID]=" & CT)
    ' check if duplicate exists
    if rs.recordCount > 0 then
    'your duplicate logic here
    else
    ' your non duplicate logic here
    end if

    ' clear memory
    set rs = nothing

    pls excuse any mistakes, i've typed code directly without checking.
    I hope this helps.

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Before Insert to check on if all ready exist?

    Company Test.zip

    Hi June7
    Sorry for confusion! Not work means it didn't stop from duplicating records. The CompanyID CY is a number I believe? Its a combo with CompanyID and CompanyName, Same with CT
    I attached the db if that helps?
    Thanks
    Dave

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Why not just do the validation test in the forms Before Update event with perhaps DCount(). Then the update can be cancelled if DCount() returns a value >0
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks
    I got the following to work!
    Wow what a day....

    Code:
    Option Compare Database
    Option Explicit
    Private Sub CboCompanyType_BeforeUpdate(Cancel As Integer)
        Dim CY As String
        Dim CT As String
        Dim stLinkCriteria As String
        Dim rs As DAO.Recordset
        
        CY = (Me.CboCompany)
        CT = (Me.CboCompanyType)
        Set rs = CurrentDb.OpenRecordset("Select 1 from Company2CompanyTypeTbl where [CompanyID]=" & CY & " and [CompanyTypeID]=" & CT)
        'both exist and are in same existing record
        If rs.RecordCount > 0 Then
            Me.Undo
            'Cancel = True
            MsgBox "Warning!  Company Type is already in Database for this Company." _
            & vbCr & vbCr & "Please choose a diffeerent Company Type.", _
            vbInformation, "Duplicate Information"
            Exit Sub
        End If
    End Sub

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

Similar Threads

  1. Replies: 5
    Last Post: 04-25-2017, 08:48 AM
  2. check if column exist
    By xopherira in forum Modules
    Replies: 5
    Last Post: 08-25-2015, 02:09 PM
  3. Check if record exist
    By sahand in forum Forms
    Replies: 2
    Last Post: 06-11-2014, 07:07 AM
  4. Replies: 4
    Last Post: 06-21-2012, 05:39 PM
  5. Check if record exist
    By khhess in forum Programming
    Replies: 1
    Last Post: 06-14-2011, 06:56 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