Check for duplicate before new record saved

[Guest]

I would like to check for a duplicate entry and cancel the operation if the
user starts a new record with a duplicate ID. But I can't figure out how to
get the code to run. The field is the very first one for the record, so the
record doesn't exist until after the field has been exited. This seems to
mean that none of the usual events like AfterUpdate, Change, Exit, etc. get
triggered. The field is bound to a table. Should I make it unbound?

 

[Guest]

Assuming the user enters the ID in a control, use the control's Before Update
event.

If Not IsNull(DLook("[ID]", "MyTable", "[ID] = " & Me.txtID) Then
MsgBox "This ID is already in the table"
Cancel = True
End If

If the ID is found in the table, the DLookup will return its value. The
user will get a warning, the Update will be canceled, and the focus will stay
in the control.

 

[Guest]

The BeforeUpdate event will only fire if the value entered in the field by
the user. If it is done programmatically, it will not fire.
How are you assigning the new ID value?

My problem is not how to check for the duplicate but how to trigger the code.
The BeforeUpdate event doesn't occur. I am not sure why that is the case.
--
Dave

Assuming the user enters the ID in a control, use the control's Before Update
event.

If Not IsNull(DLook("[ID]", "MyTable", "[ID] = " & Me.txtID) Then
MsgBox "This ID is already in the table"
Cancel = True
End If

If the ID is found in the table, the DLookup will return its value. The
user will get a warning, the Update will be canceled, and the focus will stay
in the control.

I would like to check for a duplicate entry and cancel the operation if the
user starts a new record with a duplicate ID. But I can't figure out how to
get the code to run. The field is the very first one for the record, so the
record doesn't exist until after the field has been exited. This seems to
mean that none of the usual events like AfterUpdate, Change, Exit, etc. get
triggered. The field is bound to a table. Should I make it unbound?

 

[Guest]

My problem is not how to check for the duplicate but how to trigger the code.
The BeforeUpdate event doesn't occur. I am not sure why that is the case.
--
Dave

Assuming the user enters the ID in a control, use the control's Before Update
event.

If Not IsNull(DLook("[ID]", "MyTable", "[ID] = " & Me.txtID) Then
MsgBox "This ID is already in the table"
Cancel = True
End If

If the ID is found in the table, the DLookup will return its value. The
user will get a warning, the Update will be canceled, and the focus will stay
in the control.

I would like to check for a duplicate entry and cancel the operation if the
user starts a new record with a duplicate ID. But I can't figure out how to
get the code to run. The field is the very first one for the record, so the
record doesn't exist until after the field has been exited. This seems to
mean that none of the usual events like AfterUpdate, Change, Exit, etc. get
triggered. The field is bound to a table. Should I make it unbound?

 

[Guest]

Never mind...I failed to set the AfterUpdate link in the Properties box!
--
Dave

My problem is not how to check for the duplicate but how to trigger the code.
The BeforeUpdate event doesn't occur. I am not sure why that is the case.
--
Dave

Assuming the user enters the ID in a control, use the control's Before Update
event.

If Not IsNull(DLook("[ID]", "MyTable", "[ID] = " & Me.txtID) Then
MsgBox "This ID is already in the table"
Cancel = True
End If

If the ID is found in the table, the DLookup will return its value. The
user will get a warning, the Update will be canceled, and the focus will stay
in the control.

I would like to check for a duplicate entry and cancel the operation if the
user starts a new record with a duplicate ID. But I can't figure out how to
get the code to run. The field is the very first one for the record, so the
record doesn't exist until after the field has been exited. This seems to
mean that none of the usual events like AfterUpdate, Change, Exit, etc. get
triggered. The field is bound to a table. Should I make it unbound?