By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,900 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,900 IT Pros & Developers. It's quick & easy.

Set required field to Null

P: n/a
Hi.

My form contains a control (cboFooBar), which has an underlying field
with the "Required" property set to "Yes". Now, while filling out all
the controls of the form, I have to fill out this particular (required)
control as well.

However further down the form, there is another control, which has to
(under certain conditions) to delete again the content of the (required)
control just filled out before (cboFooBar). This is done by code.

However here comes the problem ... I cannot use "Me!cboFooBar.Value =
Null" since the "Required" property is set to "Yes". Access gives the
corresponding error message (logical!).

Now comes the question ... how can I delete a field, which has the
"Required" property set to "Yes".

TIA

--
Georges
Nov 12 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
dp
I'd turn the required status of that field off, and control the value of
that field programatically..

on myfield_exit() ' or maybe afterupdate..
if isnull(myform!myfield) then
myform!myfield = "defaultvalue"
end if

"Georges Heinesch" <vo**@void.com> wrote in message
news:40**********@news.vo.lu...
Hi.

My form contains a control (cboFooBar), which has an underlying field
with the "Required" property set to "Yes". Now, while filling out all
the controls of the form, I have to fill out this particular (required)
control as well.

However further down the form, there is another control, which has to
(under certain conditions) to delete again the content of the (required)
control just filled out before (cboFooBar). This is done by code.

However here comes the problem ... I cannot use "Me!cboFooBar.Value =
Null" since the "Required" property is set to "Yes". Access gives the
corresponding error message (logical!).

Now comes the question ... how can I delete a field, which has the
"Required" property set to "Yes".

TIA

--
Georges

Nov 12 '05 #2

P: n/a
dp wrote:
I'd turn the required status of that field off, and control the value of
that field programatically..

on myfield_exit() ' or maybe afterupdate..
if isnull(myform!myfield) then
myform!myfield = "defaultvalue"
end if


Is there no solution using code while leaving the required status on?

--
Georges
Nov 12 '05 #3

P: n/a
> Is there no solution using code while leaving the required status on?

What value is the Required property if it isn't enforced?

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #4

P: n/a
On Wed, 18 Feb 2004 22:24:02 +0100, Georges Heinesch wrote:
dp wrote:
I'd turn the required status of that field off, and control the value of
that field programatically..

on myfield_exit() ' or maybe afterupdate..
if isnull(myform!myfield) then
myform!myfield = "defaultvalue"
end if


Is there no solution using code while leaving the required status on?


It can be done as long as you don't try to leave the record.
--
Mike Storr
www.veraccess.com
Nov 12 '05 #5

P: n/a
Bruce M. Thompson wrote:
Is there no solution using code while leaving the required status on?


What value is the Required property if it isn't enforced?


Sorry, I don't get your question.
Could you rephrase please.

Thanks.

--
Georges
Nov 12 '05 #6

P: n/a
Georges Heinesch <vo**@void.com> wrote in message news:<40**********@news.vo.lu>...
Bruce M. Thompson wrote:
Is there no solution using code while leaving the required status on?


What value is the Required property if it isn't enforced?


Sorry, I don't get your question.
Could you rephrase please.


'Required' means that you're not allowed to put a null in that field.
So... if you want nulls in that field, why set it to 'Required' at
all?
Nov 12 '05 #7

P: n/a
> >>Is there no solution using code while leaving the required status on?

What value is the Required property if it isn't enforced?


Sorry, I don't get your question.
Could you rephrase please.


From Access Help:

"The Required property is enforced at the table level by the Microsoft Jet
database engine. If you set this property to Yes, the field must receive or
already contain a value when it has the focus - when a user enters data in a
table (or in a form or datasheet based on the table), when a macro or Visual
Basic sets the value of the field, or when data is imported into the table."

In other words, if the "Required" property for a field at the table level is set
to "Yes", the jet database engine will refuse to save a record that is missing
data in that field ... period. If the record were allowed to be saved, then the
"Required" property would be of little consequence - it's there to prevent null
values in the related field.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #8

P: n/a
Georges Heinesch wrote:
Hi.

My form contains a control (cboFooBar), which has an underlying field
with the "Required" property set to "Yes". Now, while filling out all
the controls of the form, I have to fill out this particular (required)
control as well.

However further down the form, there is another control, which has to
(under certain conditions) to delete again the content of the (required)
control just filled out before (cboFooBar). This is done by code.

However here comes the problem ... I cannot use "Me!cboFooBar.Value =
Null" since the "Required" property is set to "Yes". Access gives the
corresponding error message (logical!).

Now comes the question ... how can I delete a field, which has the
"Required" property set to "Yes".

TIA

If that other field has this special value allowing the Null int FooBar,
is it still possible to have a value at all there? I mean, you can just
leave the value in FooBar since the signalling value in the other field
is present. Querying and reporting can filter on that.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #9

P: n/a
phobos wrote:
'Required' means that you're not allowed to put a null in that field.
So... if you want nulls in that field, why set it to 'Required' at
all?


.... to temporarily delete the content of the control to make it visible
for the user that this field still requires input.

--
Georges
Nov 12 '05 #10

P: n/a
Bruce M. Thompson wrote:
From Access Help:

"The Required property is enforced at the table level by the Microsoft Jet
database engine. If you set this property to Yes, the field must receive or
already contain a value when it has the focus - when a user enters data in a
table (or in a form or datasheet based on the table), when a macro or Visual
Basic sets the value of the field, or when data is imported into the table."

