I want a field to check itself for duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a form ( ID field hidden) I wish to have the second field (ie Ingredient
Name) check and see if it already exists. If it does then display the record
for editing. If not then continue to fill out form. I've tried various things
but fairly new so a few clues would be great! Eric
 
copy this code into the before update of your ingredient name, change the
spelling of IngredientName if nessesary

------------------------------------------------------------------------------
-------
Private Sub IngredientName_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.IngredientName.Value
stLinkCriteria = "[IngredientName]=" & "'" & SID & "'"


If DCount("[IngredientName]", "[table name where IngredientName exists]",
stLinkCriteria) > 0 Then

Me.Undo

MsgBox "WARNING " _
& SID & " Already Exists." _
& vbCr & vbCr & "You will now be taken there.", vbInformation _
, "Duplicate Information"

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
 
Thanks for the time Chris.
Is coming up with error message " Userdefined type not defined" -
DAO.Recordset.

Im using Acess 2002. When the line is removed the procedure runs but
error message - no data found.
How do I get DAO to work?

Chris B via AccessMonster.com said:
copy this code into the before update of your ingredient name, change the
spelling of IngredientName if nessesary

------------------------------------------------------------------------------
-------
Private Sub IngredientName_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.IngredientName.Value
stLinkCriteria = "[IngredientName]=" & "'" & SID & "'"


If DCount("[IngredientName]", "[table name where IngredientName exists]",
stLinkCriteria) > 0 Then

Me.Undo

MsgBox "WARNING " _
& SID & " Already Exists." _
& vbCr & vbCr & "You will now be taken there.", vbInformation _
, "Duplicate Information"

rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub
------------------------------------------------------------------------------
---------------------------------
Hope this helps!!

On a form ( ID field hidden) I wish to have the second field (ie Ingredient
Name) check and see if it already exists. If it does then display the record
for editing. If not then continue to fill out form. I've tried various things
but fairly new so a few clues would be great! Eric
Click to expand...
Click to expand...
 
How do I get DAO to work?
Click to expand...

Open the VBA editor; on the menu select Tools... References. Scroll
down to the line with "Microsoft DAO 3.6 Object Library" and check it.


John W. Vinson[MVP]
 
Thanks John,
I too still have a lot to learn and would not have been able to answer Erics
problem!
Eric, A quick way into the VBA Editor is Contrl-G and follow John's
instructions from there
Let us know how it turns out!
 
Thanks John and Chris
Am progressing I guess.!
Now having trouble with the Bookmark line. - No entry for Me.Bookm ark or
for rsc. Bookmark stLinkCriteria is valid for each different entry.
I've had trouble using the dao methods before but now I know why it's
another step ahead and more brain power required - Great !
- and back to the refence books
any suggestions for books - I've sort of progressed beyond the access 2000
bible.

Also as an aside what do the letters MVP stand for - you guys are great.
eric
 
Back
Top