What's the best way to do this?

[Guest]

I have a form with 4 key fields, depending upon the field value in 1st field
user can choose values in the 2nd field, then the combination of the 1st two
fields provides list of values for the 3rd and fourth fields.

If users enter the 1st field and then select the 2nd, 3rd and 4th. Later on if
they change their mind, they may goto the the 1st field and select
a different value. In that case, the rest of the 3 fields should be
initialized and
reselected.

I wrote a change event for 1st field and tried to assign null as follows -

Me.DepartmentNO = NULL
Me.SubdepartmentNO = NULL
Me.EmployeeNo = NULL

I got error, then I tried to assing value as Me.DepartmentNo =" "
still I get error.

How can I initialize fields?

Thanks for your help!
-Me

 

[Marshall Barton]

I have a form with 4 key fields, depending upon the field value in 1st field
user can choose values in the 2nd field, then the combination of the 1st two
fields provides list of values for the 3rd and fourth fields.

If users enter the 1st field and then select the 2nd, 3rd and 4th. Later on if
they change their mind, they may goto the the 1st field and select
a different value. In that case, the rest of the 3 fields should be
initialized and
reselected.

I wrote a change event for 1st field and tried to assign null as follows -

Me.DepartmentNO = NULL
Me.SubdepartmentNO = NULL
Me.EmployeeNo = NULL

That's the common way to reset the combo box values.
Unfortunately, with some more clues, such as the error
message, I can not do more than guess why it's not working.

Perhaps there is a validation rule (including the Required
property) that prevents null values??

 

[Guest]

Hi Marshall,

There isn't any validation except that these fields are linked to sqlserver
database table where the required property is enforced. I fail to understand
why it should be enforced at the time of data entry as opposed to commit time.

Other than that I only have
<fieldname>_After_update() and <field>_Enter() events
with Me.<fieldname>.Requery

Please help me asap.

Thank you,
-Me

 

[Marshall Barton]

The Required property means that the fields are not allowed
to contain a Null value. How this is enforced via SQL
Server is beyond me, but it is standard Access behavior, so
I think that explains the error message.

I think the question now becomes how to achieve the desired
effect when you are not allowed to place invalid values in
the bound controls. The obvious thing to do is to remove
the Required property from those fields, but you probably
don't want to or are not allowed to do that.

How about making those three combo boxes unbound and using
the form's BeforeUpdate event to check if they have been
correctly filled in and, if so, copy their values to their
corresponding fields. Just make sure the combo boxes have a
different name than the fields they relate to.

 

[Guest]

Marshall,

Thanks, even I was thinking on the same lines. I was hoping someone would
know another way, but perhaps there isn't any.

Appreciate your help!

-Me

The Required property means that the fields are not allowed
to contain a Null value. How this is enforced via SQL
Server is beyond me, but it is standard Access behavior, so
I think that explains the error message.

I think the question now becomes how to achieve the desired
effect when you are not allowed to place invalid values in
the bound controls. The obvious thing to do is to remove
the Required property from those fields, but you probably
don't want to or are not allowed to do that.

How about making those three combo boxes unbound and using
the form's BeforeUpdate event to check if they have been
correctly filled in and, if so, copy their values to their
corresponding fields. Just make sure the combo boxes have a
different name than the fields they relate to.
--
Marsh
MVP [MS Access]

There isn't any validation except that these fields are linked to sqlserver
database table where the required property is enforced. I fail to understand
why it should be enforced at the time of data entry as opposed to commit time.

Other than that I only have
<fieldname>_After_update() and <field>_Enter() events
with Me.<fieldname>.Requery