By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Adding a validation rule to a form to stop duplicate records

dragonsbreath
P: 4
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
May 15 '07 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
Eliminate the equal sign preceding your expression. If that doesn't work, try changing your expression to the following:

Expand|Select|Wrap|Line Numbers
  1. nz(DLookUp("[org]","[Org_Sen]","[org]=" & [Form].[org]) ,0) = 0
May 15 '07 #2

dragonsbreath
P: 4
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?
May 16 '07 #3

Denburt
Expert 100+
P: 1,356
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.
May 16 '07 #4

dragonsbreath
P: 4
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.
May 16 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourCombobox_AfterUpdate()
  2. If Me!YourCombobox.Value = "" Then
  3. MsgBox "Please make a selection or Exit."
  4. Exit Sub
  5. End If
  6. End Sub
May 16 '07 #6

dragonsbreath
P: 4
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.
May 17 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.