You tried to assign the Null value...

[Brian]

I've been at this way too long to have this problem, but here it is anyway:

Continuous form.
Combo box, column 1 bound to field in table that is the form's RecordSource.
Field is not part of PK in table.
RowSource is from another table.
Field is the many side of a one-to-many relationship. Integrity is enforced
with CascadeUpdate.

I want the contents to be optional but referential integrity-enforced when
populated. Furthermore, there are times when it needs to be cleared after
previously populated.

I must have forgotten or missed something obvious here.

When I populate the field, then try to nullify it (via VBA "Field1 = Null"
or manully via highlight & delete key), I get a 3162 error "You tried to
assign the Null value to a variable that is not a Variant data type."

I can open the table directly, populate the field, move to another field &
back, then delete the contents just fine. It just fails on the form.

I thought error 3162 was the error one gets when attempting to delete part
of a multi-field PK, but evidently not.

What's going on here?

 

[Allen Browne]

The error indicates that you are trying to assign Null to a *variable* in
your code, e.g.:
Dim i As Integer
i = Null

That code will fail, because the only VBA type that can contain the Null
value is the Variant. Therefore you need:
Dim v As Variant
v = Null
followed by whatever other code you need.

As you say, JET is quite happy about having a Null in a foreign key field,
even with Referential Integrity enforced. And there are cases where it makes
sense to do so.

You may not be aware, it but is possible to have Access automatically set
the foreign key to Null when the record in the primary table is deleted. For
details on how to set this up, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html

 

[Brian]

Thanks for the quick response, Allen.

This one really had me stuck. I am well aware of the need to use the Variant
type when there is any possibility that the variable could be null. However,
this is a bound control, not a variable.

Now, I just found the problem and a partial answer in the form's record
source. I typically include a join to each of the primary table's PK's so
that I can sort the form by the visible column of each combo box.

Here is a simplified version:

SELECT Trip.*, Route.RouteName
FROM Route INNER JOIN Trip ON Route.RouteID = Trip.RouteID
ORDER BY Route.RouteName;

OF COURSE! The JOIN effectively makes the RouteID a required field. It still
seems to me that the 3162 error should occur at update of the form and not of
the control, though. Is this just something I have to accept, or is there a
workaround? There are still times when I want to nullify the value of the
control after it has been populated. In this case, the RouteID control is
populated in the DriverID_AfterUpdate, thus:

RouteID = Dlookup ("[RouteID]", "[Driver]","[DriverID] = " & [DriverID])

There are drivers that have no default RouteID. If a user selects an
incorrect driver having a RouteID, then the box is populated; when the user
then selects another driver having no RouteID, I want the box to be cleared
to ensure that the user picks the correct RouteID in Form_BeforeUpdate
instead of leaving the RouteID as it was with the incorrect driver. In my
Form_BeforeUpdate, I already call a routine that checks to make sure this
field is not null before updating.

Here is the RecordSource that works:

SELECT Trip.*, Route.RouteName
FROM Route RIGHT JOIN Trip ON Route.RouteID = Trip.RouteID
ORDER BY Route.RouteName;

The RIGHT JOIN resolves the issue. Is the RIGHT JOIN my only solution here,
or is there an option somewhere to tell Access to quit enforcing the JOIN at
the control update and enforce it only at the form update?

 

[Allen Browne]

The outer join sounds like a perfectly good solution.

