Combo box question

[sir_aingeal]

I have a combo box that is set as limit to list so that when a new entry is added it brings up a form to add the new entry to the list. It currently does this by requerying and putting the new entry in order in the list, I would like it to automatically enter the new entry as the active entry in the combo.

Any suggestions on how to do this would be gratefully received.

Thanks

Iain

 

[Mile-O]

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

    Dim db As DAO.Database, rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryYourQuery")
    With rs
        .AddNew
        .Fields("MyField") = NewData
        .Update
        .Close
    End With
    
    Response = acDataErrAdded
    
    Me.lstMyListBox.Requery
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub

 

[sir_aingeal]

Thanks that works good.

Will that also work with cascading combo's where it is in the secord or third in the cascade.

Iain

 

[Mile-O]

Should do, although you'll have to add the extra details to ensure that it's normalised properly and will appear on future cascadings in the correct place.

i.e. If you have two combos: cboCountries and cboCities then you should have two tables:

tblCountries
CountryID
Country

tblCities
CityID
City
CountryID


So, if adding a new city, you'd add NewData into the city field but you'd need to use the value of the cboCountries to add it into the foreign key of tblCities.