ComboBox Dropdown Problem

[George Akers]

I have a ComboBox with LimitToList set to Yes. When the user enters a
new value(not in list) I call a routine to add that value to the list.
Prior to that I put up a MsgBox asking the user if that is what they
want to do. If they select no I return to the ComboBox and perform an
Undo. The problem is that the list is always dropped down when control
returns to the user. Does anyone know how to avoid this? Here is the
NotInList Event:

Private Sub cboContact_NotInList(NewData As String, Response As Integer)
On Error GoTo cboContact_NotInListErr

Dim intAddRow As Integer

AddNewRows "tblContacts", intAddRow
If intAddRow = vbNo Then
Me.cboContact.Undo
End If

Response = 0

cboContact_NotInListExit:

Exit Sub

cboContact_NotInListErr:
MsgBox Err.Description, , "Runtime Error # " & Err.Number & " in "
& Err.Source
Resume cboContact_NotInListExit
End Sub



TIA, George

 

[Graham Mandeno]

Hi George

You have not told the combobox that the new item has been added. This is
done via the Response argument.

You should use the predefined constants to return values in the response
argument, instead of literal constants (eg 0).

Return acDataErrContinue if you have NOT added the new item (this suppresses
the default error message) and return acDataErrAdded if you HAVE added the
item (this causes the list to be automatically requeried).

As an aside, I don't know what your AddNewRows procedure is doing, but I
can't see how it will work if you don't pass it the NewData string.

 

[George Akers]

Thanks Graham. That does make my code look better, you are right about
NewData. I had mistakedly left that out of my sample.

Now that snippet looks like this:

AddNewRows "tblCategories", NewData, intAddRow
If intAddRow = vbNo Then
Me.cboContact.Undo
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

However it has not helped with my problem of the ComboBox being "dropped
down" when the user chooses not to add the new value and regains control
of the form. Any ideas there?