By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

How to make a message "data can't double"

P: 4
I have a textbox call Staff_ID, I don't want my user to type the same Staff_ID . So I write the vba code like this

Expand|Select|Wrap|Line Numbers
  1. Private Sub Staff_ID_AfterUpdate()
  2. Dim SID As String
  3. Dim stLinkCriteria As String
  4. Dim rsc As DAO.Recordset
  5.  
  6. Set rsc = Me.RecordsetClone
  7. SID = Staff_ID.Value
  8. stLinkCriteria = "[Staff_ID]=" & "'" & SID & "'"
  9.  
  10.     If DCount("Staff_ID", "[Employee]", stLinkCriteria) > 0 Then
  11.         Me.Undo
  12.         MsgBox "Staff_Id = " & SID & " Already Have..." _
  13.         & vbCr & vbCr & "Type another staff_ID.", vbInformation _
  14.         , "Data cannot Double"
  15.         Me.New.SetFocus
  16.  
  17.         rsc.FindFirst stLinkCriteria
  18.         Me.Bookmark = rsc.Bookmark
  19.         End If
  20. Set rsc = Nothing
  21. End Sub
The problem when I try it I Have a message " Run time Error "3420" Object Invalid or no longer set.When I open the debug at ( rsc.findfirst stlinkcriteeria ) have a yellow color.

Please help me to solve this problem. Thank you
Feb 19 '12 #1

✓ answered by Mihail

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     If DCount("[Staff_ID]", "Employee", "[Staff_ID]='" & Staff_ID & "'") Then
  3.         MsgBox ("Existent ID")
  4.         Cancel = True
  5.     End If
  6. End Sub

Share this Question
Share on Google+
3 Replies


100+
P: 759
Access manage itself your problem.
Assuming that the Staff_ID text box is bound to Staff_ID field all you need is to set the Indexed property for Staff_ID field (table design view -> General tab) to Yes (No Duplicates).

This way Access do not allow duplicates for this field.

By the way, the event _AfterUpdate() is not useful because you need to looking for duplicates _BeforeUpdate (BEFORE the value is passed to table, isn't it ?)

Good luck !
Feb 19 '12 #2

P: 4
thanks, but it still no work, maybe you have another way for my case, all I need is making my application look like a professional. Thanks
Feb 22 '12 #3

100+
P: 759
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     If DCount("[Staff_ID]", "Employee", "[Staff_ID]='" & Staff_ID & "'") Then
  3.         MsgBox ("Existent ID")
  4.         Cancel = True
  5.     End If
  6. End Sub
Feb 22 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.