ComboBox_NotInList

[downwitchyobadself]

I would like to:

[ol][li]Reproduce the behavior of the private NotInList event on a combo box, but[/li][li]Publicly, and[/li][li]without showing the Text-you-entered-is-not blah blah message (equivalent acDataErrContinue),[/li][li]and retaining the new data to start populating a form[/li][li]all this with no ComboBox_NotInList code, but rather a call to a public function.[/ol]

Any helpful thoughts?

 

[930driver]

Here's the public sub that I use to add a value to a combo box list. If you don't want the message box, just remove or comment it out. NewData is available in the NotInList event as well as in the below proceedure. If you need to use it in another proceedure pass it as a parameter. You could also pass it to a form as it's opening with OpenArgs. You could also save it to a global variable for further use.

Sub AddToList(NewData As String, Response As Integer, Title As String, strTable As String _
, strField As String)
' Add a new record by typing a name in the combo box.

On Error GoTo ErrorProc:
Dim intNewRecord As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim mydb As Database
Dim rs As Recordset

' Display message box asking if user wants to add a new record.
strTitle = Title & " Not In List"
intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
intNewRecord = MsgBox("Do you want to add a new " & Title & "?", intMsgDialog, strTitle)

If intNewRecord = vbYes Then
' Remove new data from combo box so
' control can be requeried when user returns to form.
DoCmd.RunCommand acCmdUndo

Set mydb = DBEngine(0)(0)
Set rs = mydb.OpenRecordset(strTable)
With rs
.AddNew
.Fields(strField) = NewData
.Update
.Close
End With

' Continue without displaying default error message.
Response = acDataErrAdded
End If
leave:
On Error Resume Next
Set rs = Nothing
Set mydb = Nothing
Exit Sub

ErrorProc:
MsgBox "Error: " & Err.Number & ": " & Err.Description
Resume leave

End Sub

 

[downwitchyobadself]

Thanks for the reply. But how do I invoke this public procedure without putting some code behind the combo itself? In other words, don't you still have a line item in the NameOfCombo_NotInList event on your form, a line which says something like

    AddToList NewData, Response, "New record", "MyTableName", "MyFieldName"

in a Private sub procedure, which means that the form contains a class module?

What I'm really looking for is a function call that would allow me to pass NewData and Response straight off the line in the properties of the form, using "=", but it seems like NewData and Response don't exist as part of a function call...

Unless you call your sub procedure a different way?

 

[930driver]

Hmm, don't think you can get there from here.

I believe in order to use newdata and response you'll have to have the code there.