Adding a validation rule to a form to stop duplicate records

[dragonsbreath]

Firstly I am not a coder.

I am trying to add a validation rule to an input form that will prevent users’ creating more than one record per organisation.

This DLookUp seems to work

=DLookUp("[org]","[Org_Sen]","[org]=" & [Form].[org]) Is Null

If the user tries to select a organisation that already has a record the validation text is displayed so far so good - but when the user deletes the entry and tries to close the form I get a syntax error “(missing operator) in the query expression”

any ideas what I am doing wrong

[puppydogbuddy]

Eliminate the equal sign preceding your expression. If that doesn't work, try changing your expression to the following:

nz(DLookUp("[org]","[Org_Sen]","[org]=" & [Form].[org]) ,0) = 0

[dragonsbreath]

Thanks for the advice but it is still not working.

I think I need to clarify -

I am trying to add a validation rule to a dropdown input box on a form that will bring up an error message if the user tries to select an organisation that already has a record.

I have tried putting the above validation code in the property box of the field on the form and it works to some extent, it will not allow a user to add a record for an organisation that already exists, and a pop up message does appear. If the user selects another organisation that does not have a record then no problem, however if the user tries to delete the selection and close the form I get a syntax error.

I want the user to be able to delete the selection and exit the form - any ideas?

[Denburt]

Since you have validation rules in place there is never a need to delete any record since a record can not be created if it violates the rule. Try using Undo to release any fields that are bound with data that should resolve the issue.

[dragonsbreath]

I think I have the syntax wrong in the rule, as from the dropdown you select a duplicate organisation the error message pops up and if you try and do anything with the duplicate organisation in field I get the error message. If I delete the selection and click on undo or exit I get a syntax error.

[puppydogbuddy]

Ok, when a previous selection from the combobox is deleted (blanked out), the combobox control is empty ("") rather than null, and therefore is not covered by your validation rule. There are several ways you can handle this. I would try putting the following code in the AfterUpdate event of your combobox.

Private Sub YourCombobox_AfterUpdate()
If Me!YourCombobox.Value = "" Then
MsgBox "Please make a selection or Exit."
Exit Sub
End If
End Sub

[dragonsbreath]

Thanks that worked.

I had to simplify my validation rule to:

=DLookUp("[org]","[Org_Sen]") Is Null

that with the above code solved my problem.