How do I override the "required" field via a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

keers - 17 Mar 2007 22:42 GMT

Hi,

I recently took over support of a database and have now been asked to
provide enhancements. The main form has approx 80 fields, the vast
majority of which are "Required" (the design of the table). The
enhancement requested is to provide a "qucik fill" form consisting of
about 20 fields (the user will have the ability to add to the record
at a later date). The problem, is that I am unable to save the record
as there are a number of "Required" fields missing. I am loathed to
change the design of the table as it will have ramifiactions
elsewhere.

I have considered the following options;

1) Temporarily alter the "Required" flag to off when the form opens
(and back on again when the form closes), but this doesn't work as the
table is open at the time (this is a subform)

2) Assign a value to the table field : Me![DelDateActual] =
"01/01/2006" via code when the form opens. the controls are not used
on the form, but i read somewhere that this was not necessary as long
as the recordsource for the form is the table - which it is. I am
getting error 2448.

Any suggestions gratefully received.

Thanks

Keers

Reply to this Message

Mr B - 17 Mar 2007 23:25 GMT

Keers,

It is most likely not a good idea to change the required fields in your
table. As you say, it could have serious ramifications with your data.

Also, I do not think that you can just programatically make changes to the
required condition in your table.

One consideration might be to have a temporary table where the new "quick
fill" type records can be placed. Then when your users are ready to actually
complete the actual record, just populate the new record with the data from
the temporary table.

I would take a little coding but I think it would work.

Signature

HTH

Mr B
email if needed to:
draccess at askdoctoraccess dot com

> Hi,
>
[quoted text clipped - 25 lines]
>
> Keers

Reply to this Message

Arvin Meyer [MVP] - 18 Mar 2007 02:12 GMT

I prefer to add a default value in the table design to any field that is
required. This isn't a great solution because the data is meaningless. Then
again, I only make fields required when they really are. I also write code
or queries to periodically check for the default values and inform me that
they are the default.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Hi,
>
[quoted text clipped - 25 lines]
>
> Keers

Reply to this Message

Allen Browne - 18 Mar 2007 02:24 GMT

Keers, it's good that you are aware that just setting Required to No could
have side-effects the developer did not intend. There are several, and some
would be hard to trace, such as the need to change inner join queries to
outer joins, or alter the criteria to include the Null case, or ending up
with extra lines (and therefore inflated totals) on a report, etc.

Re #1, even if you temporarily disable to Required flag (so as to add data
that breaks the rule) and then turn it on again, you still have all those
issues to trace and deal with.

Re #2, I don't understand the need to assign a value as soon as a form
opens. It might make sense to assign a DefaultValue to the control, or to
assign a value in Form_BeforeInsert (for new records), or in
Form_BeforeUpdate (for the latest possible moment before the save.) But
dirtying the record in Form_Open would be a really bad idea. If the form is
opened to an existing record, you overwrite the date? If it is filtered or
sorted differently, you overwrite the date in the wrong record?

And if this is a date/time field (not a Text field), use the # delimiters:
Me![DelDateActual] = #01/01/2006#

Hope that helps

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I recently took over support of a database and have now been asked to
> provide enhancements. The main form has approx 80 fields, the vast
[quoted text clipped - 23 lines]
>
> Keers

Reply to this Message

keers - 20 Mar 2007 23:17 GMT

> Keers, it's good that you are aware that just setting Required to No could
> have side-effects the developer did not intend. There are several, and some
[quoted text clipped - 53 lines]
>
> - Show quoted text -

Thanks to all who took the time to respond.

In light of the advice, i decided to opt for setting "default
values" (values that i can easily identify later) to the required
fields and this works fine for my needs. The comments made certaintly
helped my understanding of Access, particularly wrt dirtying records.

Thanks

Keers

Reply to this Message