OnNotInList - Combo Box

[Tracym]

I have a combo box listing Names which are stored in the Row Source property...

I can enter a value not listed in Row Source but how do I then automatically add it so that the value appears as an option in the Combo box for subsequent records?
I am familiar with VBA for Excel but not sure where to start with Access...

I know I can create another table which I can update (still don't know how to do that automatically though!) and link it to the Combo Box, but I was wondering if there were an easier way

Please help!!!!

Thanks
Tracyx

 

[SydneyGeek]

Hi Tracy,
something like this?

Private Sub lstCompany_NotInList(NewData As String, Response As Integer)

    ' Add a new category by typing a name in lstCompany combo box.

    Dim intNewCompany As Integer, strTitle As String, intMsgDialog As Integer
    Dim strMsg As String, rst As Recordset, Db As Database, newRst As Recordset
    Dim CompID As Long
    
    ' Adapted from VBA Developers Handbook
    ' Display message box asking if user wants to add a new category.
    strTitle = "New Company"
    strMsg = "'" & NewData & "' is not in the list. "
    strMsg = strMsg & "Would you like to add it?"
    intMsgDialog = vbYesNo + vbQuestion + vbDefaultButton1
    intNewCompany = MsgBox(strMsg, intMsgDialog, strTitle)
    If intNewCompany = vbNo Then
        Response = acDataErrDisplay
    Else
        Set Db = CurrentDb()
        Set rst = Db.OpenRecordset("Companies")
        rst.AddNew
            rst![Company Name] = NewData
        rst.Update
        Response = acDataErrAdded
        rst.Close
        
        'Open the Companies form
        DoCmd.OpenForm "Companies Form", acNormal, , , acFormEdit, acDialog
    End If

End Sub

This will write another record to the Companies table in which the CompanyID field resides. It also promps you to enter other client details before returning to the original form.

Denis

 

[bat17]

The method that SydneyGeek has given you works for a ComboBox that has a table for its row source, which is the best way to use combo's. There is no easy way to update a list of names in the control source that will stick. To do it you would need to change the form to design mode and make your changes, save the changes, then open the form again and return it to the right record.
I have not tested the code above but I think that you may need a requery statement at the end to refresh the combobox and make the new item show.

HTH

Peter

 

[SydneyGeek]

In reply to Peter,

Yes you do need a Requery event to see the new value -- my code doesn't have it here because I forgot to grab it

Not in front of Access at the moment, but the code -- something like the snippet below -- is attached to the AfterUpdate event of the combo box

Sub MyCombo_AfterUpdate()
   Me.Requery
End Sub

Hope that helps

Denis