Validation Question

[AZSteve]

Access 2003, existing DB, existing Form, no validation. Employee ID textbox
at top of form - I want Employee ID text box to verify that the 6-digit
number entered matches an entry in the All Employee Info table (keyed on
Employee ID) before the use is able to go to another box.

In Data tab of Properties, Validation Rule, I entered using Expression
Builder>
=Str([All Employee Info]![Employee Number])
(I used Str because it appears entries are numbers on form but text in the
table, but it also doesn't work without Str)

I also put "Does not match existing employee number" the Validation Text.
Enabled Yes, Locked No.

I tried with bad employee numbers. Instead of my error message I get
Microsoft error messages "The expression [All Employee Info] you entered in
the form control's ValidationRule property contains the error The object
doesn't contain the Automation object 'All Employee Info'. When I
acknowledge that I get another Microsoft error message "The value in the
field or record violates the validation rule for the record or field."

Based on another thread in this discussion group I entered this into the VB
Code for this form>

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2116 Then
Response = acDataErrContinue
Else
MsgBox DataErr & " " & Response
End If
End Sub

and also tried the other error numbers suggested there. Then I got little
boxes with other numbers like "2469 1" or "2169 1", so I tried those 4-digit
numbers in the code too. I always eventually got back to the Microsoft
messages described above, never my error message.

I also tried with good Employee numbers and got the same results.

This is my first attempt at data validation in a form. Please help. And
while you are at it, how can I put the Employee Name (also in the All
Employee Info table) next to the Employee Number on the form if there is a
good match?

 

[KARL DEWEY]

Why not use a List box or Combo box with Auto Expand and Limit to List?

 

[Jeff Boyce]

I'll echo Karl's sentiments...

Why force the users to know/remember/enter codes/ids when you can simply
list the Employees who ARE valid?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AZSteve]

The correct info is usually in front of them and therefore they get it right.
I want to catch the occasional mistakes. I find it frustrating to use list
boxes when there are more than about 30 choices, and we have 100-200,
depending on the time of year.

I'll echo Karl's sentiments...

Why force the users to know/remember/enter codes/ids when you can simply
list the Employees who ARE valid?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access 2003, existing DB, existing Form, no validation. Employee ID
textbox
at top of form - I want Employee ID text box to verify that the 6-digit
number entered matches an entry in the All Employee Info table (keyed on
Employee ID) before the use is able to go to another box.

In Data tab of Properties, Validation Rule, I entered using Expression
Builder>
=Str([All Employee Info]![Employee Number])
(I used Str because it appears entries are numbers on form but text in the
table, but it also doesn't work without Str)

I also put "Does not match existing employee number" the Validation Text.
Enabled Yes, Locked No.

I tried with bad employee numbers. Instead of my error message I get
Microsoft error messages "The expression [All Employee Info] you entered
in
the form control's ValidationRule property contains the error The object
doesn't contain the Automation object 'All Employee Info'. When I
acknowledge that I get another Microsoft error message "The value in the
field or record violates the validation rule for the record or field."

Based on another thread in this discussion group I entered this into the
VB
Code for this form>

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2116 Then
Response = acDataErrContinue
Else
MsgBox DataErr & " " & Response
End If
End Sub

and also tried the other error numbers suggested there. Then I got little
boxes with other numbers like "2469 1" or "2169 1", so I tried those
4-digit
numbers in the code too. I always eventually got back to the Microsoft
messages described above, never my error message.

I also tried with good Employee numbers and got the same results.

This is my first attempt at data validation in a form. Please help. And
while you are at it, how can I put the Employee Name (also in the All
Employee Info table) next to the Employee Number on the form if there is a
good match?

 

[Jeff Boyce]

Listboxes serve well, when your screen has the available space and the list
is less than 10-12. Longer lists? Use a combobox instead!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The correct info is usually in front of them and therefore they get it
right.
I want to catch the occasional mistakes. I find it frustrating to use
list
boxes when there are more than about 30 choices, and we have 100-200,
depending on the time of year.

I'll echo Karl's sentiments...

Why force the users to know/remember/enter codes/ids when you can simply
list the Employees who ARE valid?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access 2003, existing DB, existing Form, no validation. Employee ID
textbox
at top of form - I want Employee ID text box to verify that the 6-digit
number entered matches an entry in the All Employee Info table (keyed
on
Employee ID) before the use is able to go to another box.

In Data tab of Properties, Validation Rule, I entered using Expression
Builder>
=Str([All Employee Info]![Employee Number])
(I used Str because it appears entries are numbers on form but text in
the
table, but it also doesn't work without Str)

I also put "Does not match existing employee number" the Validation
Text.
Enabled Yes, Locked No.

I tried with bad employee numbers. Instead of my error message I get
Microsoft error messages "The expression [All Employee Info] you
entered
in
the form control's ValidationRule property contains the error The
object
doesn't contain the Automation object 'All Employee Info'. When I
acknowledge that I get another Microsoft error message "The value in
the
field or record violates the validation rule for the record or field."

Based on another thread in this discussion group I entered this into
the
VB
Code for this form>

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2116 Then
Response = acDataErrContinue
Else
MsgBox DataErr & " " & Response
End If
End Sub

and also tried the other error numbers suggested there. Then I got
little
boxes with other numbers like "2469 1" or "2169 1", so I tried those
4-digit
numbers in the code too. I always eventually got back to the Microsoft
messages described above, never my error message.

I also tried with good Employee numbers and got the same results.

This is my first attempt at data validation in a form. Please help.
And
while you are at it, how can I put the Employee Name (also in the All
Employee Info table) next to the Employee Number on the form if there
is a
good match?