Catching Duplicate records

checking duplicate records before hitting the save button

Page 1 of 1

4 Replies - 5885 Views - Last Post: 26 January 2010 - 05:58 AM Rate Topic: -----

#1 purple_jay  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 02-December 08

Catching Duplicate records

Posted 24 January 2010 - 07:25 AM

Hi! I'm trying to do a simple file maintenance program wherein a user can add, edit and delete records.
There's this part of my program that I'm trying to 'catch' the user if in case he/she is trying to add a new record with an ID that already exists in the database before he/she hits the save button and it won't work..

Here's my code:
Private Sub cmdSave_Click()
	GetRS
	Set rs = Nothing
	rs.CursorLocation = adUseClient
	If Mode = "New" Then
	rs.Open "SELECT COUNT(e_ID) As noDUP FROM Employee WHERE e_ID = '" & txtEmpID & "'",cn,3,2
	If noDUP = 0 Then
	rs.AddNew
	Else
	MsgBox ("Record Already Exists!")
	Exit Sub
	   End If
	 ElseIf Mode = "Edit" Then
		rs.Open "Select * from Employee where e_Id ='" & txtEmpID & "'", cn, 3, 2
	End If
	With rs
		.Fields(0) = txtEmpID
		.Fields(1) = txtFirst
		.Fields(2) = txtLast
		.Fields(3) = Val(cmbDept)
		.Fields(4) = txtOffice
		.Fields(5) = txtHome
		.Fields(6) = dtpDateHired.Value
		.Fields(7) = CDbl(txtHourlyRate)
		.Update
	End With
	Locked_Fields
End Sub



I'm getting an error message that says "item cannot be found in the collection corresponding to the requested name or ordinal"

Need help..

Is This A Good Question/Topic? 0
  • +

Replies To: Catching Duplicate records

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Catching Duplicate records

Posted 24 January 2010 - 08:03 AM

try using this
if rs.recordcount <> 0 then
' add the records
end if



in short check if there is record for that id
Was This Post Helpful? 0
  • +
  • -

#3 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: Catching Duplicate records

Posted 25 January 2010 - 07:38 AM

Actually NoBrain, since purple jay is using a select count, the recordset will always have a recordcount that equals one (1), and purple jay, your error means that e_ID is not a field in the database.

Furthermore, your "If noDup" will also not work as I do not see where noDup is defined and you should recieve a variable not defined error.

Now, when aliasing a file as something, you still need the recordset object to reference it. So your If statement should be like...
If Rs.Fields("noDup").Value = 0 Then
  'okay to add records
Else
  'if it does not equal zero then it must be greater than zero
End If




Good Luck
Was This Post Helpful? 0
  • +
  • -

#4 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Catching Duplicate records

Posted 25 January 2010 - 09:09 AM

yea your right :)

This post has been edited by NoBrain: 25 January 2010 - 09:10 AM

Was This Post Helpful? 0
  • +
  • -

#5 purple_jay  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 02-December 08

Re: Catching Duplicate records

Posted 26 January 2010 - 05:58 AM

vb5prgrmr and No Brain, Thanks much!
It's already working.



I forgot that noDup is considered a field now since I've used the SELECT COUNT query. .. "*slaps forehead*

This post has been edited by purple_jay: 26 January 2010 - 06:15 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1