Cannot contain a Null value

[Guest]

Hello. I have a combo box which is used to select a member's name (this is on
a subform). All goes well if the user selects a name. However, if the user
clicks on the combo field and then decides not to select a name, there is a
Access message:

The field 'ReadResults.ContactID' cannot contain a Null value because the
required property is set to True. Enter a value in this field.

And then the user MUST select something in this field and then delete it.
Once you touch the combo box field, you just can't get out it unless you
select something. Extremely frustrating.

Worse, there are 2 combo boxes on the subform and I get the same error for
'ReadResults.AnimalID'. I don't have many fields required but I do need both
of these.

I would appreciate help in setting up a message box saying the field is
required, with a yes/no and clearing the field so the user can move on. Maybe
something along this line, but not sure how to trigger the message box
appropriately.

If vbNo = MsgBox("This is a required field. Are you sure you don't want to
enter a member name?", vbQuestion + vbYesNo)

Cancel = True

Thanks for your time!

 

[Jeff Boyce]

Stephanie

I'm confused. You state that these are required fields. That means you've
told Access that there must be a value in the field.

But you also state that you want a way for the user to decide not to put
anything in the field. If that's true, then it isn't a "required" field, is
it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Sorry for the confusion.
I want to give the user the opportunity to say "oops, I didn't mean to
select a name here" and gracefully exit.

I have a main form that lists the Registration Date and then the subform
combo boxes allow the user to select the Members and Animals that are
associated with that registration date.

As it is now, if the user selects a name and then decides they are in the
wrong registration date, the user can't just clear the member's name out of
the subform combo box field and move to the next registration date (on the
main form) to enter the user's name (in the subform).

It seems that since the member's name was selected and then "erased", Access
had a ContactID but inow considers ContactID null. The red "X" is not
available and the user gets the "cannot contain a null value" message.
However, once the user enters "fake" data for member and animal, the red "X"
is available to delete the record. It's very sloppy.

Thanks for your help!

 

[Jeff Boyce]

Stephanie

I believe I understood from your description that this happens when an item
is selected then deleted.

My question still remains. If you insist on a value for this field (i.e.,
"required"), then why are you allowing the user to enter nothing at all?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I'm not allowing the user to enter nothing.

The combo box fields allow the user to list all of the members and animals,
in a datasheet format, that have the same registration date.

Mainform:
Registration Expiration Date (03/31/07)

Subform:
Sue Anderson, Buffy, other fields...
John Doe, Spot, other fields...
Mike Taylor and oops, Mike Taylor has an expiration date of 06/31/07

Let's delete Mike's name from the 03/31/07 record instance of Reg. Exp. Date
and enter him instead in the next record instance. I backspace over his name
and try to move the main form to the next instace. But I can't. I have to
re-enter Mike, enter his dog's name and then delete the entire row before I
can get the main form to 06/31/07 instance.

I'm not not-entering the ContactID. I'm trying to erase an error. But have
to jump through hoops to do so.

I hope that makes sense.
Thanks for hanging in there.

 

[Jeff Boyce]

Aha!

You "backspace" over, believing that this erases. While it does erase
characters you can see, your combo box has already "selected" something. By
backspacing, you are leaving a zero-length string in the control ... I
suspect that's what's causing the problem.

Instead of backspacing, try pressing <Esc> to undo/clear that control. And
since entering something (even a zero-length string) dirties the record,
press <Esc> a second time to undo the record that you've inadvertently
started.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Let's delete Mike's name from the 03/31/07 record instance of Reg. Exp. Date
and enter him instead in the next record instance. I backspace over his name
and try to move the main form to the next instace. But I can't. I have to
re-enter Mike, enter his dog's name and then delete the entire row before I
can get the main form to 06/31/07 instance.

Why not base the combo on a Query offering only unexpired names?

Note that the user can hit <Esc> once to erase the current entry in this
control, restoring it to its previous value; or <Esc> twice to cancel the
current table record altogether. Maybe it's just a training issue!

John W. Vinson [MVP]

 

[Guest]

A training issue- for me! Who knew?! Thanks for the tip!

 

[Guest]

Thanks for taking the time to get to the "Aha!" moment! I had no idea about
the nifty "double escape" trick! Thanks for the help.