Results 1 to 7 of 7
  1. #1
    margzj is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Dubai
    Posts
    41

    Allow duplicate entry but prompt user if he still wants to continue


    Hi I will like to allow duplicate entry but the user must choose if he still wants to continue.<br>
    <br>
    I would like something like this I got from online if the data is not in the list it will prompt to add the data.<br>
    <br>
    Code:
    <br>
    Private Sub CompanyInfo_ID_NotInList(NewData As String, Response As Integer)<br>
    On Error GoTo myError<br>
    &nbsp;&nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp;Dim rst As DAO.Recordset<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp;Set rst = CurrentDb.OpenRecordset("CompanyInfo", dbOpenDynaset)<br>
    &nbsp;&nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;If vbYes = MsgBox("Company name is not in the list. Do you wish to add " _<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&amp; NewData &amp; " as a new Company?", _<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;vbYesNo + vbInformation, _<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"New Company") Then<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rst.AddNew<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rst!CompanyName = NewData<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rst.Update<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Response = acDataErrAdded<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;Else<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Response = acDataErrContinue<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;End If<br>
    &nbsp;&nbsp; &nbsp; &nbsp; <br>
    leave:<br>
    <br>
    <br>
    &nbsp;&nbsp; &nbsp;If Not rst Is Nothing Then<br>
    &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;rst.Close: Set rst = Nothing<br>
    &nbsp;&nbsp; &nbsp;End If<br>
    &nbsp;&nbsp; &nbsp;<br>
    &nbsp;&nbsp; &nbsp;Exit Sub<br>
    &nbsp;&nbsp; &nbsp;<br>
    myError:<br>
    &nbsp;&nbsp; <br>
    &nbsp;&nbsp; &nbsp;MsgBox "Error " &amp; Err.Number &amp; ": " &amp; Error$<br>
    &nbsp;&nbsp; &nbsp;Resume leave<br>
    End Sub<br>
    <br>
    <br>
    <br>
    I would like to put It in the afterupdate.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What is wrong with leaving it in the NotInList Event?

  3. #3
    margzj is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Dubai
    Posts
    41
    That is a different code, that code is already working. But I want something like that, it will prompt the user if the data is duplicate and he will choose to continue with the duplicate data or not.

    Quote Originally Posted by RuralGuy View Post
    What is wrong with leaving it in the NotInList Event?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You will probably want to use the BeforeUpdate event of that control.

    Code:
    Private Sub CompanyInfo_ID_BeforeUpdate(Cancel As Integer)
       '-- Check if this is a duplicate
    
       With Me.RecordsetClone
          If .RecordCount Then  'If records exist
             .FindFirst "CompanyInfo_ ID= '" & Me.CompanyInfo_ID & "'"
             If Not .NoMatch Then
                If MsgBox("This ID is a Duplicate..." & vbCrLf & vbCrLf & _
                   "Do you want to add it anyway?", vbDefaultButton2 + vbYesNo + vbExclamation, "Duplicate Company") = vbNo Then
                   Cancel = True
                End If
             End If
          End If
       End With
    
    End Sub

  5. #5
    margzj is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Dubai
    Posts
    41
    Hi, It doesn't do anything, no error either.

    Quote Originally Posted by RuralGuy View Post
    You will probably want to use the BeforeUpdate event of that control.

    Code:
    Private Sub CompanyInfo_ID_BeforeUpdate(Cancel As Integer)
       '-- Check if this is a duplicate
    
       With Me.RecordsetClone
          If .RecordCount Then  'If records exist
             .FindFirst "CompanyInfo_ ID= '" & Me.CompanyInfo_ID & "'"
             If Not .NoMatch Then
                If MsgBox("This ID is a Duplicate..." & vbCrLf & vbCrLf & _
                   "Do you want to add it anyway?", vbDefaultButton2 + vbYesNo + vbExclamation, "Duplicate Company") = vbNo Then
                   Cancel = True
                End If
             End If
          End If
       End With
    
    End Sub

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Can you set a break-point and single step the code and tell me what is happening?

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If CompanyInfo_ID is numeric then you would need to change this:
    Code:
    .FindFirst "CompanyInfo_ ID= '" & Me.CompanyInfo_ID & "'"
    ...to...

    Code:
    .FindFirst "CompanyInfo_ ID= " & Me.CompanyInfo_ID

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

Similar Threads

  1. Prompt user during first login
    By nishant.dhruve in forum Access
    Replies: 1
    Last Post: 12-27-2016, 04:34 PM
  2. Replies: 6
    Last Post: 03-24-2016, 12:56 PM
  3. Replies: 2
    Last Post: 02-15-2015, 07:53 AM
  4. Pop Up window to prompt user!
    By Kevo in forum Forms
    Replies: 6
    Last Post: 06-14-2012, 02:25 PM
  5. User Prompt for multiple Files
    By ratherbgolfing in forum Programming
    Replies: 1
    Last Post: 01-31-2012, 03:38 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