Forcing entry to a text box

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

Hi. I have a form where users enter info in one set of
fields, then finish off by entering info in another two.
I've noticed that they regularly forget the last two,
which are prety crutial. Is it possible to stop the user
progressing to the next record, or closing the form, if
they have entered info into any one of the first set of
fields but forgotten to enter anything into both of the
last two? A popup message reminding them to complete the
last two fields would also be needed. Is this possible
and if so how would I go about it? Thanks, Noel
 
Hi,


Add a table validation rule, kind of:


(NOT Field1 IS Null) IMP (( NOT Field2 IS NULL) AND ( NOT Field3 IS
NULL))



which states that if field1 is not null, then neither field2 neither field3
can be null. A table validation follows the table every where, without
having to remember it. You may have to catch the error in the form onError
event.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks for this Michel. Im afraid I cant get this to
work. Ive put the following in the tables Validation Rule
properties box and it has no effect. I can still close
the form normally or move to the next record after
putting a value in the BEdYr1SBW field and leaving fields
Placements and/or PlacementStartYear blank.

(Not "BEdYr1SBW" Is Null) Imp ((Not "Placements" Is Null)
And (Not "PlacementStartYear" Is Null))

What am I doing wrong? Two other questions - how would
the code look if I wanted to force the user to input
values to Placements and PlacementStartYear once they
have put a value in, say Field1 or Field2 or Field3 etc.
Also what do you mean by You may have to catch the error
in the form onError?

Thanks for the help, Noel
 
Hi,


Sorry, I forgot about Access sometimes thinking we mean constants while
we want fields. Try:

(Not [BEdYr1SBW] Is Null) Imp ((Not [Placements] Is Null) And (Not
[PlacementStartYear] Is Null))



Actually, with the " ", Access uses constant, literally, and "Placements",
as a constant, is never null, so the validation expression always evaluates
to true. Using [ ] around the name forces Access to see it as field name,
not as constant, anymore.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel, that works a treat - exactly what I want.
I figured out how to use OR to string eight fields
together in the first half of the code to ensure that the
forced entry applies to all of them. Just that last point
about catching an error. I dont get one, is there
something else I need to do? Thanks again, Noel
-----Original Message-----
Hi,


Sorry, I forgot about Access sometimes thinking we mean constants while
we want fields. Try:

(Not [BEdYr1SBW] Is Null) Imp ((Not [Placements] Is Null) And (Not
[PlacementStartYear] Is Null))



Actually, with the " ", Access uses constant, literally, and "Placements",
as a constant, is never null, so the validation expression always evaluates
to true. Using [ ] around the name forces Access to see it as field name,
not as constant, anymore.



Hoping it may help,
Vanderghast, Access MVP



Thanks for this Michel. Im afraid I cant get this to
work. Ive put the following in the tables Validation Rule
properties box and it has no effect. I can still close
the form normally or move to the next record after
putting a value in the BEdYr1SBW field and leaving fields
Placements and/or PlacementStartYear blank.

(Not "BEdYr1SBW" Is Null) Imp ((Not "Placements" Is Null)
And (Not "PlacementStartYear" Is Null))

What am I doing wrong? Two other questions - how would
the code look if I wanted to force the user to input
values to Placements and PlacementStartYear once they
have put a value in, say Field1 or Field2 or Field3 etc.
Also what do you mean by You may have to catch the error
in the form onError?

Thanks for the help, Noel

