"You tried to assign a Null value..."

[Stephanie]

Hope I can be helped! Here’s the error message: “You tried to assign a Null
value to a variable that is not a Variant data typeâ€.

Here’s the setup:

tblContacts
ContactID

tblChapters
ChapterID

tblChapterMembers
ChapterMemberID
ContactID
ChapterID

(which is a bad design since a contact can only be a member of one chapter,
but that’s how I designed it)

The row source for subfrmChapterMembers is
SELECT Chapter.ChapterID, Chapter.ChapterName
FROM Chapter;

frmContacts is based on tblContacts.

I have subformChapterMembers on frmContacts.

Not all contacts belong to a chapter. If I leave the chapter blank, all is
well. But if I accidentally assign a chapter to someone who doesn’t belong to
that chapter, I can’t delete the chapter assignment. When I “backspace†the
chapter entry, I get this error message: “You tried to assign a Null value to
a variable that is not a Variant data typeâ€.

I tried “delete†twice, but received the same error message. So essentially,
I need to leave an (incorrect) entry for Chapter to avoid this message. I’d
appreciate your insight!

 

[Klatuu]

Post back the code where this is happening and point out the line where the
error occurs, please.

 

[Stephanie]

This error doesn't happen when walking through code. It happens when I use
the combo box to select "chapter" on subfrmChapter that is on the frmContacts.

 

[Klatuu]

So that would be happening in the After Update event of the combo. What is
happening in that event?

 

[Stephanie]

Good to know!
There is no code on the combo or on the sfrm in any of the events.

 

[J_Goddard via AccessMonster.com]

Did you try entering blanks to replace the erroneous chapter value? Does the
table (tblChapterMembers?) allow null values for ChapterID, i.e. is Required
set to "Yes"?

The error massage may be misleading and is related to the table.

John

 

[Klatuu]

This is certainly a mystery. The error you are getting indicates a problem
trying to assign a Null value to a variable. The issue is that unless there
is a table contraint on a table field, you can assign nulls to any data type
in a table. You can also assign a Null to a form control, so that leaves
variables. Variables are in code, so some code is running to cause that
error.

Look in the events tab of the properties dialog for the after update event
in the combo and tell me what it says.

 

[Stephanie]

Hmm. There is no code in any of the combo box events or in any of the sfrm
events. I'm not a coding kind of gal. I'm not getting a error message (like
error 2108). Just the standard Access message box...

I created the sfrm with record source:
SELECT ChapterMembers.ChapterMemberID, ChapterMembers.ContactID,
ChapterMembers.ChapterID, Chapter.ChapterName
FROM Chapter INNER JOIN ChapterMembers ON Chapter.ChapterID =
ChapterMembers.ChapterID;

I created the combo with record source: SELECT Chapter.ChapterID,
Chapter.ChapterName
FROM Chapter;

The frm is based on Contacts.

I pulled the sfrm to the frm. I select the chapter from the combo for each
contact. If I accidently select a chapter, I can't get rid of it.

In tblChapterMemeber, there are no required fields.

 

[Stephanie]

Interesting. I just tried typing "spaces" but that didn't work. Required is
not set to yes...

 

[J_Goddard via AccessMonster.com]

Sounds like the problem might be with the combo box. Is the Limit to List
property set to "No"?

John

Hmm. There is no code in any of the combo box events or in any of the sfrm
events. I'm not a coding kind of gal. I'm not getting a error message (like
error 2108). Just the standard Access message box...

I created the sfrm with record source:
SELECT ChapterMembers.ChapterMemberID, ChapterMembers.ContactID,
ChapterMembers.ChapterID, Chapter.ChapterName
FROM Chapter INNER JOIN ChapterMembers ON Chapter.ChapterID =
ChapterMembers.ChapterID;

I created the combo with record source: SELECT Chapter.ChapterID,
Chapter.ChapterName
FROM Chapter;

The frm is based on Contacts.

I pulled the sfrm to the frm. I select the chapter from the combo for each
contact. If I accidently select a chapter, I can't get rid of it.

In tblChapterMemeber, there are no required fields.

This is certainly a mystery. The error you are getting indicates a problem
trying to assign a Null value to a variable. The issue is that unless there

[quoted text clipped - 54 lines]

 

[Klatuu]

Is the combo box a bound control?
Check the table design of the table and see if the field it is bound to
allows Nulls.

 

[Stephanie]

Sorry- I don't know what you are asking. The combo is a bound control but I
don't see in the table design where you see where I'd say if I'd allow
Nulls...

 

[Stephanie]

Interesting! I do have the Limit to List property set to "No" because I want
the users to only choose "designated" Chapters. Once a Chapter is chosen, I
can't clear the Chapter since only Chapters are in the combo (so I can't
reset the Chapter to Null).

Does that mean I can either change the Limit to List as "Yes" and be
prepared for free-form chaos, or I can add "(blank)" to the Chapter list and
say that anything that is blank (either because Chapter was never entered, or
because I manually selected "(blank)" doesn't have a Chapter? Of couse, then
I could run a report for ("blank") chapters which is not the same thing as
for Chapter being null- but I don't think that matters.

Guess I'm just wondering if adding "blank" to the Chapter list is a good idea.
Thanks!

Sounds like the problem might be with the combo box. Is the Limit to List
property set to "No"?

John

Hmm. There is no code in any of the combo box events or in any of the sfrm
events. I'm not a coding kind of gal. I'm not getting a error message (like
error 2108). Just the standard Access message box...

I created the sfrm with record source:
SELECT ChapterMembers.ChapterMemberID, ChapterMembers.ContactID,
ChapterMembers.ChapterID, Chapter.ChapterName
FROM Chapter INNER JOIN ChapterMembers ON Chapter.ChapterID =
ChapterMembers.ChapterID;

I created the combo with record source: SELECT Chapter.ChapterID,
Chapter.ChapterName
FROM Chapter;

The frm is based on Contacts.

I pulled the sfrm to the frm. I select the chapter from the combo for each
contact. If I accidently select a chapter, I can't get rid of it.

In tblChapterMemeber, there are no required fields.

This is certainly a mystery. The error you are getting indicates a problem
trying to assign a Null value to a variable. The issue is that unless there

[quoted text clipped - 54 lines]

I need to leave an (incorrect) entry for Chapter to avoid this message. I’d
appreciate your insight!