How to Add feature to query Append ?

[LarryE]

Form Error event perhaps?

OK I'll try that. Thanks.

 

[LarryE]

You can either trap the error in the AfterUpdate event of the control with the duplicate value and prevent the user from going further OR you can trap the data error in the on Error event of the form which interrupts the BeforeUpdate event.

May I suggest you play with the tool I have suggested many times to help you to learn how to use the Access event model.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Here is some sample code for the form's Error event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case 3022
            MsgBox "Some Custom Message", vbOKOnly
            Response = acDataErrContinue
        Case Else
            MsgBox "DataErr = " & DataErr
            Response = acDataErrDisplay
    End Select

End Sub

Here you thought you can't teach an old dog like me new tricks. Thanks so much.

 

[azhar2006]

I promise you will love the tool if you take the time to understand it and how you can test concepts by adding your own forms or modifying the ones I created. Uncle Gizmo and I had a blast when we made the first video. I suggest that you leave my forms alone. Just copy them and modify them. Modify the combos so you can choose to use your customized forms or mine.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

   Select Case DataErr
      Case 3022
           MsgBox "You added a record which duplicates an existing value."
           Response = acDataErrContinue
      Case Else
           Response = acDataErrDisplay
   End Select

End Sub

You can also add a focus transition line to the non-repeating field.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were uncuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."

        Me.MilitaryNumber.SetFocus
        Response = acDataErrContinue
    Else
        Response = acDataErrDisplay
    End If
End Sub

You are really great.

Pat Hartman​

LarryE​

Gasman​

 

[Gasman]

You really should not be getting that far?

That is called 'shutting the barn door, after the horse has bolted'

 

[LarryE]

I promise you will love the tool if you take the time to understand it and how you can test concepts by adding your own forms or modifying the ones I created. Uncle Gizmo and I had a blast when we made the first video. I suggest that you leave my forms alone. Just copy them and modify them. Modify the combos so you can choose to use your customized forms or mine.

OK, but as you know, I already know all there is to know about ACCESS so we will see. I might be able to learn some new stuff though.

 

[LarryE]

I promise you will love the tool if you take the time to understand it and how you can test concepts by adding your own forms or modifying the ones I created. Uncle Gizmo and I had a blast when we made the first video. I suggest that you leave my forms alone. Just copy them and modify them. Modify the combos so you can choose to use your customized forms or mine.

Pat, I don't know how to find this tool. I did attempt to follow the link you posted, but got hijacked by WinZip corporation. It installed a bunch of utilities on my computer that I didn't know anything about and never did get to any website where your tool can be found. I have not a clue how to get to it to look at it or use it. WinZip corporation won't let me get to it. Every time I click on the link you provided it goes to WinZip corporation.

 

[Gasman]

Pat, I don't know how to find this tool. I did attempt to follow the link you posted, but got hijacked by WinZip corporation. It installed a bunch of utilities on my computer that I didn't know anything about and never did get to any website where your tool can be found. I have not a clue how to get to it to look at it or use it. WinZip corporation won't let me get to it. Every time I click on the link you provided it goes to WinZip corporation.

Try here. https://www.access-programmers.co.u...-bad-for-business-1-and-2.324342/post-1861285

 

[LarryE]

Try here. https://www.access-programmers.co.u...-bad-for-business-1-and-2.324342/post-1861285

Thanks. Got it.

 

[LarryE]

@LarryE I don't know what happened. I clicked the link I posted earlier. It took me to the correct thread.

@azhar2006 You're welcome

Gonna take a look this afternoon.