Set required field to Null

[Georges Heinesch]

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

[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

[Georges Heinesch]

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

[Bruce M. Thompson]

> 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.<<

[Mike Storr]

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

[Georges Heinesch]

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

[phobos]

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?

[Bruce M. Thompson]

> >>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.<<

[Bas Cost Budde]

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

[Georges Heinesch]

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

[Georges Heinesch]

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

[Brendan Reynolds]

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

[Bas Cost Budde]

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

[Georges Heinesch]

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

[Georges Heinesch]

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

[Bas Cost Budde]

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

[Steve Jorgensen]

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?