Results 1 to 14 of 14
  1. #1
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58

    Goto Record when Duplicate Record Exists

    Currently have a database with 3 fields set as primary key so that duplicates will not be saved. I also inserted an On error message below to display an error message. The final step would be to take the user to the already existing record. Any ideas on how to accomplish this? Is there some code that I could add to the on error message to take the use to the already existing record. The three fields are Loan_Number, Investor_Number and Request_Type. Thanks for the help.



    Code:
    Err_Save_Record_Click:
    Select Case Err
    Case 3022
    MsgBox "A record with this Loan Number already exists.", vbOKOnly, "Record Exists"
       
    Case Else
     
     
    Exit Sub
    End Select

  2. #2
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    I found this link, http://msdn.microsoft.com/en-us/libr...ffice.12).aspx



    Example
    The following example uses the GoToRecord method to make the seventh record in the form Employees current
    Code:
     DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7

    So if this was adjusted and put into the IF statement, it might work.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    redbull, that won't work because the Offset argument is an unknown. Code to determine the offset is probably more complicated than other options.

    Other options:

    1. apply a filter to the form recordset
    Me.FilterOn = False
    Me.Filter = construct filter criteria with the 3 inputs
    Me.FilterOn = True

    2. use recordset clone method to move to record - example
    Code:
    Private Sub tbxLabNum_BeforeUpdate(Cancel As Integer)
    With Me.RecordsetClone
    .FindFirst "LabNum='" & Me.tbxLABNUM & "'"
    If Not .NoMatch Then
       Cancel = True
       If MsgBox("Lab Number exists. Do you want to view record?", vbYesNo) = vbYes Then Me.Bookmark = .Bookmark
    End If
    End With
    End Sub
    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.

  4. #4
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58
    how would I apply the clone method if I want three field conditions to match before a record is duplicate. For example, Investor Loan, Request Type and Loan Number should all match an existing record and then be determined as duplicate? Currently, I set the 3 fields as a primary key to prevent duplicates and then put an on error message. I am not sure how to structure the clone method with 3 field conditions. Thanks for the help.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Having 3 values to match does complicate. Figuring out the event to use for triggering the match search is the trick. Could force user to input value in each field in 1 2 3 order by starting with only the Loan_number textbox available. After they make entry the next box is enabled and so on. Then in the BeforeUpdate of the 3rd control, run code. The WHERE argument of the FindFirst can have as many criteria as you want. Would have to not only Cancel that last entry but also undo the entire record initialization. I don't have any compound keys (avoid like the plague) so not an issue I have had to deal with.

    Or have procedure called by each textbox. Include condition if each field has data then run search.

    Another approach is to set up a combobox that displays the 3 fields. User can select from existing records. If the combination not presented in the list, offer button to 'Add New Record' or use the NotInList event.
    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.

  6. #6
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58
    Thanks. The code below is what I have so far as a Save command Button. It forces the user to enter the Loan Number and Investor Number which are the criteria. The third is a list box. Even if these two criteria are met for the duplicate record it would be fine. How would I insert the clone method into this code which generates on a save command click. Thanks for the help in advance.


    Code:
    Private Sub Save_Record_Click()
     
    Dim SQL As String
     
     
    If IsNull(Me!LoanNumber.Value) Then
              MsgBox "Please Enter Loan Number", vbOKOnly, "Required Field Missing"
              Cancel = True
              GoTo Err_Save_Record_Click
           End If
          
     If IsNull(Me!InvestorLoanNumber.Value) Then
              MsgBox "Please Enter Investor Loan Number", vbOKOnly, "Required Field Missing"
              Cancel = True
              GoTo Err_Save_Record_Click
           End If
          
     If IsNull(Me!LoanBalance.Value) Then
              MsgBox "Please Enter Current Principal Balance Amount", vbOKOnly, "Required Field Missing"
              Cancel = True
              GoTo Err_Save_Record_Click
           End If
          
      If IsNull(Me!OrigDate.Value) Then
              MsgBox "Please Enter Origination Date", vbOKOnly, "Required Field Missing"
              Cancel = True
              GoTo Err_Save_Record_Click
           End If
          
       If IsNull(Me!CustomerName.Value) Then
              MsgBox "Please Enter Customer Name", vbOKOnly, "Required Field Missing"
              Cancel = True
              GoTo Err_Save_Record_Click
           End If
    SQL = "Update Investor_Request set Status = 'Closed' where Loan_Number = " & Me![Loan Number] & _
      " AND status = 'Outstanding'"
     
     
    CurrentDb.Execute SQL, dbFailOnError
     
          
       
        On Error GoTo Err_Save_Record_Click
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
     
    Err_Save_Record_Click:
    Select Case Err
    Case 3022
    MsgBox "A record with this Loan Number already exists.", vbOKOnly, "Record Exists"
       
    Case Else
     
     
    Exit Sub
    End Select
    End Sub

  7. #7
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58
    I guess another way to ask the question is, how Would I add another field criteria to this and then place in wither the before update of the second field or in the save command click. I am still confused on how to structure the clone method for the multiple field criteria. Right now there is code that prompts the use to enter data in the first criteria field and the second criteria field. Now I would just need to add the clone method code to either the before update event of the second field criteria or the save command click event and that is where I am running into the issue. Thanks for the help.

    Code:
        Dim SID As String
        Dim stLinkCriteria As String
        Dim rsc As DAO.Recordset
     
        Set rsc = Me.RecordsetClone
     
        SID = Me.strStudentNumber.Value
        stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"
     
        'Check StudentDetails table for duplicate StudentNumber
        If DCount("strStudentNumber", "tblStudentDetails", _
                  stLinkCriteria) > 0 Then
            'Undo duplicate entry
            Me.Undo
            'Message box warning of duplication
            MsgBox "Warning Student Number " _
                 & SID & " has already been entered." _
                 & vbCr & vbCr & "You will now been taken to the record.", _
                   vbInformation, "Duplicate Information"
            'Go to record of original Student Number
            rsc.FindFirst stLinkCriteria
            Me.Bookmark = rsc.Bookmark
        End If
     
        Set rsc = Nothing
    End Sub

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So Investor_Number is InvestorLoanNumber and Request_Type is not required? Then why is Request_Type in the key?

    Put the new code after the InvestorLoanNumber. Actually I would make the LoanNumber, InvestorLoanNumber, and the search a single If Then ElseIf sequence.

    How much data does user enter before clicking 'Save'? Is checking for existing record after a lot of data entry just going to aggravate user?

    Why does user enter customer name and not a customerID? Do you not have repeat customers?
    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.

  9. #9
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58
    thanks for the input. Only InvestorNumber and InvestorLoanNumber are the fields that must not be duplicated. I was thinking of putting something along the lines of the below code in the before update event of the InvestorLoanNumber field or in the save event. Do you think something like this would would

    Code:
        Dim SID As String
        Dim SID1 As String
        Dim stLinkCriteria As String
        Dim stLinkCriteria1 As String
        Dim rsc As DAO.Recordset
     
        Set rsc = Me.RecordsetClone
     
        SID = Me.HarrisLoanNumber.Value
        SID1 = Me.InvestorLoanNumber.Value
     
        stLinkCriteria = "[HarrisLoanNumber]=" & "'" & SID & "'"
        stLinkCriteria1 = "[InvestorLoanNumber]=" & "'" & SID1 & "'"
     
     
        If DCount("HarrisLoanNumber", "Repurchase", _
                  stLinkCriteria) > 0
     
    And
     
        If DCount("InvestorLoanNumber", "Repurchase", _
                  stLinkCriteria1) > 0
     
     
     Then
           
            Me.Undo
           
            MsgBox "Warning Harris Loan Number " _
                 & SID & " has already been entered." _
                   Investor Loan Number " _
                 & SID1 & " has already been entered." _
                 & vbCr & vbCr & "You will now been taken to the record.", _
                   vbInformation, "Duplicate Information"
     
            rsc.FindFirst stLinkCriteria
            Me.Bookmark = rsc.Bookmark
        End If
     
        Set rsc = Nothing
    End Sub

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is HarrisLoanNumber, first mention of that name.

    rsc.FindFirst "[HarrisLoanNumber]='" & SID & "' AND [InvestorLoanNumber]='" & SID1 & "'"

    You were already familiar with RecordsetClone? As demonstrated by my example, it is not necessary to set up a recordset. I use form method approach for these simple searches.
    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.

  11. #11
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58
    this record clone code was giving me errors. I am pretty sure I entered everything correctly but not sure why
    Any ideas?

    Code:
    Private Sub LoanNumber_BeforeUpdate(Cancel As Integer)
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    SID = Me.LoanNumber.Value
    stLinkCriteria = "[LoanNumber]=" & "'" & SID & "'"
    
        If DCount("LoanNumber", "Repurchase", stLinkCriteria) > 0 Then
        
            Me.Undo
            MsgBox "Loan Number " _
            & SID & " has already been entered." _
            & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
            , "Duplicate Information"
        
            rsc.FindFirst stLinkCriteria
            Me.Bookmark = rsc.Bookmark
        End If
    Set rsc = Nothing
    End Sub

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What errors - error message, wrong result, nothing happens?

    What happens if FindFirst does not find match.

    Need to test for match. Review my suggested code again. It uses NoMatch property of the RecordsetClone.

    Step debug.
    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.

  13. #13
    rlsublime is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    58
    Basically. this code is working properly but I am getting an error message when the field is blank. Is there a way to just exit the sub when the user enters nothing, right now it is generating an error when blank

    where would i insert the code

    Code:
    Private Sub Investor_Loan_Number_BeforeUpdate(Cancel As Integer)
    
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    SID = Me.Investor_Loan_Number.Value
    stLinkCriteria = "[Investor_Loan_Number]=" & "'" & SID & "'"
        If DCount("Investor_Loan_Number", "Investor_Request", stLinkCriteria) > 0 Then
        
            Me.Undo
            MsgBox "Investor Loan Number " _
            & SID & " has already been entered." _
            & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
            , "Duplicate Information"
        
            rsc.FindFirst stLinkCriteria
            Me.Bookmark = rsc.Bookmark
        End If
    Set rsc = Nothing
    
    End Sub

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    From my post #5 "Include condition if each field has data then run search."

    If Not IsNull(Me.LoanNumber) And Not IsNull(Me.InvestorLoanNumber) Then
    'do search
    End If
    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.

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

Similar Threads

  1. Goto Record
    By jgalloway in forum Forms
    Replies: 8
    Last Post: 09-25-2011, 08:03 AM
  2. Goto record in subform - sometimes
    By RasterImage in forum Forms
    Replies: 6
    Last Post: 09-13-2011, 04:36 PM
  3. list box goto record issue
    By Madmax in forum Access
    Replies: 2
    Last Post: 07-07-2011, 06:17 AM
  4. Replies: 1
    Last Post: 06-01-2011, 06:44 PM
  5. Goto a new record in tabbed subform
    By snoopy2003 in forum Programming
    Replies: 3
    Last Post: 03-05-2011, 04:24 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