Sort of Validation Help

E

essseeproductions

Hi I was wondering if it was possible to do the following:
The person entering data into the table has to enter very important
data, therefore i was wondering if on entering the data maybe a box
pops up asking the user to enter it again, and if data matches then it
enters sucessfully into the table however if not an error message
appears.
If not is there any other way because i want to minimize human error!!
Many thanks in advance.
 
G

GregTarr

Hi I was wondering if it was possible to do the following:
The person entering data into the table has to enter very important
data, therefore i was wondering if on entering the data maybe a box
pops up asking the user to enter it again, and if data matches then it
enters sucessfully into the table however if not an error message
appears.
If not is there any other way because i want to minimize human error!!
Many thanks in advance.
Click to expand...

Hello.

The best way to do this is to define a "Validation rule" in the table.
To do this, open the table in Design View and click on the field that
is important. You will see a list of properties below, one of which
will be the Validation rule.

Have a look at http://office.microsoft.com/en-us/access/HA100963121033.aspx
for some examples of what rules you can define.

Hope it works, Greg
 
K

Ken Sheridan

The first thing you should examine is whether the data input can be
controlled by referential integrity. If for instance the data is a company
name in a customers column in an orders table and you want to ensure that
only a valid company name is entered then you should have a Customers table
with one row per customer and enforce referential integrity so that only
values which exist in the customers table can be entered in the Orders table.
In fact a numeric CustomerID foreign key column referencing a numeric
CustomerID primary key of the Customers table would be better as names are
not necessarily unique. Data entry of the foreign key value would normally
be via selection form a combo box's list.

If you can't enforce a valid value by referential integrity then a
validation rule might suffice if you can devise a suitable expression which
ensures that the data entered satisfies the rule.

If the validation criteria are too complex to handle it easily with a
validation rule then you might be able to put some validation code in the
BeforeUpdate event procedure of the control on your data input form (data
should only ever be entered via a form, never in raw datasheet view). If the
value entered does not meet the validation criteria set the return value of
the Cancel argument to True.

If you can't validate the data at the first time of entry and want to adopt
your 'double entry' approach then you could try putting the following code in
the control's BeforeUpdate event procedure. It uses Static variables to keep
track of what's been entered and whether it’s the first time or not:

Static i As Integer
Static strVal As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
If i = 0 Then
i = 1
strVal = ctrl
MsgBox "Please enter data again.", vbInformation, "Confirm"
Cancel = True
ctrl.Undo
Else
i = 0
If ctrl <> strVal Then
MsgBox "Values entered do not match. Try again",
vbExclamation, "Warning"
Cancel = True
ctrl.Undo
End If
End If
End If

This will carry on forcing the user to enter the value until the same value
has been entered twice in succession.

Ken Sheridan
Stafford, England
 
E

essseeproductions

Many thanks for your help.
I have added validation to my table, however i am still interested in
the double entry procedure but have not been sucessful in implementing
it as of yet.
Ken would i enter the above code in a new Module, if so would i just
copy and paste it as so or would i need to add function and end
function tags. Also are there any variables in it that need to be
edited? I did try to do this however on Debug it says invalid use of
Me keyword, so i guess i must be doing something wrong. Also i do not
know how to implement it into the form.
Many thanks again.
 
K

Ken Sheridan

The code is entered in the BeforeUpdate event procedure of the control bound
to the field in question on the data entry form. In form design view select
the control and open its properties sheet. Select the BeforeUpdate event and
click on the 'build' button (the one on the right with 3 dots). In the next
dialogue select Code Builder. When the VBA window opens it will be at the
control's BeforeUpdate event procedure, and you enter the code between the
two lines already in place. You don't need to edit the code at all, unless
you want to change the text for the messages to something more appropriate
for the context.

Ken Sheridan
Stafford, England
 
E

essseeproductions

Oh right thanks for clearing that up. There's just one problem, the
"MsgBox "Values entered do not match. Try again","
line comes up in red and the error states that it expected an error. I
saved it as it was anyway however it did not work. Any suggestions to
why this line may be erronous?
 
K

Ken Sheridan

Something you need to be wary of when code is posted here; a long line will
be split over two lines in your newsgroup reader. The whole of the following
should be entered as a single line in the procedure:

MsgBox "Values entered do not match. Try again", vbExclamation, "Warning"

or for better readability you can split long lines over two or more lines
using the underscore continuation character. When doing this I usually
indent the subsequent lines like so:

MsgBox "Values entered do not match. Try again", _
vbExclamation, "Warning"

Ken Sheridan
Stafford, England
 
Top