NULL)
AND ( NOT Field3 IS every
where, without
Click to expand...


.
Click to expand...
 
Hi,


A record cannot be appended or modified in such a way that the
validation rule is not satisfied. If you add a record in the table,
directly, you should have an error while the validation rule does not return
True, when trying to save the record ( going into another record) . Under a
form, same thing should occur at the moment you try to save the record. If
you use

CurrentDb.Execute "INSERT ... "

then, the statement may fail silently. You have to add the optional
argument, dbFailOnError, to catch an error.


What is the statement you use with the OR? An OR returns TRUE if ANY
of its two parts evaluate to TRUE. It may occur that you don't get an error
because of that, because in your test, one of the two part was satisfied?



Vanderghast, Access MVP



Noel said:
Thanks Michel, that works a treat - exactly what I want.
I figured out how to use OR to string eight fields
together in the first half of the code to ensure that the
forced entry applies to all of them. Just that last point
about catching an error. I dont get one, is there
something else I need to do? Thanks again, Noel
-----Original Message-----
Hi,


Sorry, I forgot about Access sometimes thinking we mean constants while
we want fields. Try:

(Not [BEdYr1SBW] Is Null) Imp ((Not [Placements] Is Null) And (Not
[PlacementStartYear] Is Null))



Actually, with the " ", Access uses constant, literally, and "Placements",
as a constant, is never null, so the validation expression always evaluates
to true. Using [ ] around the name forces Access to see it as field name,
not as constant, anymore.



Hoping it may help,
Vanderghast, Access MVP



Thanks for this Michel. Im afraid I cant get this to
work. Ive put the following in the tables Validation Rule
properties box and it has no effect. I can still close
the form normally or move to the next record after
putting a value in the BEdYr1SBW field and leaving fields
Placements and/or PlacementStartYear blank.

(Not "BEdYr1SBW" Is Null) Imp ((Not "Placements" Is Null)
And (Not "PlacementStartYear" Is Null))

What am I doing wrong? Two other questions - how would
the code look if I wanted to force the user to input
values to Placements and PlacementStartYear once they
have put a value in, say Field1 or Field2 or Field3 etc.
Also what do you mean by You may have to catch the error
in the form onError?

Thanks for the help, Noel


-----Original Message-----
Hi,


Add a table validation rule, kind of:


(NOT Field1 IS Null) IMP (( NOT Field2 IS NULL)
AND ( NOT Field3 IS
NULL))



which states that if field1 is not null, then neither
field2 neither field3
can be null. A table validation follows the table every
where, without
having to remember it. You may have to catch the error
in the form onError
event.



Hoping it may help,
Vanderghast, Access MVP


message
Hi. I have a form where users enter info in one set of
fields, then finish off by entering info in another
two.
I've noticed that they regularly forget the last two,
which are prety crutial. Is it possible to stop the
user
progressing to the next record, or closing the form, if
they have entered info into any one of the first set of
fields but forgotten to enter anything into both of the
last two? A popup message reminding them to complete
the
last two fields would also be needed. Is this possible
and if so how would I go about it? Thanks, Noel


.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi, My Validation Rule code reads

((Not [BEdYr1SBW] Is Null) Or (Not [BEdYr1] Is Null) Or
(Not [BEdYr2] Is Null) Or (Not [BEdYr3] Is Null) Or (Not
[BEdFinal] Is Null) Or (Not [PGCEAutumn] Is Null) Or (Not
[PGCEFirst] Is Null) Or (Not [PGCEFinal] Is Null)) Imp
((Not [Placements] Is Null) And (Not [PlacementStartYear]
Is Null))

Apart from putting a message in the Validation text box,
thats all Ive done. It all works fine. If the user puts a
value in at least one of the fields mentioned in the OR
side, they get the message and cannot close the form or
move to the next record until they put a value in both
the Placements and PlacementStartYear fields. I can
create a new record OK. Im not sure what you mean about
CurrentDb.Execute "INSERT ... ". I havent knowingly used
that statement anywhere. Am I in the clear re error
messages? Thanks again for the help, Noel
-----Original Message-----
Hi,


A record cannot be appended or modified in such a way that the
validation rule is not satisfied. If you add a record in the table,
directly, you should have an error while the validation rule does not return
True, when trying to save the record ( going into another record) . Under a
form, same thing should occur at the moment you try to save the record. If
you use

CurrentDb.Execute "INSERT ... "

then, the statement may fail silently. You have to add the optional
argument, dbFailOnError, to catch an error.


What is the statement you use with the OR? An OR returns TRUE if ANY
of its two parts evaluate to TRUE. It may occur that you don't get an error
because of that, because in your test, one of the two part was satisfied?



Vanderghast, Access MVP



Thanks Michel, that works a treat - exactly what I want.
I figured out how to use OR to string eight fields
together in the first half of the code to ensure that the
forced entry applies to all of them. Just that last point
about catching an error. I dont get one, is there
something else I need to do? Thanks again, Noel
-----Original Message-----
Hi,


Sorry, I forgot about Access sometimes thinking we mean constants while
we want fields. Try:

(Not [BEdYr1SBW] Is Null) Imp ((Not [Placements] Is Null) And (Not
[PlacementStartYear] Is Null))



Actually, with the " ", Access uses constant, literally, and "Placements",
as a constant, is never null, so the validation expression always evaluates
to true. Using [ ] around the name forces Access to see it as field name,
not as constant, anymore.



Hoping it may help,
Vanderghast, Access MVP



Thanks for this Michel. Im afraid I cant get this to
work. Ive put the following in the tables Validation Rule
properties box and it has no effect. I can still close
the form normally or move to the next record after
putting a value in the BEdYr1SBW field and leaving fields
Placements and/or PlacementStartYear blank.

(Not "BEdYr1SBW" Is Null) Imp ((Not "Placements" Is Null)
And (Not "PlacementStartYear" Is Null))

What am I doing wrong? Two other questions - how would
the code look if I wanted to force the user to input
values to Placements and PlacementStartYear once they
have put a value in, say Field1 or Field2 or Field3 etc.
Also what do you mean by You may have to catch the error
in the form onError?

Thanks for the help, Noel


-----Original Message-----
Hi,


Add a table validation rule, kind of:


(NOT Field1 IS Null) IMP (( NOT Field2 IS NULL)
AND ( NOT Field3 IS
NULL))



which states that if field1 is not null, then neither
field2 neither field3
can be null. A table validation follows the table every
where, without
having to remember it. You may have to catch the error
in the form onError
event.



Hoping it may help,
Vanderghast, Access MVP


message
Hi. I have a form where users enter info in one
Click to expand...
set
of
fields, then finish off by entering info in another
two.
I've noticed that they regularly forget the last two,
which are prety crutial. Is it possible to stop the
user
progressing to the next record, or closing the form, if
they have entered info into any one of the first set of
fields but forgotten to enter anything into both
Click to expand...
of
the
last two? A popup message reminding them to complete
the
last two fields would also be needed. Is this possible
and if so how would I go about it? Thanks, Noel


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi,


About catching the error, is that generally, we are not "pleased"
with the error message Access produces, so, we have to catch the error to
modify it... but you have already done it right, it seems, with an
appropriate custom error message ( that is still perfectly "ok" for the user
using your form). You do not need to do anything more, in that situation.


CurrentDb.Execute " ... sql action statement here... " ,
dbFailOnError
allows us to execute any action-statement, such as appending a record
(without any user interface what so ever, from "pure" code, if you prefer).
If you ever have that need kind of functionality, do not forget the optional
flag, dbFailOnError, as shown, else, the code may fail, but your code won't
be aware of it!



Vanderghast, Access MVP


Noel said:
Hi, My Validation Rule code reads

((Not [BEdYr1SBW] Is Null) Or (Not [BEdYr1] Is Null) Or
(Not [BEdYr2] Is Null) Or (Not [BEdYr3] Is Null) Or (Not
[BEdFinal] Is Null) Or (Not [PGCEAutumn] Is Null) Or (Not
[PGCEFirst] Is Null) Or (Not [PGCEFinal] Is Null)) Imp
((Not [Placements] Is Null) And (Not [PlacementStartYear]
Is Null))

Apart from putting a message in the Validation text box,
thats all Ive done. It all works fine. If the user puts a
value in at least one of the fields mentioned in the OR
side, they get the message and cannot close the form or
move to the next record until they put a value in both
the Placements and PlacementStartYear fields. I can
create a new record OK. Im not sure what you mean about
CurrentDb.Execute "INSERT ... ". I havent knowingly used
that statement anywhere. Am I in the clear re error
messages? Thanks again for the help, Noel
-----Original Message-----
Hi,


A record cannot be appended or modified in such a way that the
validation rule is not satisfied. If you add a record in the table,
directly, you should have an error while the validation rule does not return
True, when trying to save the record ( going into another record) . Under a
form, same thing should occur at the moment you try to save the record. If
you use

CurrentDb.Execute "INSERT ... "

then, the statement may fail silently. You have to add the optional
argument, dbFailOnError, to catch an error.


What is the statement you use with the OR? An OR returns TRUE if ANY
of its two parts evaluate to TRUE. It may occur that you don't get an error
because of that, because in your test, one of the two part was satisfied?



Vanderghast, Access MVP



Thanks Michel, that works a treat - exactly what I want.
I figured out how to use OR to string eight fields
together in the first half of the code to ensure that the
forced entry applies to all of them. Just that last point
about catching an error. I dont get one, is there
something else I need to do? Thanks again, Noel

-----Original Message-----
Hi,


Sorry, I forgot about Access sometimes thinking we
mean constants while
we want fields. Try:

(Not [BEdYr1SBW] Is Null) Imp ((Not [Placements] Is
Null) And (Not
[PlacementStartYear] Is Null))



Actually, with the " ", Access uses constant,
literally, and "Placements",
as a constant, is never null, so the validation
expression always evaluates
to true. Using [ ] around the name forces Access to
see it as field name,
not as constant, anymore.



Hoping it may help,
Vanderghast, Access MVP



message
Thanks for this Michel. Im afraid I cant get this to
work. Ive put the following in the tables Validation
Rule
properties box and it has no effect. I can still close
the form normally or move to the next record after
putting a value in the BEdYr1SBW field and leaving
fields
Placements and/or PlacementStartYear blank.

(Not "BEdYr1SBW" Is Null) Imp ((Not "Placements" Is
Null)
And (Not "PlacementStartYear" Is Null))

What am I doing wrong? Two other questions - how would
the code look if I wanted to force the user to input
values to Placements and PlacementStartYear once they
have put a value in, say Field1 or Field2 or Field3
etc.
Also what do you mean by You may have to catch the
error
in the form onError?

Thanks for the help, Noel


-----Original Message-----
Hi,


Add a table validation rule, kind of:


(NOT Field1 IS Null) IMP (( NOT Field2 IS
NULL)
AND ( NOT Field3 IS
NULL))



which states that if field1 is not null, then neither
field2 neither field3
can be null. A table validation follows the table
every
where, without
having to remember it. You may have to catch the error
in the form onError
event.



Hoping it may help,
Vanderghast, Access MVP


message
Hi. I have a form where users enter info in one set
of
fields, then finish off by entering info in another
two.
I've noticed that they regularly forget the last
two,
which are prety crutial. Is it possible to stop the
user
progressing to the next record, or closing the
form, if
they have entered info into any one of the first
set of
fields but forgotten to enter anything into both of
the
last two? A popup message reminding them to complete
the
last two fields would also be needed. Is this
possible
and if so how would I go about it? Thanks, Noel


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Michel. Thanks for this and for all your help. Cheers,
Noel
-----Original Message-----
Hi,


About catching the error, is that generally, we are not "pleased"
with the error message Access produces, so, we have to catch the error to
modify it... but you have already done it right, it seems, with an
appropriate custom error message ( that is still perfectly "ok" for the user
using your form). You do not need to do anything more, in that situation.


CurrentDb.Execute " ... sql action statement here... " ,
dbFailOnError
allows us to execute any action-statement, such as appending a record
(without any user interface what so ever, from "pure" code, if you prefer).
If you ever have that need kind of functionality, do not forget the optional
flag, dbFailOnError, as shown, else, the code may fail, but your code won't
be aware of it!



Vanderghast, Access MVP


Hi, My Validation Rule code reads

((Not [BEdYr1SBW] Is Null) Or (Not [BEdYr1] Is Null) Or
(Not [BEdYr2] Is Null) Or (Not [BEdYr3] Is Null) Or (Not
[BEdFinal] Is Null) Or (Not [PGCEAutumn] Is Null) Or (Not
[PGCEFirst] Is Null) Or (Not [PGCEFinal] Is Null)) Imp
((Not [Placements] Is Null) And (Not [PlacementStartYear]
Is Null))

Apart from putting a message in the Validation text box,
thats all Ive done. It all works fine. If the user puts a
value in at least one of the fields mentioned in the OR
side, they get the message and cannot close the form or
move to the next record until they put a value in both
the Placements and PlacementStartYear fields. I can
create a new record OK. Im not sure what you mean about
CurrentDb.Execute "INSERT ... ". I havent knowingly used
that statement anywhere. Am I in the clear re error
messages? Thanks again for the help, Noel
-----Original Message-----
Hi,


A record cannot be appended or modified in such a way that the
validation rule is not satisfied. If you add a record
Click to expand...
in
the table,
directly, you should have an error while the
Click to expand...
validation
rule does not return
True, when trying to save the record ( going into another record) . Under a
form, same thing should occur at the moment you try to save the record. If
you use

CurrentDb.Execute "INSERT ... "

then, the statement may fail silently. You have to add the optional
argument, dbFailOnError, to catch an error.


What is the statement you use with the OR? An OR returns TRUE if ANY
of its two parts evaluate to TRUE. It may occur that
Click to expand...
you
don't get an error
because of that, because in your test, one of the two part was satisfied?



Vanderghast, Access MVP



Thanks Michel, that works a treat - exactly what I want.
I figured out how to use OR to string eight fields
together in the first half of the code to ensure
Click to expand...
that
the
forced entry applies to all of them. Just that last point
about catching an error. I dont get one, is there
something else I need to do? Thanks again, Noel

-----Original Message-----
Hi,


Sorry, I forgot about Access sometimes thinking we
mean constants while
we want fields. Try:

(Not [BEdYr1SBW] Is Null) Imp ((Not [Placements] Is
Null) And (Not
[PlacementStartYear] Is Null))



Actually, with the " ", Access uses constant,
literally, and "Placements",
as a constant, is never null, so the validation
expression always evaluates
to true. Using [ ] around the name forces Access to
see it as field name,
not as constant, anymore.



Hoping it may help,
Vanderghast, Access MVP



message
Thanks for this Michel. Im afraid I cant get this to
work. Ive put the following in the tables Validation
Rule
properties box and it has no effect. I can still close
the form normally or move to the next record after
putting a value in the BEdYr1SBW field and leaving
fields
Placements and/or PlacementStartYear blank.

(Not "BEdYr1SBW" Is Null) Imp ((Not "Placements" Is
Null)
And (Not "PlacementStartYear" Is Null))

What am I doing wrong? Two other questions - how would
the code look if I wanted to force the user to input
values to Placements and PlacementStartYear once they
have put a value in, say Field1 or Field2 or Field3
etc.
Also what do you mean by You may have to catch the
error
in the form onError?

Thanks for the help, Noel


-----Original Message-----
Hi,


Add a table validation rule, kind of:


(NOT Field1 IS Null) IMP (( NOT Field2 IS
NULL)
AND ( NOT Field3 IS
NULL))



which states that if field1 is not null, then neither
field2 neither field3
can be null. A table validation follows the table
every
where, without
having to remember it. You may have to catch the error
in the form onError
event.



Hoping it may help,
Vanderghast, Access MVP


"Noel" <[email protected]>
Click to expand...
wrote
in
message
Hi. I have a form where users enter info in
Click to expand...
one
set
of
fields, then finish off by entering info in another
two.
I've noticed that they regularly forget the last
two,
which are prety crutial. Is it possible to
Click to expand...
stop
the
user
progressing to the next record, or closing the
form, if
they have entered info into any one of the first
set of
fields but forgotten to enter anything into
Click to expand...
both
of
the
last two? A popup message reminding them to complete
the
last two fields would also be needed. Is this
possible
and if so how would I go about it? Thanks, Noel


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top