checking duplicate values on data entry

[Afsaneh]

Dear all,
In my table design,Because I need to accept No value , I can not use NO
duplicate index for my field , but I shouldn't let duplicate values in it. to
develop the need I check the value in the data entry form using "Find first"
of the record set. On my own computer it is OK and no duplicate value can be
entered. but users CAN add duplcate value.(mine is office xp but some use
office2003). what should I check? and if there are better solutions please
let me know. tnx in advance.

 

[ruralguy via AccessMonster.com]

The version of Access should make no difference. How are you using FindFirst
and in what event? Show us the complete event from Private Sub ... to End Sub.

 

[Afsaneh]

when the text box loses focus this sub will be run
and if it is a duplicate value an instance of the form with the previous
data will be shown.
Private Sub cmdNewPersonel_Click()
On Error GoTo Err_cmdNewPersonel_Click
Dim rst As DAO.Recordset


If Not (IsNull(Me.T) Or Trim(Me.T) = "") Then

Set rst =
CurrentDb.QueryDefs("QFullKala").OpenRecordset(dbOpenDynaset)

'Search for a matching record
rst.FindFirst "trim([jamdarycode]) = " & Trim(Me![T])
If Not rst.NoMatch Then
'If yes then
Me.T.Undo
If MsgBox(" Duplicate value. show the previous one>",
vbOKCancel, "warning") = vbOK Then

Set frmchild = New Form_Kala
frmchild.AllowEdits = True
frmchild!lstKalaGroup.Requery
frmchild!lstKalaGroup.SetFocus
frmchild!lstKalaGroup = rst.Fields("kalagroup.id")
TSelectedKalaGroup = rst.Fields("kalagroup.id")
' frmchild.lstSubGroup.Requery
frmchild.lstSubGroup.SetFocus
frmchild!lstSubGroup = rst.Fields("kalasubgroup.id")
TselectedSubGroup = rst.Fields("kalasubgroup.id")
' frmchild.lstAmval.Requery
frmchild!lstAmval.SetFocus
frmchild!lstAmval = rst.Fields("amval.id")
frmchild!cmdToEditAmval.SetFocus

frmchild.Visible = True
frmchild.Refresh
End If
Set rst = Nothing
GoTo Exit_cmdNewPersonel_Click
End If
Set rst = Nothing
End If


DoCmd.OpenForm "amval"
Me.lstAmval.Requery
' To The First Status of Form
cmdReviewPersonel_Click

' End If


Exit_cmdNewPersonel_Click:

SchOption = 0
Exit Sub

Err_cmdNewPersonel_Click:
MsgBox Err.Description
Resume Exit_cmdNewPersonel_Click

End Sub

 

[Daryl S]

Afsaneh -

You can still set up a unique index, and ignore null values. That will
prevent any duplicate values from being entered, but still allow the nulls to
be there.

 

[Afsaneh]

THank you for your help. Now the question is : shall I check for duplicate
value before the table constraints' warning? and if yes which method is
better dlookup or findfirst?

 

[Daryl S]

Afsaneh -

Use dlookup as you don't have to open a recordset with all the data in it
like you would with findfirst.