Delete From ComboBox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,
Access 97, combo box recordsource is 2 columns from a table. LimitToList is
True.

I'd like to allow users to hit the Delete key while focus is on my combo and
hit Tab or Enter. I want to trap the appropriate event with code to prompt
if they really want to delete the record (I can take it from there if I get
that far). I also want them to be able to use the NotInList event to
prompt/add records to the combo recordsource if they type in a new entry.
That code is already in place.

Problem is that, as soon as I Tab away from the combo with the entry
deleted, I get runtime error 3162, "Can't assign a Null value..." I've tried
to capture a Null in the box value with BeforeUpdate, but apparently
NotInList happens first and the "NewData" argument in that proc is predefined
as as String (which I believe is what's causing the error).

The only way I've been able to get this to work is a workaround I'm a bit
uncomfortable with - to capture the error in the Form's error event, bypass
it and then run my code for the prompt/delete. If this error occurs for any
other reason than my design I'll have problems.

Any ideas on this would be appreaciated!
 
DatabaseBob said:
Greetings,
Access 97, combo box recordsource is 2 columns from a table.
LimitToList is True.

I'd like to allow users to hit the Delete key while focus is on my
combo and hit Tab or Enter. I want to trap the appropriate event
with code to prompt if they really want to delete the record (I can
take it from there if I get that far). I also want them to be able
to use the NotInList event to prompt/add records to the combo
recordsource if they type in a new entry. That code is already in
place.

Problem is that, as soon as I Tab away from the combo with the entry
deleted, I get runtime error 3162, "Can't assign a Null value..."
I've tried to capture a Null in the box value with BeforeUpdate, but
apparently NotInList happens first and the "NewData" argument in that
proc is predefined as as String (which I believe is what's causing
the error).

The only way I've been able to get this to work is a workaround I'm a
bit uncomfortable with - to capture the error in the Form's error
event, bypass it and then run my code for the prompt/delete. If this
error occurs for any other reason than my design I'll have problems.

Any ideas on this would be appreaciated!
Click to expand...

What record is it you're wanting to delete? The current record of the
form, or the record in the combo box's rowsource table that corresponds
to the value shown in the combo box?

Do want this process to happen when the user deletes the entire value
shown in the combo box, or should they just have to hit the Delete key
any time they are in the combo box (and what then if they want to edit
the value and delete a character from the middle of it)?

Is this a bound combo box? If so, what should become of the bound field
when you delete its value from the combo's rowsource?

You're messing with the normal Windows user interface here, which may
not be such a great idea if users don't expect the behavior you
implement.
 
Dirk,

Thanks for the reply. I'd like to delete the form record. It's a
continuous forms subform with 3 fields on it, one of which is the combo. I
want it to happen when the user blanks the entire value out (and makes the
combo value Null) where it had a value previously. The combo is bound to a
table field that is part of the form's recordset. Since I want the form's
record to be deleted, the bound value would go with it. The recordsource for
the combo would be unaffected.

This IS the behaviour the users expect. The combo is the only required
field on the form and when they remove its value they expect the record to
dissappear.
 
DatabaseBob said:
Dirk,

Thanks for the reply. I'd like to delete the form record. It's a
continuous forms subform with 3 fields on it, one of which is the
combo. I want it to happen when the user blanks the entire value out
(and makes the combo value Null) where it had a value previously.
The combo is bound to a table field that is part of the form's
recordset. Since I want the form's record to be deleted, the bound
value would go with it. The recordsource for the combo would be
unaffected.

This IS the behaviour the users expect. The combo is the only
required field on the form and when they remove its value they expect
the record to dissappear.
Click to expand...

Okay, I've got it now. I've even seen this expectation in action. Try
something like this:

'----- start of example code -----
Private Sub cboYourCombo_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_Handler

With Me.cboYourCombo
If IsNull(.Value) And Not IsNull(.OldValue) Then
Cancel = True
.Undo
Me.Undo
RunCommand acCmdDeleteRecord
End If
End With

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point

End Sub
'----- end of example code -----
 
Looks good except for one thing: I can't get BeforeUpdate to fire on the
Combo box. The NotInList event seems to fire first and throws the error
before ever reaching BeforeUpdate. Notice that when you create an
OnNotInList procedure for a combo box it predefines the "NewData" argument as
a String (in Acc97). I think Access is trying to shove a Null into NewData
when the user deletes the value from the box and tries to move focus. Just a
theory.

When I put a break point on the first line of a BeforeUpdate proc, execution
never breaks at that point. The error (3162) is thrown and execution stops.
Also interesting: Even if I set the IDE to break on all errors I don't get a
break with this error.
 
DatabaseBob said:
Looks good except for one thing: I can't get BeforeUpdate to fire on
the Combo box. The NotInList event seems to fire first and throws
the error before ever reaching BeforeUpdate. Notice that when you
create an OnNotInList procedure for a combo box it predefines the
"NewData" argument as a String (in Acc97). I think Access is trying
to shove a Null into NewData when the user deletes the value from the
box and tries to move focus. Just a theory.

When I put a break point on the first line of a BeforeUpdate proc,
execution never breaks at that point. The error (3162) is thrown and
execution stops. Also interesting: Even if I set the IDE to break on
all errors I don't get a break with this error.
Click to expand...

Interesting, Bob. I've tested the code and it works fine for me. When
I delete the contents of the combo box and tab out, the NotInList event
does not fire, and the BeforeUpdate event does. So there's something
different about your setup from mine. I tried it in both Access 97 and
Access 2002, and it worked both times. Are you sure you aren't typing a
space in the control, rather than just deleting its entire contents?

I don't think your theory about the NewData argument holds water.
NewData gets the text that is in the control, which is always a string,
even if it's a zero-length string.

Maybe you should post all the code you have in the form that is relevant
to that control.
 
Thanks Dirk. I don't think it'll be necessary to post code from the form -
your comments prompted me to try a little experiment. I deleted ALL the code
from the form's class module, retried the same action with it and still got
the same error. This leads me to believe the form itself is corrupt. The
fact that you confirmed BeforeUpdate runs before NotInList and that the
NewData argument is not the problem means I need to recreate this form. And
yes, I'm deleting the entire contents of the combo. Thanks for your help.
 
DatabaseBob said:
Thanks Dirk. I don't think it'll be necessary to post code from the
form - your comments prompted me to try a little experiment. I
deleted ALL the code from the form's class module, retried the same
action with it and still got the same error. This leads me to
believe the form itself is corrupt. The fact that you confirmed
BeforeUpdate runs before NotInList and that the NewData argument is
not the problem means I need to recreate this form. And yes, I'm
deleting the entire contents of the combo. Thanks for your help.
Click to expand...

Hmm. I didn't mean to suggest that BeforeUpdate fires before NotInList.
I don't believe it does. But in my tests NotInList didn't fire when I
deleted the value displayed in the combo box. In other words, a Null
entered value doesn't trigger NotInList.

Maybe your form is corrupt, but maybe it's just something we've
overlooked. If you'd like to send me a cut-down copy of your database,
containing only the elements necessary to demonstrate the problem,
compacted and then zipped to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it
to the address derived by removing NO SPAM from the reply address of
this message.
 
Back
Top