Search Duplicate records

[darinmc]

Hi
On exiting a National Insurance text box, i want it to validate there are no duplicates.


I have copied some code, which will hopefully count the times National insurance is duplicated and produce a msgbox...


Set rst = dbs.OpenRecordset("SELECT * FROM tblEmployee WHERE (([tblEmployee].[EmpNationalInsuranceNo]) =" _
& Me.TNI & ");")
getting error on the bottom line, i cant see where i'm going wrong.. Maybe closing brackets?

Run time error 3075
Syntax error (Missing operator) in query expression
Set rst = dbs.OpenRecordset("SELECT * FROM tblEmployee WHERE (([tblEmployee].[EmpNationalInsuranceNo]) = 'TN120777F)' AND (([tblEmployee].[EmpRegNo]) <> '10);'

Private Sub TNI_Exit(Cancel As Integer)
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblEmployee WHERE (([tblEmployee].[EmpNationalInsuranceNo]) = '" _
      & Me.TNI.Value & ")' AND (([tblEmployee].[EmpRegNo]) <> '" & Me.tReg.Value & ");")


-----USED SAMPLE CODE BUT I'M GETTING ERROR--------
'Set rst = dbs.OpenRecordset("SELECT * FROM tbl1 WHERE (([tbl1].[fld1]) = '" _
      & Me.txtFld1.Value & ") AND (([tbl1].[fld2]) = '" & Me.txtFld2.Value & ");")
-------------------------------------------------

If rst.RecordCount > 0 Then   ' this is a duplicate entry
      ' Enter code here
      MsgBox "This is DUPLICATE"
Else         ' This is not a duplicate entry
      ' Enter code here
      MsgBox "This is NOT dup"
End If

Set rst = Nothing
Set dbs = Nothing
End Sub

If an easier method could be used, all help would be appreciated..

I tried using Dlookup, but couldnt get that to work properly,
---------USING vLOOKUP-------
'Dim VarX As Variant
'VarX = Nz(DLookup("[EmpNationalInsuranceNo]", "tblEmployee", "[EmpRegNo] = " & Forms![Form1]!tReg))
'If VarX = Me.TNI Then
'MsgBox "Someone ALREADY has this NI"
'Else
'MsgBox "This NI is UNIQUE"
'End If
------------------

It would be great to get both methods to work
Thx
Darin

 

[Remou]

If you are using Dlookup in a Before Update event, it may suit, after update, you will already have an entry, the one just made:

[tt]'Test for a record with the EmpNationalInsuranceNo being updated
VarX = Nz(DLookup("[EmpNationalInsuranceNo]", "tblEmployee", "[EmpNationalInsuranceNo] = '" & [Put the name of the control that holds the EmpNationalInsuranceNo being entered here] & "'"),0)[/tt]

You seem to have a lot of brackets and too few single quotes:

[tt]Set rst = dbs.OpenRecordset("SELECT * FROM tbl1 WHERE [tbl1].[fld1] = '" _
& Me.txtFld1.Value & "' AND [tbl1].[fld2]) = '" & Me.txtFld2.Value & "'")[/tt]

Once again, my comments about the event apply.

 

[darinmc]

Hi
I finally got it to work, thx... it first gave me an error message stating data mis match.
EmpReg was a number, therefore i tried taking away the ' ' at end..

Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb

'Set rst = dbs.OpenRecordset("SELECT * FROM tblEmployee WHERE [tblEmployee].[EmpNationalInsuranceNo] = '" _
      & Me.tNI.Value & "' AND [tblEmployee].[EmpRegNo] <> '" & Me.tEmpReg.Value & "'")

Set rst = dbs.OpenRecordset("SELECT * FROM tblEmployee WHERE [tblEmployee].[EmpNationalInsuranceNo] = '" _
      & Me.tNI.Value & "' AND [tblEmployee].[EmpRegNo] <> " & Me.tEmpReg.Value & "")

If rst.RecordCount > 0 Then   ' this is a duplicate entry
      ' Enter code here
      MsgBox "This is DUPLICATE"
      Me.tNI = Null
      Me.tEmpReg.SetFocus
      Me.tNI.SetFocus
Else         ' This is not a duplicate entry
      ' Enter code here
      'MsgBox "This is NOT dup"
End If

Set rst = Nothing
Set dbs = Nothing

thx again
Darin

 

[darinmc]

Hi

I WOULD LIKE TO TAKE IT 1 STEP FURTHER,

I was just wondering if there was a way of displaying the recordset as a:
Example of 1 i already have:

DoCmd.OpenQuery "Find duplicates for tblEmployee NI", acViewNormal, acEdit

BUT now showing all the records, or preferablly the name, surname and NI etc

Thx
Darin

 

[Remou]

You may find it suitable to use a listbox. The RowSource can be set to your SQL string in a suitable event, for example the Before Update event of tNI or the Current event of the form. Is this what you mean?

 

[darinmc]

Yes, that would work.

Set rst = dbs.OpenRecordset("SELECT * FROM tblEmployee WHERE [tblEmployee].[EmpNationalInsuranceNo] = '" _
      & Me.tNI.Value & "' AND [tblEmployee].[EmpRegNo] <> " & Me.tEmpReg.Value & "")

How would I implement it, if I just wanted name, surname and NI number to be shown in listbox?
Do I have to do:
dim Sql1 as String?
e.g.
Sql1=("SELECT * FROM tblEmployee WHERE [tblEmployee].[EmpNationalInsuranceNo] = '" _
& Me.tNI.Value & "' AND [tblEmployee].[EmpRegNo] <> " & Me.tEmpReg.Value & "")
with all the same syntax...

Could you please give a little help and basic code?

Thx
Darin

 

[Remou]

I have not tested this:

Sql1="SELECT ID, SName, Fname FROM tblEmployee WHERE [tblEmployee].[EmpNationalInsuranceNo] = '" _
      & Me.tNI & "' AND [tblEmployee].[EmpRegNo] <> " & Me.tEmpReg
Me.lstListBox.RowSource=sql1

You will need to set the listbox up with the appropriate number of columns. You may wish to hide the key column, which you can do by setting the Column Width to zero.

You could set the list box to the selection of fields you want for testing purposes:

SELECT ID, SName, Fname FROM tblEmployee

Then play around to get it looking right.

 

[darinmc]

Thx
Will give it a go later...

Darin