How to Add feature to query Append ?

[azhar2006]

I did ask if he needed to do updates or if the exchange was simply for new records and I mentioned the two choices when only adds are relevant. What is he supposed to do if there are "duplicates" in the input file? Should the entire file be set aside unless it is corrected? Whenever two applications exchange information, there needs to be a protocol for validation and acceptance of the data. That has not been defined by this poster. I am merely emphasizing the importance of the unique index. NO MATTER WHAT ELSE he does, the index needs to be added. The prevention of duplicates is the job of the database engine. You might do code validation so you can give the user better error messages but the buck stops with the db engine. And if you don't assign this task to the engine by creating a unique index, you are a fool. I am trying very hard to not equivocate I don't want there to be any confusion in the mind of anyone. If you need to prevent duplicates, you need a unique index. The rest of the process is workflow.

This is what the OP said:

Clearly the reply didn't give you the warm fuzzies either but you didn't insist on the index. That is what I am doing. You reiterated the importance of running the find duplicates query. Running the find duplicates query is information but it does NOT prevent adding duplicates. THAT is the important part of the process. We don't even know if anyone cares about the duplicates. They probably do so I would also run the query but it is far more likely that the duplicates are actually there because some piece of other data needs to change. That is why I asked yet again about the need to update existing data.

I would probably go further. Not only would I check for duplicates but if there are duplicates, I would compare each associated field. If they are all the same, I would simply ignore the duplicate. Why make work you don't need to. If they are different, then there is something amiss and it needs to be corrected.

I think our big brother @The_Doc_Man knows what the military number is. It is a number that accompanies the employee from the time he enters the service until his retirement. It is impossible for two employees to have the same number, because this number consists of rows of numbers, each two numbers refer to a specific code of information, and it is always approved and even printed on the metal necklace. It is impossible for it to be repeated except for the same person. For example, this soldier was working in Unit A and then transferred to Unit C after four years. When it is repeated, we know that Unit A did not remove it from its database. I think my big brother @The_Doc_Man knows what I am talking about.

 

[azhar2006]

We are very likely having a language issue. Just answer yes or no. Did you add the unique index on the MilitaryNumber? It also needs to be required and the default should be NULL and NOT 0 if it is an actual number.

No, I did not add it.

 

[Pat Hartman]

Do you intend to add it? Simply checking for duplicates will never stop them from being added to your table. The only way to ensure that no duplicate ever gets added is to add the unique index AND set the Required property to Yes and leave the Default value empty. Was I not clear? I do not know how to make you understand the importance of this. If you do not add the unique index, you will end up with duplicates in the table if someone runs the append query.

Also, I would not store the MilitaryNumber as a long integer since it is actually a code and not a number on which you would ever perform arithmetic. But, that is just a recommendation based on many years of experience. Do what you want with that. For those of you in the US, I never stored the SSN as a long integer in any application I designed that collected it and since the SSA started adding letters to the string a few years ago, I was rewarded because none of my applications needed to be changed A code is a code. It is not a number. It may be a number in its source application where it is generated but in all other applications, it is simply a unique reference code. The military has fewer members than the general population so they probably have some years to go before they run into having to reuse old numbers or increase the length or start using letters. So, what happened to the SSN will at some point happen to the MilitaryNumber. It will ultimately have to be expanded in length or include the use of letters.

 

[LarryE]

No, I did not add it.View attachment 116310

Adding a unique index will prevent duplicates from being entered and saved. This is always preferrable to having to detect duplicates and correct them later. If a user inputs a duplicate MilitaryNumber, then an error message will appear preventing the number from being entered and saved.

 

[Pat Hartman]

Thank you Larry for confirming my answer.

 

[LarryE]

Thank you Larry for confirming my answer.

Ok but we're not done yet with this issue. I've been doing some experimenting with indexes and the error message they produce. When you use special indexing, and the error occurs when a duplicate record is created, the error message is fired on the forms BeforeUpdate event. Here is the message:

To most users, it could be very confusing, because by the time it is fired, they could have entered data in many fields, so I think any developer needs to capture that error message when it occurs and replace it with something like "Duplicate Military Number." Using the example we have been using.

I have been attempting to do just that with no success. Here is the code I am using for the form BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Update_Error
Exit Sub
Update_Error:
DoCmd.CancelEvent
MsgBox "Duplicate Military Number"
Exit Sub
End Sub

And here is form:

If I enter 12345 on the first record, it will produce the error because it is a duplicate of the second record (as expected). But I cannot seem to capture the error BEFORE it is displayed and then show the MsgBox "Duplicate Military Number".

Can you, or anyone else, look at the BeforeUpdate code and tell me how, or even if, we can somehow capture the ACCESS native error message and then show my message box? Once again, it's the old story of trying to learn new stuff.

Thanks Pat and everyone for your kind assistance. Here's the file too.

 

[Gasman]

Form Error event perhaps?

 

[Pat Hartman]

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

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

@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

 

[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.

 

[Pat Hartman]

There are three videos. The one on youtube is a presentation to an access user group.