combobox null entry

[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

 

[Ken Snell \(MVP\)]

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

 

[smk23]

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

 

[Ken Snell \(MVP\)]

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/