Finding Duplicates

[prkundan]

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?

 

[Joe4]

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:

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

 

[prkundan]

Thanks A Lot!!! GOD BLESS YOU!!!
Where to find Macro Recorder?

 

[Yongle]

Where to find Macro Recorder?

It is on the Developer Tab

or use shortcut {ALT} L R

 

[prkundan]

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.

 

[welshgasman]

It is on the Developer Tab

or use shortcut {ALT} L R

Excel only though?

 

[Yongle]

It is not possible to record a macro in access https://answers.microsoft.com/en-us...n-access/20f723f1-a9be-438f-b5d2-2738efc71c5e

 

[Joe4]

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:

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

 

[prkundan]

Thanks for the hint!!! Actually the following was required:
DCount("[REG_NO]", "Tbl_Regn", "[REG_NO] =" & Me.Parent.[ID])

 

[welshgasman]

That is because you never stated that you were running this code in a subform against a parents form control.?