# need help with some VBA

**URL:** <https://boards.straightdope.com/t/need-help-with-some-vba/463314>\
**Category:** Factual Questions\
**Created:** [September 12, 2008, 5:57pm UTC](https://boards.straightdope.com/t/need-help-with-some-vba/463314 "2008-09-12T17:57:55Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [September 12, 2008, 5:57pm UTC](https://boards.straightdope.com/t/need-help-with-some-vba/463314/1 "2008-09-12T17:57:55Z")

</div>

I’m not very well versed in VBA, but I think it can do this and I just can’t figure out how: I’ve got a form in Access, and while the database is allowed to have duplicates in a particular field, I would like a message box to display when the user enters a duplicate value. It’s not really worth getting into why I need to do it this way, but suffice it to say that supervisors sometimes want what they want and it’s not worth arguing about.

So when someone keys a value into the ACCOUNT\_NUMBER field in this form, I need a way to warn the user that they’ve entered a duplicate. Anyone know how to do this?

---

<div class="post-metadata">

**Author:** ![NicorGasMan](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@NicorGasMan](https://boards.straightdope.com/u/NicorGasMan)\
**Post date:** [September 12, 2008, 6:15pm UTC](https://boards.straightdope.com/t/need-help-with-some-vba/463314/2 "2008-09-12T18:15:24Z")

</div>

You could use the DLookup or DCount functions. Something like this would work:  
Private Sub ACCOUNT\_NUMBER\_AfterUpdate()  
If DCount("[ACCOUNT\_NUMBER]", “[MYTABLE]”, "[ACCOUNT\_NUMBER]= Forms![MYForm]![ACCOUNT\_NUMBER] ") \> 0 Then  
MsgBox “This ACCOUNT NUMBER has already been entered”, vbInformation  
End If  
End Sub

---

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [September 12, 2008, 6:16pm UTC](https://boards.straightdope.com/t/need-help-with-some-vba/463314/3 "2008-09-12T18:16:34Z")

</div>

thanks, I’ll give it a try now and let you know how it works out.

---

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [September 12, 2008, 6:45pm UTC](https://boards.straightdope.com/t/need-help-with-some-vba/463314/4 "2008-09-12T18:45:57Z")

</div>

that did the trick, thanks for the help!

---

<div class="post-metadata">

**Author:** ![Clothahump](https://avatars.discourse-cdn.com/v4/letter/c/51bf81/32.png) [@Clothahump](https://boards.straightdope.com/u/Clothahump)\
**Post date:** [September 12, 2008, 9:39pm UTC](https://boards.straightdope.com/t/need-help-with-some-vba/463314/5 "2008-09-12T21:39:56Z")

</div>

Make sure that field is indexed, allowing duplicates. Otherwise, DLookup/DCount will take a looonnnnggg time once you get some serious data in the table.