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.V alue =
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]

Re: Set required field to Null

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!m yfield) then
myform!myfield = "defaultval ue"
end if



"Georges Heinesch" <void@void.co m> wrote in message
news:4033bfdd$1 _2@news.vo.lu.. .[color=blue]
> 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.V alue =
> 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[/color]

[Georges Heinesch]

Re: Set required field to Null

dp wrote:
[color=blue]
> 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!m yfield) then
> myform!myfield = "defaultval ue"
> end if[/color]

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

--
Georges

[Bruce M. Thompson]

Re: Set required field to Null

> 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
bthmpson@mvps.o rg (See the Access FAQ at http://www.mvps.org/access)[color=blue][color=green]
>> NO Email Please. Keep all communications[/color][/color]
within the newsgroups so that all might benefit.<<

[Mike Storr]

Re: Set required field to Null

On Wed, 18 Feb 2004 22:24:02 +0100, Georges Heinesch wrote:
[color=blue]
> dp wrote:
>[color=green]
>> 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!m yfield) then
>> myform!myfield = "defaultval ue"
>> end if[/color]
>
> Is there no solution using code while leaving the required status on?[/color]

It can be done as long as you don't try to leave the record.
--
Mike Storr

https://www.veraccess.com

[Georges Heinesch]

Re: Set required field to Null

Bruce M. Thompson wrote:
[color=blue][color=green]
>>Is there no solution using code while leaving the required status on?[/color]
>
> What value is the Required property if it isn't enforced?[/color]

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

Thanks.

--
Georges

[phobos]

Re: Set required field to Null

Georges Heinesch <void@void.co m> wrote in message news:<4034448d$ 1_1@news.vo.lu> ...[color=blue]
> Bruce M. Thompson wrote:
>[color=green][color=darkred]
> >>Is there no solution using code while leaving the required status on?[/color]
> >
> > What value is the Required property if it isn't enforced?[/color]
>
> Sorry, I don't get your question.
> Could you rephrase please.[/color]

'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]

Re: Set required field to Null

> >>Is there no solution using code while leaving the required status on?[color=blue][color=green]
> >
> > What value is the Required property if it isn't enforced?[/color]
>
> Sorry, I don't get your question.
> Could you rephrase please.[/color]

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
bthmpson@mvps.o rg (See the Access FAQ at http://www.mvps.org/access)[color=blue][color=green]
>> NO Email Please. Keep all communications[/color][/color]
within the newsgroups so that all might benefit.<<

[Bas Cost Budde]

Re: Set required field to Null

Georges Heinesch wrote:
[color=blue]
> 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.V alue =
> 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
>[/color]
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]

Re: Set required field to Null

phobos wrote:
[color=blue]
> '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?[/color]

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

--
Georges

[Georges Heinesch]

Re: Set required field to Null

Bruce M. Thompson wrote:
[color=blue]
> 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.[/color]

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]

Re: Set required field to Null

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 programmaticall y only when the record is saved.

--
Brendan Reynolds

"Georges Heinesch" <void@void.co m> wrote in message
news:4035a9a2_2 @news.vo.lu...[color=blue]
> Bruce M. Thompson wrote:
>[color=green]
> > From Access Help:
> >
> > "The Required property is enforced at the table level by the Microsoft[/color][/color]
Jet[color=blue][color=green]
> > database engine. If you set this property to Yes, the field must receive[/color][/color]
or[color=blue][color=green]
> > already contain a value when it has the focus - when a user enters data[/color][/color]
in a[color=blue][color=green]
> > table (or in a form or datasheet based on the table), when a macro or[/color][/color]
Visual[color=blue][color=green]
> > Basic sets the value of the field, or when data is imported into the[/color][/color]
table."[color=blue][color=green]
> >
> > In other words, if the "Required" property for a field at the table[/color][/color]
level is set[color=blue][color=green]
> > to "Yes", the jet database engine will refuse to save a record that is[/color][/color]
missing[color=blue][color=green]
> > data in that field ... period. If the record were allowed to be saved,[/color][/color]
then the[color=blue][color=green]
> > "Required" property would be of little consequence - it's there to[/color][/color]
prevent null[color=blue][color=green]
> > values in the related field.[/color]
>
> 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[/color]

[Bas Cost Budde]

Re: Set required field to Null

Georges Heinesch wrote:[color=blue]
> phobos wrote:
>[color=green]
>> '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?[/color]
>
>
> ... to temporarily delete the content of the control to make it visible
> for the user that this field still requires input.
>[/color]
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]

Re: Set required field to Null

Brendan Reynolds wrote:
[color=blue]
> 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 programmaticall y only when the record is saved.[/color]

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]

Re: Set required field to Null

Bas Cost Budde wrote:
[color=blue][color=green]
>> ... to temporarily delete the content of the control to make it
>> visible for the user that this field still requires input.[/color]
>
> Aah. Then what about a SetFocus (back) on the Exit event, unless the
> value makes sense?[/color]

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