NOTINLIST problem

[jamespeters01]

I know this sounds quite crazy, but how / is there a way to automatically add a new input in my form's combo box to my table using NOTINLIST ? At the mo. I'm having to go through the process of opening up a separate form through NOTINLIST which then add's my new input to the table then allows me to choose it in my combo box.

 

[AlexMidd]

I have used this in several applications. The code is something like this:

    Dim Msg As String, Answer As Integer
    Dim dbs As Database, rst As Recordset
    Answer = MsgBox("This category does not exist." & Chr(10) & "@Do you want to add it?@ ", _
            vbQuestion + vbYesNo + vbDefaultButton2, "Item Type")
    Response = acDataErrContinue
    If Answer = vbNo Then Exit Sub
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Type", dbOpenDynaset)
    With rst
        .AddNew
        !TypeDescription = NewData
        .Update
        .Bookmark = .LastModified
    End With
    Response = acDataErrAdded
    cmbItemType.Requery

This code comes from a list of categories in an expenses database and adds a new category if the one the user types in is not in the list. The important lines are the 'Response=' lines.

Response=acDataErrContinue tells the system to ignore the rror produced by the NotInList event, and to continue running the code.

Response=acDataErrAdded tells the system that the item has been added. The last line requeries the combo box so that the new item now appears in the list.

Make sure that the LimitToList property of the combo is set to 'Yes'.

Have fun! Alex Middleton

 

[tful282]

This post was just what i have been looking for, but can anyone tell me how to do this using ADO objects.

 

[jamespeters01]

Thanks for your help Alex, although it is bringing up all sorts of error massages !

 

[AlexMidd]

It took me a while to get this to work as well, but if you persevere and track down those errors, it is worth it.

The code is verbatim from one of my apps and it works there, so I assume there are conflicts with your code which need to be ironed out. Have fun! )

Alex Middleton

 

[ComputorMike]

Add the following function to module1
The following will prompt a message box to user if they want to add the info to the list. You only have to send it Me. The fuction will figure out what combo box has been selected even if it is in a sub form, and what field to append the new data in the table. This function assume that the source of the combo box is comming from a table.


Public Function MyAddtoList(sNewData As String, iResponse As Integer, frmName As Form)

'Private Sub ComboName_NotInList(NewData As String, Response As Integer)
'MyAddtoList NewData, Response, Me 'add this line to NotInList [Event Procedure]
'End Sub

Dim db As Database
Dim rst As Recordset
Dim frmMyForm As Form
Dim ctlCurrentControl As Control
Dim iAnswer As Integer
Dim iFieldsNumber As Integer
Dim sTableName As String
Dim sFieldName As String

Set frmMyForm = [frmName]
Set ctlCurrentControl = frmMyForm.ActiveControl
Set db = CurrentDb
sTableName = ctlCurrentControl.RowSource
iFieldsNumber = ctlCurrentControl.BoundColumn
iAnswer = MsgBox("{ " & sNewData & " } is currently not in the list!" & vbCr & _
" Would you like to add it?" & vbCr & _
" Click Yes to Add !!! ", vbYesNo + vbQuestion + vbDefaultButton2)

If iAnswer = vbYes Then
Set rst = db.OpenRecordset(sTableName)
sFieldName = rst.Fields(iFieldsNumber).Name
Set rst = db.OpenRecordset(sTableName)
rst.AddNew
rst.Fields(sFieldName) = sNewData
rst.Update
rst.Close
End If

iResponse = acDataErrAdded
Set db = Nothing
End Function To object or not to object
That is the question
Alast poor varible I new you well

 

[CptTom]

Mike:

I also followed your advice and put it into a Public Function and it worked !!! In my case, I am using an inventory form & table and the NotInList would be a new supplier. If I want to open the AddNewSupplier Form & table to fill in all the information, then go back to the inventory form, how would you write it?

Larry

 

[Guest_imported]

Had difficulty using these systems. I was getting an Error 13 Type Mismatch.

Turned out to be coming from the Recordset variables. Instead of declaring them as Recordset I declared them as Object and let OpenRecordSet set them to the correct type of Record set.

Thx for the code.

Deeraeya