Hello,
I have a field on a form that will be filled out by a user. I need to have the ability to run the data that is input in the field against a query to see if there is a match. If there is a match, I need it to show an error message and require the user to enter new data.
For example, field name is Phone Number.
I have a query for all of the phone numbers that were entered in the original table.
After updating the phone number field, I need to have the input phone number look against the phone number query to see if it exists.
If it exists, then an error message needs to come up requiring a different input.
If it does not exist, nothing happens.
Right now, I have an 'After Update' macro on the phone number field in the form. I have an If statement that reads:
If [Forms]![Customer Form]![Phone Number] = [Phone Number Lookup]![Phone Number] Then
MessageBox
Message: You have entered a duplicate phone number!
Beep: Yes
Type: Warning!
Title: Duplicate Phone Number
End If
When I try to execute this, the error message that I receive is the following:
Microsoft Access cannot find the name 'Phone Number Lookup' you entered in the expression.
You may have specified a control that wasn't on the current object without specifying the correct form or report context.
To refer to a control on another form or report, precede the control name with the name of a collection. This is usually either Forms or Reports, and the name of the form or report to which the control belongs. For example, Forms![Products]![Units In Stock].
Is it because that I can't run an if statement against a query? If not, is there another way I can accomplish this. (FYI: I cannot just adjust the index property in the database to say no duplicates, I need to know if it's a duplicate as soon as data is entered in the field before the rest of the form is filled out)
Thank you in advance.
Cory