In other words, if the "Required" property for a field at the table level is set
to "Yes", the jet database engine will refuse to save a record that is missing
data in that field ... period. If the record were allowed to be saved, then the
"Required" property would be of little consequence - it's there to prevent null
values in the related field.


I don't want to save the record with Null in this field, I only would
like to temporarily delete the field in order to emphasize to the user
the empty field (make it visually more obvious). Sure, before the record
is saved, this value needs to be set.

--
Georges
Nov 12 '05 #11

P: n/a
Can't be done, Georges. The Required rule is enforced as soon as the value
of the control is changed, JET doesn't wait until you try to save the
record. If the Required property is True, you can not assign a Null value to
that field. You will need to either a) change the Required property to
False, b) find another way of indicating to the user that they need to
change the value or c) use an unbound control, and assign the value to the
field programmatically only when the record is saved.

--
Brendan Reynolds

"Georges Heinesch" <vo**@void.com> wrote in message
news:40********@news.vo.lu...
Bruce M. Thompson wrote:
From Access Help:

"The Required property is enforced at the table level by the Microsoft Jet database engine. If you set this property to Yes, the field must receive or already contain a value when it has the focus - when a user enters data in a table (or in a form or datasheet based on the table), when a macro or Visual Basic sets the value of the field, or when data is imported into the table."
In other words, if the "Required" property for a field at the table level is set to "Yes", the jet database engine will refuse to save a record that is missing data in that field ... period. If the record were allowed to be saved, then the "Required" property would be of little consequence - it's there to prevent null values in the related field.


I don't want to save the record with Null in this field, I only would
like to temporarily delete the field in order to emphasize to the user
the empty field (make it visually more obvious). Sure, before the record
is saved, this value needs to be set.

--
Georges

Nov 12 '05 #12

P: n/a
Georges Heinesch wrote:
phobos wrote:
'Required' means that you're not allowed to put a null in that field.
So... if you want nulls in that field, why set it to 'Required' at
all?

... to temporarily delete the content of the control to make it visible
for the user that this field still requires input.

Aah. Then what about a SetFocus (back) on the Exit event, unless the
value makes sense?

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #13

P: n/a
Brendan Reynolds wrote:
Can't be done, Georges. The Required rule is enforced as soon as the value
of the control is changed, JET doesn't wait until you try to save the
record. If the Required property is True, you can not assign a Null value to
that field. You will need to either a) change the Required property to
False, b) find another way of indicating to the user that they need to
change the value or c) use an unbound control, and assign the value to the
field programmatically only when the record is saved.


a) not possible due to User Security features enabled.
b) possible with background color, however not in line with the
intuitive behaviour of the rest of the forms.
c) an option! gonna give that one a try.

Thanks a lot!

--
Georges
Nov 12 '05 #14

P: n/a
Bas Cost Budde wrote:
... to temporarily delete the content of the control to make it
visible for the user that this field still requires input.


Aah. Then what about a SetFocus (back) on the Exit event, unless the
value makes sense?


Not sure if I get what you mean ... is it that you suggest to leave the
required property off, and to check the value of the control using the
exit event? Sorry.

--
Georges
Nov 12 '05 #15

P: n/a
Georges Heinesch wrote:
Bas Cost Budde wrote:
... to temporarily delete the content of the control to make it
visible for the user that this field still requires input.

Aah. Then what about a SetFocus (back) on the Exit event, unless the
value makes sense?

Not sure if I get what you mean ... is it that you suggest to leave the
required property off, and to check the value of the control using the
exit event? Sorry.


No problem, I'm very unclear at times.

I wasn't even thinking of a specific setting for Required, but
circumventing the Null in the first field. You don't have to set that to
Null if you can confine the user there until input is valid.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #16

P: n/a
On Wed, 18 Feb 2004 20:41:17 +0100, Georges Heinesch <vo**@void.com> wrote:
Hi.

My form contains a control (cboFooBar), which has an underlying field
with the "Required" property set to "Yes". Now, while filling out all
the controls of the form, I have to fill out this particular (required)
control as well.

However further down the form, there is another control, which has to
(under certain conditions) to delete again the content of the (required)
control just filled out before (cboFooBar). This is done by code.

However here comes the problem ... I cannot use "Me!cboFooBar.Value =
Null" since the "Required" property is set to "Yes". Access gives the
corresponding error message (logical!).

Now comes the question ... how can I delete a field, which has the
"Required" property set to "Yes".

TIA


I've been following this thread with interest because I recently had a similar
situation. I have basically a category/subcategory relationship on a form.
The category is not bound because it is simply used to filter the subcategory,
but if a subcategory is already chosen, and the user changes the category, I
want to null out the subcategory because it already appears to be null since
there's no longer a match in the subcategory combo's rowsource, and we don't
want to let the record get saved with the old subcategory ID value after a new
category has been chosen.

Basically, I found 3 possible solutions.

1. Make the control unbound. Set the control's value by copying from an
invisible bound control in the Form_Current handler, and don't update the
bound control from the unbound control until the Form_BeforeUpdate handler.
Of course, that's still a problem if there's some other save error, and the
user remains in edit mode.

2. Only display categories that have one or more subcategories, and
automatically pick the first subcategory when the category is chosen. This
might be slightly dangerous in terms of not forcing the user to explicitly
choose a subcategory, but it's not terrible. Also, some categories might have
only one category, and in this case, it's a nice, time-saving feature.

3. Cache all the control values, undo the record, and write back all the
values except the one that should now be null bending entry by the user. This
only helps on Add, not edit, and it's also in the "you must be joking"
category in terms of potential side effects, so that's a big no.

I think number 2 works out the best for my situation. Does this help in
yours?
Nov 12 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.