How to look for duplicate (1 Viewer)

[cecil24tn]

I'm looking how to look for a duplicate in a form and display a msgbox if it's true and go to that Transaction Number or enter new ticket if false. I have a table named InHouseAccount a field named InHouseAccountID set to autonumber and a field named TransactionNumber Data type set to number and a form named EnterNewTicket with a text box named TransactionNumber. Would someone help me with the code. Thanks for any help

 

[theDBguy]

I usually use the DCount() function to check for duplicates. Have you tried it?

 

[arnelgp]

add code to TransactionNumber textbox, AfterUpdate event:

Private Sub TransactionNumber_AfterUpdate()
    Dim lngNumber As Long
    lngNumber = Me!TransactionNumber
    Me.Undo
    With Me.RecordsetClone
        .FindFirst "TransactionNumber = " & lngNumber
        If Not .NoMatch Then
            Msgbox "Transaction Number, " & lngNumber & ", already exists." & vbcrlf & "Press [Ok] to go to that record."
            Me.Bookmark = .Bookmark
        Else
            DoCmd.GoToRecord , , acNewRec
            Me!TransactionNumber = lngNumber
        End If
    End With
End Sub

 

[cecil24tn]

I usually use the DCount() function to check for duplicates. Have you tried it?

I'm new to vba and access. Im looking for some to code this and explain it line by line so I can understand what it does. I learn the best from detail examples. I know they are a lot to this code to get it to work. I been looking for over a week. I do know that they are a thousand ways to do the same thing and that makes it harder to understand. And Thanks for any help that you have

 

[MajP]

Probably the easiest is if you can provide your sample db. Since there are lots of ways, it may depend on how it is set up.

 

[arnelgp]

the code i gave you will work as long as the form property, DataEntry is set to No.

 

[theDBguy]

I'm new to vba and access. Im looking for some to code this and explain it line by line so I can understand what it does. I learn the best from detail examples. I know they are a lot to this code to get it to work. I been looking for over a week. I do know that they are a thousand ways to do the same thing and that makes it harder to understand. And Thanks for any help that you have

Okay, maybe you could start with this.

1. Create a blank form and add a Textbox to it.
2. In the AfterUpdate event of the Textbox, enter the following code.

If DCount("*", "InHouseAccount", "TransactionNumber='" & Me.TextboxName & "'") > 0 Then
    MsgBox "Duplicate!"
Else
    MsgBox "Not a duplicate!"
End If

View the form in Normal View and try enter some duplicate and non-duplicate values to test it.

By the way, the above code assumes the TransactionNumber field is a Short Text field. Please let us know if it's a Number field instead.

 

[arnelgp]

sample

 

[arnelgp]

Please let us know if it's a Number field instead.

post#1 already stated that it is a number.

 

[theDBguy]

post#1 already stated that it is a number.

Sorry, I missed that. My code would be more like this then.

If DCount("*", "InHouseAccount", "TransactionNumber=" & Me.TextboxName) > 0 Then

 

[cecil24tn]

Sorry, I missed that. My code would be more like this then.

If DCount("*", "InHouseAccount", "TransactionNumber=" & Me.TextboxName) > 0 Then

Ok that works perfect!!! Now how can you get it to go to the Transaction Number if it is a duplicate? Then after you get this to work will you explain it to me so I know how this works? Thanks for taking the time to help me with this.

 

[theDBguy]

Ok that works perfect!!! Now how can you get it to go to the Transaction Number if it is a duplicate? Then after you get this to work will you explain it to me so I know how this works? Thanks for taking the time to help me with this.

It might be easier to show you. Can you post a sample db with test data? When you say "go to the Transaction Number," I am guessing you're referring to a particular form. If so, please make sure to include that form in the demo you're posting. Thank you.

 

[cecil24tn]

Private Sub TransactionNumber_AfterUpdate()
Dim lngNumber As Long
    lngNumber = Me!TransactionNumber
    Me.Undo
    With Me.RecordsetClone
        .FindFirst "TransactionNumber = " & lngNumber
        MsgBox "Transaction Number " & lngNumber & " Has Already Been Entered" _
            & vbCr & vbCr & "Please Check Transaction Number!!!", vbInformation, "Duplicate Information"
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        Else
            DoCmd.GoToRecord , , acNewRec
            Me!TransactionNumber = lngNumber
        End If
    End With
End Sub

 

[cecil24tn]

Ok I got this to work. If this is good will you explain it to me so i can learn it? Thanks for all of the help

 

[cecil24tn]

Ok I got this to work. If this is good will you explain it to me so i can learn it? Thanks for all of the help

Sorry this not working. It's telling me that any number that I enter is a duplicate. When I do enter a number that is a duplicate it works like it should. It's not letting me to add a new number.

 

[theDBguy]

Sorry this not working. It's telling me that any number that I enter is a duplicate. When I do enter a number that is a duplicate it works like it should. It's not letting me to add a new number.

Any chance you can grant my earlier request?

 

[cecil24tn]

Any chance you can grant my earlier request?

 

[theDBguy]

Thanks. Check out the attached and let us know what you think.

 

[cecil24tn]

Thanks. Check out the attached and let us know what you think.

Thank you for what you have done so far. The "Yes" part looks good but the "No" will let you enter a duplicate number. I think it should clear it at go to new record.

 

[theDBguy]

Thank you for what you have done so far. The "Yes" part looks good but the "No" will let you enter a duplicate number. I think it should clear it at go to new record.

I agree. But the funny thing about your form/table is you are asking for a value in that field. For example, open the New InHouse Ticket form and then enter something in the Transaction# Textbox. But, instead of hitting the Enter key, highlight what you just entered and hit the Delete key, then hit the Enter key. You will get an error message.

So, if we try to "clear" the duplicate number, we are "forced" to replace it with something else. Your Table settings doesn't allow to just clear that one field.

At least, that's what I think is happening.