DLookUp

[Lyncroft]

I've got a DLookUp on a form which prevents a user adding a name that's already in there. Each record also has an autonumber ID.

When a duplicate is found the user has to exit. However, I've noticed that because of the autonumber ID field a record is still created in the underlying table. This record is empty apart from the ID field. Can I prevent this?

This is my code

If Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation]='" & Me![Organisation] & "'")) Then
MsgBox "That Organisation already exists. Return to Organisation Search and try again", vbCritical
Me.Organisation.Undo
Me.ID.Undo
Cancel = True
End If

End Sub

 

[Elana]

I'm assuming you're placing this code on the BeforeUpdate event of the form?

If yes, then you need only to add "Exit Sub" after your "cancel = true" statement

You don't need to "undo" the other two fields as the record will not update.


HTH
E

 

[Lyncroft]

Thanks Elana.

I put in ;

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Probably not quite the done thing but it seems to work!!! Bit hotch potch my databases!

Cheers again