Manipulating .NotInList Event/Add Data to Combobox

[dch3]

I have a comboBox where I've provided the user with the ability to enter a
value not in the list and if it is present in the underlying recordsource,
select the value. (For example, if the comboBox has three columns - EMPLOYEE
NUMBER, EMPLOYEE NAME, EMPLOYEE INITIALS - where column(0) EMPLOYEE NUMBER is
the bound field and .limitToList is True, the user has the ability to enter
the EMPLOYEE INITIALS to enter the employee name.

In the code below and its associated comboBox, the user can type the Show
Name or Show Number. The Show Number is in the column that is bound, however
it is not displayed - only the Show Name. The code below allows the user to
enter the Show Number and if present will select the corresponding Show Name.
If the user, types a Show Name that is *NOT* present in the list, the code
successfully allows the user to enter a new record and then go to that record
in the comboBox.

The problem that I'm having is when the user enters the Show Number. The
code successfully opens the form that captures the new record, however upon
closing that form the new record is not displayed and I'm presented with the
'The please select an item from the list.' The new record is added to the
list.

How do I suppress the error message?

I've marked the problem area.

David H

Private Sub cboSelectShow_NotInList(NewData As String, Response As Integer)
'Code which selects the show based on show number is of my own doing
'Code which adds a new record curtesy of MS.
'Since the .LimitToList = True, this code allows the user to enter a job
number and locate the corresponding show from there
'Although the txtShowNumber is the first field in the combo list and the
bound field, we have to retreive the show name from
'the underlying table in order to move the list to that specific record

Dim strMsgText As String
Dim varResult As Variant
Dim rs As DAO.Recordset
Dim strShowName As String

On Error GoTo Err_cboSelectShow_NotInList

If ECount("txtShowName", "tblShowInformation", "txtShowNumber = '" &
NewData & "'") > 0 Then
Me.cboSelectShow = NewData
Response = acDataErrContinue
Me.cboSortOrder.SetFocus
Call cboSelectShow_AfterUpdate
Exit Sub
End If

strMsgText = ""
strMsgText = strMsgText & "The show number or name has not been set-up
in the database" & Chr(13) & Chr(13)
strMsgText = strMsgText & "Do you want to add the show?"

If MsgBox(strMsgText, vbInformation + vbYesNo + vbDefaultButton2) = vbNo
Then
strMsgText = "Please select a show from the list."
MsgBox strMsgText, vbInformation
Response = acDataErrContinue
Exit Sub
End If

DoCmd.Echo False
DoCmd.OpenForm "frmShowInformationDetail", , , , acFormAdd, acDialog,
NewData
DoCmd.Echo True

varResult = ECount("txtShowName", "tblShowInformation", "txtShowNumber =
'" & NewData & "'")
If IsNull(varResult) Then
' If the show was not added set the Response argument to suppress an
error message and undo changes.
strMsgText = "Please select a show from the list."
MsgBox strMsgText, vbInformation
Response = acDataErrContinue
Else

'-------------------------PROBLEM AREA-------------------------------------
strShowName = NewData
Me.cboSelectLoadList = strShowNam
'--------------------------------------------------------------------------------
Response = acDataErrAdded
End If

Exit_cboSelectShow_NotInList:
Exit Sub

Err_cboSelectShow_NotInList:
MsgBox getDefaultErrorMessage(Me.Name, "cboSelectShow_NotInList",
Err.Number, AccessError(Err.Number)), vbCritical
Resume Exit_cboSelectShow_NotInList

End Sub

 

[Klatuu]

I don't know why, but I have found it is necessary to undo the combo box to
avoid the message coming back again. As to why you don't see the record, it
is because you are adding the record in another form and the new record is
not yet in your form's recordset. You can requery your form and navigate to
the new record

 

[dch3]

This did it...

Me.cboSelectShow.Undo
Me.cboSelectShow.Requery
Me.cboSelectShow = NewData
Response = acDataErrContinue
Me.cboSortOrder.SetFocus
Call cboSelectShow_AfterUpdate

From what I can tell...
-The undo was neccessary to clear value typed in the control, without this
Access was opening the list and presenting the 'Select from the list' msg
-.Requery forced the value back into the list
-Me.cboSelectShow = NewData sets the value to the value entered
-acDataErrContinue (which I still don't understand) tells Access that no
error occurred at all.

Bragging time...If anyone chooses to implement the bit where a person can
enter a value not in the list, but present in another column, I would like
(SOME) credit for the technique. (At least I'm pretty certain I came up with
it m'self, its been nearly 8 years). One practical application would be an
order entry screen where the user is working off of an order form with part
numbers and part descriptions. The comboBox would most likely present part
descriptions, but for efficiency sake entering the part number would be ideal.