Dear All,
How do i find duplicate record in ms access table. is there any validation expression that can be used to find the duplicate records or any other method.?
Please assist.
rgds,
aligahk06
Dear All,
How do i find duplicate record in ms access table. is there any validation expression that can be used to find the duplicate records or any other method.?
Please assist.
rgds,
aligahk06
Run the query wizard,FIND DUPLICATES. This works.
if you are adding records,run a DLOOKUP before you add ,to see if it exists first.
Can you please explain in details how to run DLOOKUP before adding a record.
look at the similar threads links at the bottom of your thread - there are several which would appear to meet your requirements
I usually test using DCount() rather than DLookup(). Something like:
in the Forms Before Update eventCode:If DCount("*", "YourTableName", "txt = '" & Me.ControlNameOnForm & "'") > 0 Then MsgBox "Duplicate" Cancel = True End If
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
// For numeric field ( numerical value ) prCol1 table name woPR
error: error 3075 missing operator
Any help in this error
Private Sub prCol1_BeforeUpdate(Cancel As Integer)
Dim resp As String
If DCount("prCol1", "woPR", "prCol1 = " & Me.prCol1) > 0 Then
resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
If resp = vbNo Then
Cancel = True
End If
End If
End Sub
Posters often say that something is numeric because to them it is(e.g. 357) but in the table they have a field with Data Type "Short Text" which makes the data stored there text not numeric.
Are you certain that the field called "prCol1" in table "woPR" is numeric
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Yes,
prCol1 is defined data type is number.
Check the value of Me.prCol1 when the code runs
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Private Sub prCol1_BeforeUpdate(Cancel As Integer)
Dim resp As String
If DCount("prCol1", "woPR", "prCol1 = " & Me.prCol1) > 0 Then
resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
If resp = vbNo Then
Cancel = True
End If
End If
End Sub
in Form view while input the existing value 4731 already exist asking message yes or no while clicking no it flash a message
syntax error Me.prCol1
1) Does the control definitely have the name of "prCol1"
2) What happens if you use:Code:Private Sub prCol1_BeforeUpdate(Cancel As Integer) Dim resp As String If DCount("prCol1", "woPR", "prCol1 = " & CInt(Me.[prCol1])) > 0 Then resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo) If resp = vbNo Then Cancel = True End If End If End Sub
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Please see attached DB, Code inserted for control prCol1 to check duplicate but it returns to debug or end while exiting from the form.
Thanks..
rgds,
aligahk06
vbno is a number so dim resp as integer
your use of cInt is unnecessary and will cause an overflow error if the user enters a number outside the integer range.
I'm guessing your syntax error is due to the field not being populated - i.e. is null
since the vba opens, the erroring line is highlighted, you can hover over each of the values to see what value is held - then see if your code can handle that error. At the moment you are not handling situations where prCol1 is null, negative or >32677 - but perhaps that doesn't matter for you - you are happy that prCol1 can be populated with these values. But the null situation matters for code and needs to be handled
Code:if isnull(prCol1) then 'that's OK else 'that's not OK end if
The validation seems to be working as it should even without the last addition (CInt(Me.[prCol1])
However, at the moment, if you enter a duplicate number (e.g. 3368) then the code runs and asks if you really want to use it.
If you answer "No" then the control is NOT updated but is left with the figure (3368) awaiting update in edit mode (The little pencil signis visible in the record selector).
If you try to close the form in this state, Access will try to save the record being edited, the Before Update will run again and you will be in this endless loop until you press the "Esc" key to exit the edit of this record.
Alternatively, a better solution might be to add the following line:
immediately after the line:Code:Me.Undo
Code:Cancel = True
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Sir ,
Thanks for precious time.