Combobox refuses to get "nothing" value, even though it's not required, allowed to get zero lengths

[Amir]

Hi!

I want to link to combo boxes in a way that
depending in the value in the first
combo box, the second combo box's will act
in one of the following ways:
1. Be available for edit, and force user to enter values into
it (limit to list, not allow zero length etc.)
2. Not be available for edit, and set itself to "nothing"
/ null / "" (I don't know what it should it)

The problem is that when I try to update the records in
the form, when I enter a value that means that the second
combobox should become unavailable and get "nothing" value,
I get the following message: 'You cannot add or change a
record because a related record is required in table
'tblMaterialTypes'

I don't understand why is it happening, becuase:
the MaterialType field in the tblMaterialTypes
is set to Required = No, AllowZeroLength = Yes.
the MaterialType field in the frmReceiptEvents is
set to LimitToList = No.

The properties of MaterialType command are determined
on the value in the ProductKey command, in the following method:

Private Sub ProductKeyInReceipt_AfterUpdate()
If Me!ProductKeyInReceipt.Column(4) = False Then
Me!MaterialType.BackColor = 9009253
Me!MaterialType.ForeColor = 14935011
Me!MaterialType.LimitToList = False
Me!MaterialType.Enabled = True
Me!MaterialType.SetFocus
Me!MaterialType = ""
Me!ProductKeyInReceipt.SetFocus
Me!MaterialType.Enabled = False
Me!MaterialType.Locked = True
Else
Me!MaterialType.BackColor = 15723495
Me!MaterialType.ForeColor = 0
Me!MaterialType.Enabled = True
Me!MaterialType.LimitToList = True
Me!MaterialType.Locked = False
End If

End Sub


Column(4) has the value which determines whether
this spesific product should have a MaterialType value
or not.

All the updates of the enabled property and colors etc. works just fine.


Thank you very much for your help!

 

[Michael Keating]

Hi,

I think this error is occurring because your material types table is on the
"one" side of a one-to-many relationship in the database, with "Enforce
Referential Integrity" set..

Because of this, if you try to add a record to the table on the many side,
with the related field set to Null, you will get the error.

The record you create must have one of the values in the material types
table.

You may want to create a material type of something like "No set type", and
use that instead of Null.

HTH

MFK.

 

[Amir]

That's exactly what I've done.

Thank you, Michael!