Search for existing name

[Mazza]

Hi Guys,
I would like to be able to type a name in a field which is not the unique
identifier and then have it flag me if that name already exists in that
field.
I need this badly and cannot come up with a solution. Can any help
Please????

 

[Douglas J. Steele]

In the BeforeUpdate event of the text box control on your form, put code to
check whether that value already appears. This can take the form of using a
DLookup statement, and setting the Cancel parameter of the event procedure
to True if it does exist.

Private Sub MyTextBox_BeforeUpdate(Cancel As Integer)

Cancel = (IsNull(DLookup("NameField", "SomeTable", "NameField = " &
Chr$(34) & Me.MyTextBox & Chr$(34))) = False)

End Sub

DLookup will return Null if the name in MyTextBox doesn't exist in table
"SomeTable". Using IsNull will return True when DLookup returns Null. You
want Cancel to be True when IsNull returns False, and False when it returns
True.

 

[fredg]

Hi Guys,
I would like to be able to type a name in a field which is not the unique
identifier and then have it flag me if that name already exists in that
field.
I need this badly and cannot come up with a solution. Can any help
Please????

Type it into a Bound control?
Code that control's BeforeUpdate event something like this:

If DCount("*","TableName","[FieldName] = """ & [ControlOnFormName] &
"""") > 0 then
' Name Exists in table field.
' Return focus to the same control
' and cancel saving the name
Cancel = True
Else
' Name not in table field
End If

 

[Mazza]

Thanks guys I shall try it but I'm sure it will work OK
Mazza