Please help with notifying (not preventing) when duplicates entered 1

[tmpark]

I've searched the threads and have come up with good advice but I'm having trouble implementing the solution. Any help you can provide is most appreciated.

I have a form, frmPROPERTY, with a field, PROP_ID, from the table tblPROPERTY. I need to display a message whenever someone enters a PROP_ID that is already in a previous record. It's ok to have a duplicate but I want the user to know that it is one. I found the following script and tried it in After Update but nothing seems to be happening when I enter two of the same Ids.

Private Sub PROP_ID_AfterUpdate()

If DCount("*", "tblPROPERTY", "[PROP_ID]=" & Me.PROP_ID) > 0 Then
MsgBox "This property has apparently been involved in a previous project"
Me.PROP_ID = Null
End If

End Sub

I may be missing something very obvious. PROP_ID is a text field with IDs such as 000-0000 or 000-0000-0000 and no validation rules applied. There are also null values for this field in the table.

Thanks for your help.

I'm also interested in general naming convention for objects and fields if you have an extra second. Thanks.

Trent Park

 

[shannonp]

Try this:

-------code begins

Private Sub PROP_ID_BeforeUpdate(Cancel As Integer)

Dim dbs As Database
Dim tdf As TableDef
Dim idx As Index
Dim rst As Recordset

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!tblPROPERTY
Set idx = tdf.CreateIndex("PROP_ID&quot
Set rst = dbs.OpenRecordset("tblPROPERTY&quot
rst.Index = idx.Name
rst.Seek "=", [PROP_ID]
If rst.NoMatch = False Then MsgBox "Property ID already exists!"
rst.Close
Set dbs = Nothing

End Sub

-------code ends

Let me know how it goes

 

[shannonp]

Additionally what you are doing is fine too but if you're dealing with thousands of records the fatest option is to use the Seek method. (in other words Dlookup, Dcount etc... are pretty slow).

 

[tmpark]

Thanks a lot for responding. When I first tried it I got an error so I turned on the MS DAO 3.6 object library. That seemed to clear up the first error but now I'm getting a "Compile error: Method or data member not found" The debugger highlights ".NoMatch" in the If statement. Any idea on what might be causing this? Again, thanks for all of your great help.

 

[shannonp]

hmmmm strange....you might want to check that your 'Microsoft DAO 3.6 Object Library' is high on your reference list (take the priority up - it could be conflicting with the ADO Library).

Quite simple but if you need help just let me know.

 

[tmpark]

That's it! Thanks so very much. It works very well.

 

[tmpark]

This may not be the place for this type of thing but do you know where we go or how we go about recognizing contibutors for their help? Or voting?

 

[shannonp]

hehe - you could start by clicking the underlined sentence ("Mark this post as a helpful/expert post!&quot below one of my posts

Thanks

 

[tmpark]

Duh, thanks again for your help.

 

[shannonp]

No problems Trent, although most people will agree with me when I say that it makes the contributor that much more "knowledge"-able too. And besides, I like helping...its fun!