combobox null entry

  • Thread starter Thread starter smk23
  • Start date Start date
S

smk23

I have a combobox with "limit to list" setting = yes. When a user wants to
delete the displayed value and have nothing in the box, I would like them to
be able to simply delete and move on.

Deleting prompts a null value and that is not in the list. I have tried
putting code on the Before_Update event to detect this and change the value
to 0 which is a blank record (and in the list) but the error occurs prior to
the Before_Update event. How have others solved this?

Thanks!!
Sam
 
Test for Null in the NotInList event and ignore it if it's there:

Private Sub ComboBoxName_NotInList(NewData As String, Response As Integer)
If Me.ComboBoxName.Value Is Null Then
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End Sub
 
Ken,
I tried the below code. Before it gets there, however, I get error message
"You tried to assign Null value to a variable that is not a Variant data
type". My backend is SQL 2000 and this particular column does not allow
nulls. Is this an Access issue or SQL issue?

Sam
 
The problem likely is ACCESS, and it's probably because NewData is a string
variable, not a variant variable, and thus cannot hold a Null value. Hmm,
I've never set up this situation before, so I apologize for missing what now
appears to be fairly obvious (if true!).

So, you'll need to use not use the built-in NotInList setup. Change the
Limit To List property to No. Delete the NotInList event code. Use the
combobox's BeforeUpdate event to do the NotInList test:

Private Sub ComboBoxName_BeforeUpdate(Cancel As Integer)
Dim blnOK As Boolean
Dim lngLoop As Long
If IsNull(Me.ComboBoxName.Value) = False Then
For lngLoop = 0 To Me.ComboBox.Name.ListCount - 1
If CStr(Me.ComboBoxName.Value) = _
Me.ComboBoxName.Column(Me.ComboBoxName.BoundColumn, _
Me.ComboBoxName.ItemData(lngLoop)) Then
blnOK = True
Exit For
End If
Next lngLoop
If blnOK = False Then
MsgBox "You must enter a value that is in the combo box's list!"
Me.ComboBoxName.Undo
Cancel = True
End If
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
Back
Top