How to prevent a field of the form from allowing multiple entries?

[Guest]

I have a sub-form in a master form with 5 fields. As soon as I reach the 5th
field of the sub-form and enter data into it and click Enter, the cursor
jumps to the first field of the same sub-form, all previous input for all 5
fields is hidden and the sub-form allows me to have a new input creating a
duplicate record.

How can I prevene a FORM from allowing duplicate records? I know it is
possible in tables by setting the Index field on "No duplicates allowed".

Thanks in advance.

Regards,
Svetlana

 

[Guest]

There are several different ways to do this, depending on personal preference
and the nature of the business rules.
One way is to specify not duplicates for the table field and use error
handling in the form's code to trap for the error.
Another is to use the field's Before Update event and use a DLookup to check
the record source for the existence of the value.
Another is to use an unbound combo to look up data in the field. The After
Update event is useful for existing data, and the Not In List event is useful
when a new value is entered in the combo.

 

[Guest]

Hi, Klatuu,

After Event sounds OK. I want to prevent a second and any other subsequent
record from being created as soon as a first record is created (by I still
want to be able to change the erroneous input in the first record (in
individual fields of the record) by simply deleting the entry and re-typing
the value).

N.B. By a record I understand a string of individual fields. If it is
possible to block the record from duplicates entries in all the underlying
fields it will save a lot of time. Otherwise, I guess I need to protect every
field of the record.

Does it make sense what I wrote?
If it does, woudl you please, give a more detailed explanation on how I can
execute an After Event?

Thanks and regards,
Svetlana

 

[Guest]

If you want to prevent duplicates on several different fields, it would be
less coding to define the fields as no duplicates in table design, then use
error handling to trap the error. If you want to do it on a field, by field
basis, it is not the After Update, but the Before Update. Here is what you
need:

Private Sub txtSomeField_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[SomeField]", "MyTable", "[SomeField] = '" &
Me.txtSomeField & "'") Then
MsgBox Me.txtSomeField & " Is Already in the Table"
Cancel = True
End If
End Function

 

[Guest]

Thanks a lot!!!

If you want to prevent duplicates on several different fields, it would be
less coding to define the fields as no duplicates in table design, then use
error handling to trap the error. If you want to do it on a field, by field
basis, it is not the After Update, but the Before Update. Here is what you
need:

Private Sub txtSomeField_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[SomeField]", "MyTable", "[SomeField] = '" &
Me.txtSomeField & "'") Then
MsgBox Me.txtSomeField & " Is Already in the Table"
Cancel = True
End If
End Function

Hi, Klatuu,

After Event sounds OK. I want to prevent a second and any other subsequent
record from being created as soon as a first record is created (by I still
want to be able to change the erroneous input in the first record (in
individual fields of the record) by simply deleting the entry and re-typing
the value).

N.B. By a record I understand a string of individual fields. If it is
possible to block the record from duplicates entries in all the underlying
fields it will save a lot of time. Otherwise, I guess I need to protect every
field of the record.

Does it make sense what I wrote?
If it does, woudl you please, give a more detailed explanation on how I can
execute an After Event?

Thanks and regards,
Svetlana