Validation rule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I would like to use a validation rule on. The form is used to check that the correct sales rep gets credit for the sale. In the db I have a table that has all of the sales reps with the following fields. Last Name and Last 4.

On the sales form we use now (hard copy form) only the last 4 appear. In the Access program we have the last name of who we think the sales rep is. The last 4 field is left blank until the sale in final and we get the hardcopy of the sales agreement. When we get the hard copy we open the Access form for that sale and type in the last 4 that is on the hard copy.

We then lookup the last 4 in the sales Rep table and check that the Last name and last 4 match. On occasion we will find one that they do not match.

I would like to have it so that when the user types in the last 4 from the hard copy the validation rule would go to the sales rep table and get the matching last 4 and last name then check the last name agents the last name that aperies on the current form that the user just typed in last 4 into. If it does not match then the user would get a Msg Bx that would tell him/her that the last for and last mane do not match.

Can I do this? Thanks.
 
KAnoe,

May I be so bold as to suggesat an alternative approach. For a number
of reasons, it is not a good idea to use both the Last Name and Last 4
fields in the Sales table. Instead, I would remove the Last Name field
from the table, and just use the Last 4 field to identify the sales rep
(assuming the entries in Last 4 are unique). Then, on your Sales form,
you can put a combobox for the Last 4 field, and this combobox can have
as its Row Source the Sales Rep table, and be set to show 2 columns,
i.e. Last 4 and Last Name. If you like you can also have ther Last Name
appear on the form, but it will not be bound to a field in the Sales
table. There are 3 commonly used methods of achieving this:
- Base your Sales form on a query which includes the Sales Rep table,
joined to the Sales table on the Last 4 field, and then you can put the
Last Name field from Sales Rep into the query and hence onto the form.
- Use an unbound textbox on the form, with its Control Source set to...
=[Last 4].[Column](1)
- Use an unbound textbox on the form, with its Control Source set to...
=DLookup("[Last Name]","Sales Reps","[Last 4]='" & [Last 4] & "'")
Then, when the sale is confirmed, all you need to do is check the entry
of the sales rep for the sale, and if incorrect simply re-enter the
correct rep from the combobox.
 
Back
Top