Finding Duplicates

prkundan

New Member
Joined
Nov 13, 2018
Messages
17
When a number is typed on a form I want to immediately check whether it already exists in the database. Can this be done through code?


Also when my code is lang I want to display "Please wait..." and when my code finishes it should vanish. How to do it?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
For the first part of your question, you can use an AfterUpdate event on the field in the form. You can use DCOUNT to count how mnay current records in your table already have this value.
Assuming that the field is actually numeric and the field name on your form is the same as the field in your table, the code would look something like this:
Code:
Private Sub [I]FieldName[/I]_AfterUpdate()

    If DCount("[I]FieldName[/I]", "[I]TableName[/I]", "[I]FieldName[/I] =" & Me.[[I]FieldName[/I]]) > 0 Then
        MsgBox "This number already exists in database."
        Me.[[I]FieldName[/I]].Value = ""
    End If
    
End Sub
Here is more on DCOUNT: https://www.techonthenet.com/access/functions/domain/dcount.php
 
Last edited:
Upvote 0
Where to find Macro Recorder?

It is on the Developer Tab

or use shortcut {ALT} L R
 
Last edited:
Upvote 0
What is wrong with the following code:
If DCount("[REG_NO]", "Tbl_Regn", "[REG_NO] =" & Me.[ID]) > 0 Then
Me.Parent.Check45 = True
Else
Me.Parent.Check45 = False
End If

It is giving error : can't find the field '|1' referred to in your expression.
 
Upvote 0
What is wrong with the following code:
If DCount("[REG_NO]", "Tbl_Regn", "[REG_NO] =" & Me.[ID]) > 0 Then
Me.Parent.Check45 = True
Else
Me.Parent.Check45 = False
End If
What is the data type of the ID field?
Also, I am not sure about the use of "Parent".
Does it work if you temporarily remove it and test something like this?
What is wrong with the following code:
Code:
[COLOR=#333333]If DCount("[REG_NO]", "Tbl_Regn", "[REG_NO] =" & Me.[ID]) > 0 Then[/COLOR]
[COLOR=#333333]    MsgBox "Yes"[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]    MsgBox "No"[/COLOR]
[COLOR=#333333]End If [/COLOR]
 
Upvote 0
Thanks for the hint!!! Actually the following was required:
DCount("[REG_NO]", "Tbl_Regn", "[REG_NO] =" & Me.Parent.[ID])
 
Upvote 0
That is because you never stated that you were running this code in a subform against a parents form control.?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top