(If you wanted an alternative, I suppose you could use a main form bound to
the main table, with a subform bound to the related table. But in this
scenario, the records that have a null foreign key never show up in the
subform again, since there is no record in the main form that would load
them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the quick response, Allen.

This one really had me stuck. I am well aware of the need to use the
Variant
type when there is any possibility that the variable could be null.
However,
this is a bound control, not a variable.

Now, I just found the problem and a partial answer in the form's record
source. I typically include a join to each of the primary table's PK's so
that I can sort the form by the visible column of each combo box.

Here is a simplified version:

SELECT Trip.*, Route.RouteName
FROM Route INNER JOIN Trip ON Route.RouteID = Trip.RouteID
ORDER BY Route.RouteName;

OF COURSE! The JOIN effectively makes the RouteID a required field. It
still
seems to me that the 3162 error should occur at update of the form and not
of
the control, though. Is this just something I have to accept, or is there
a
workaround? There are still times when I want to nullify the value of the
control after it has been populated. In this case, the RouteID control is
populated in the DriverID_AfterUpdate, thus:

RouteID = Dlookup ("[RouteID]", "[Driver]","[DriverID] = " & [DriverID])

There are drivers that have no default RouteID. If a user selects an
incorrect driver having a RouteID, then the box is populated; when the
user
then selects another driver having no RouteID, I want the box to be
cleared
to ensure that the user picks the correct RouteID in Form_BeforeUpdate
instead of leaving the RouteID as it was with the incorrect driver. In my
Form_BeforeUpdate, I already call a routine that checks to make sure this
field is not null before updating.

Here is the RecordSource that works:

SELECT Trip.*, Route.RouteName
FROM Route RIGHT JOIN Trip ON Route.RouteID = Trip.RouteID
ORDER BY Route.RouteName;

The RIGHT JOIN resolves the issue. Is the RIGHT JOIN my only solution
here,
or is there an option somewhere to tell Access to quit enforcing the JOIN
at
the control update and enforce it only at the form update?

The error indicates that you are trying to assign Null to a *variable* in
your code, e.g.:
Dim i As Integer
i = Null

That code will fail, because the only VBA type that can contain the Null
value is the Variant. Therefore you need:
Dim v As Variant
v = Null
followed by whatever other code you need.

As you say, JET is quite happy about having a Null in a foreign key
field,
even with Referential Integrity enforced. And there are cases where it
makes
sense to do so.

You may not be aware, it but is possible to have Access automatically set
the foreign key to Null when the record in the primary table is deleted.
For
details on how to set this up, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html

 

[Brian]

Thanks again, Allen.

It sounds like the outer join is the simplest answer. I've been doing this
for years without the outer join and always wondered why I got that error. It
didn't matter too much, because I require the controls to be populated in my
Form_BeforeUpdate code anyway. I check for null controls here rather than
using required fields to replace non-intuitive system messages with my own
message that says, for example, "Please enter the route" and then sets the
focus to and drops down the control in question.

I just got tired of seeing the error & decided to plow through the issue
until I found a solution.

The outer join sounds like a perfectly good solution.

(If you wanted an alternative, I suppose you could use a main form bound to
the main table, with a subform bound to the related table. But in this
scenario, the records that have a null foreign key never show up in the
subform again, since there is no record in the main form that would load
them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the quick response, Allen.

This one really had me stuck. I am well aware of the need to use the
Variant
type when there is any possibility that the variable could be null.
However,
this is a bound control, not a variable.

Now, I just found the problem and a partial answer in the form's record
source. I typically include a join to each of the primary table's PK's so
that I can sort the form by the visible column of each combo box.

Here is a simplified version:

SELECT Trip.*, Route.RouteName
FROM Route INNER JOIN Trip ON Route.RouteID = Trip.RouteID
ORDER BY Route.RouteName;

OF COURSE! The JOIN effectively makes the RouteID a required field. It
still
seems to me that the 3162 error should occur at update of the form and not
of
the control, though. Is this just something I have to accept, or is there
a
workaround? There are still times when I want to nullify the value of the
control after it has been populated. In this case, the RouteID control is
populated in the DriverID_AfterUpdate, thus:

RouteID = Dlookup ("[RouteID]", "[Driver]","[DriverID] = " & [DriverID])

There are drivers that have no default RouteID. If a user selects an
incorrect driver having a RouteID, then the box is populated; when the
user
then selects another driver having no RouteID, I want the box to be
cleared
to ensure that the user picks the correct RouteID in Form_BeforeUpdate
instead of leaving the RouteID as it was with the incorrect driver. In my
Form_BeforeUpdate, I already call a routine that checks to make sure this
field is not null before updating.

Here is the RecordSource that works:

SELECT Trip.*, Route.RouteName
FROM Route RIGHT JOIN Trip ON Route.RouteID = Trip.RouteID
ORDER BY Route.RouteName;

The RIGHT JOIN resolves the issue. Is the RIGHT JOIN my only solution
here,
or is there an option somewhere to tell Access to quit enforcing the JOIN
at
the control update and enforce it only at the form update?

The error indicates that you are trying to assign Null to a *variable* in
your code, e.g.:
Dim i As Integer
i = Null

That code will fail, because the only VBA type that can contain the Null
value is the Variant. Therefore you need:
Dim v As Variant
v = Null
followed by whatever other code you need.

As you say, JET is quite happy about having a Null in a foreign key
field,
even with Referential Integrity enforced. And there are cases where it
makes
sense to do so.

You may not be aware, it but is possible to have Access automatically set
the foreign key to Null when the record in the primary table is deleted.
For
details on how to set this up, see:
Cascade-to-Null Relations
at:
http://allenbrowne.com/ser-64.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I've been at this way too long to have this problem, but here it is
anyway:

Continuous form.
Combo box, column 1 bound to field in table that is the form's
RecordSource.
Field is not part of PK in table.
RowSource is from another table.
Field is the many side of a one-to-many relationship. Integrity is
enforced
with CascadeUpdate.

I want the contents to be optional but referential integrity-enforced
when
populated. Furthermore, there are times when it needs to be cleared
after
previously populated.

I must have forgotten or missed something obvious here.

When I populate the field, then try to nullify it (via VBA "Field1 =
Null"
or manully via highlight & delete key), I get a 3162 error "You tried
to
assign the Null value to a variable that is not a Variant data type."

I can open the table directly, populate the field, move to another
field &
back, then delete the contents just fine. It just fails on the form.

I thought error 3162 was the error one gets when attempting to delete
part
of a multi-field PK, but evidently not.

What's going on here?