Deleting text in a combo box to delete row in N:N table

[David W. Fenton]

I have noticed over the years that when you use a datasheet subform
with a combo box list for the user to create a record in the join
table for a many-to-many relationship, users do not understand that
they are creating a record in a table (nor should they need to do
so).

This causes a problem in this scenario:

1. user lands in a new row.

2. user picks something from the list.

3. user changes mind and deletes text in combo box.

4. when the form updates, they get either of these two errors:

3058 Index or primary key can't contain a Null value.
3162 You tried to assign the Null value to a variable that
isn't a Variant data type.

The other scenario that is equivalent:

1. users sees a list of items in the subform.

2. user wants to remove one item from the list.

3. users deletes the text in the combo box for that row.

4. one of the above error messages pops up.

So far as I can tell, neither of these errors is trappable via the
events of the combo box control or the form, because they are
happening at the database engine level (it's an RI or index error,
depending on your schema), and as long as the combo boxes are bound
(which they must be to display correct data), the database engine
validation happens before the control and form events have a chance
to be activated.

The lack of trappability of errors like this in bound controls is
one of the reasons I've always avoided validation rules in field
definitions, since it causes the same problem, an untrappable error
(so far as I'm aware).

Is there some way to trap it after all of which I'm just ignorant?
What I'd like to do in trapping the error is ask the user if they'd
like to delete that item from the BROOMSTICK list (where BROOMSTICK
is the meaningful name they have for the items listed in the
subform, as opposed to the list of items in the combo box). I've
Googled this and find that others have encountered the problem, but
nobody seems to have a workaround.

If there is no workaround, I see these two options:

1. train the users to do it right.

2. revise the UI for entering these items so that it avoids this.

While #1 will be successful with the user population I'm working
with in the app I'm working on right now, it goes against my grain,
as I hate handling an anticipated error by training the users how to
avoid it, instead of changing the app so that the user can't make
the mistake. I think the user interpretation of what should work is
reasonable, and training them to avoid it is an example of Joel
Spolsky's rule of leaky abstractions
(http://www.joelonsoftware.com/articles/LeakyAbstractions.html),
where the UI is allowing the underlying implementation to leak
through to the user interaction layer.

But #2 is problematic in that I've already got limited screen real
estate -- that's the reason I was using a datasheet subform in the
first place. Sure, editing could be restricted to a popup form, but
I hate popup forms for things that should be doable in place.

Any suggestions, either in how to avoid it or how to trap it?

The current app is in A2003, though that likely doesn't matter as
this has been an issue in every version of Access I've ever used
(which is all of them from Access 2 on, except 2007).

 

[David W. Fenton]

How about something like this in the control's BeforeUpdate event
procedure:

Have you tried it? The error occurs *before* the control events
fire, so it's not trappable.

And that's the whole point -- there are no events that fire before
Access validates what you've entered against the database engine's
rules (indexes, validation rules, RI).

Lying in bed last night, I came up with an awful kludge, and that's
to add a dummy field with not index or RI, and bind the combo box to
that and use the combo box events to update the *real* field. That
way, you could trap deleting the contents of the combo box in its
BeforeUpdate event and act accordingly.

But, of course, it's way too easy for the duplicate data to get out
of synch and cause problems.

 

[John Spencer]

Just out of curiousity, did you try the form error event?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[David W. Fenton]

I have tried it and it appears to work faultlessly.

Well, it turns out it *does* work. I have no idea what I was doing
wrong -- I'd already tried to deal with it in the BeforeUpdate
event, and it wasn't working. Maybe I put it in the AfterUpdate by
mistake? I sometimes accidentally switch OnClick and OnDoubleClick
with comical results, so that must have been what happened.

Thanks for taking the time -- I will now be able to write a shared
procedure to deal with this in multiple subforms. Actually, now that
I think about it, I believe I was testing the .Text property instead
of the .Value -- nope, that's not it, either, because that works,
too.

The other thing about this is that I've encountered this problem
many times in the past and felt unable to resolve it. I'm perfectly
capable of using the BeforeUpdate event, so I don't know how I seem
to have repeatedly messed this one up.

 

[David W. Fenton]

Just out of curiousity, did you try the form error event?

I see you're replying to my original post, not the followup after I
got it to work.

Yes, I tried the OnError of the form, and I said that in the post
you quote:

So far as I can tell, neither of these errors is trappable via the
events of the combo box control or the form...

Now that I have an approach that works in the combo box's
BeforeUpdate event, I haven't re-attempted the form event because I
wouldn't use it except in the case where the control event didn't
work.