Additions to Combo box on the fly

[spalmateer]

Hi,
I have a combo box on my form that has a list of builders. I have limited the input to the list but I would like to have the user be able to add additions to the list without having to leave the form. For example if the user types something that is not on the list, a message box will appear asking if the user would like to add 'this value' to the list. I've attempted to add code similiar to this (but with different names) without any luck-

Private Sub Builders_NotInList(NewData As String, Response As Integer)
Dim strMessage As String
Dim dbsContacts As Database
Dim rstTypes As DAO.Recordset

strMessage = "Are you sure you want to add '" & NewData & "' to the list of contacts?"

If Confirm(strMessage) Then

Set dbsContacts = CurrentDb
Set rstTypes = dbsContacts.OpenRecordset("Contact Types")
rstTypes.AddNew
rstTypes!ContactType = NewData
rstTypes.Update
Response = acDataErrAdded
Else
Response = asDataErrDisplay
End If
End Sub

This code above references the Confirm function which is:

Public Function Confirm(strMessage As String) As Boolean
Dim bytChoice As Byte
bytChoice = MsgBox(strMessage, vbQuestion + vbOKCancel, "ACE")
If bytChoice = vbOK Then
Confirm = True
Else
Confirm = False
End If
End Function

My combo box feeds off the table tblBuildersLookup. My form name is frmCustInfMain and the combobox is Builders.
I'm horrible with VB so I might just have the form and table references wrong. Thanks in advance for any help!
Scott

 

[SomeGuy]

I don't know if this will help or not. You say you don't want them to have to leave the form but what about a small popup form that will allow them to enter in the new builder and any additional info that you may want.

I have a combobox for researchers involved in projects. Whenever one is entered that is not on the list, a message box lets the entry person know and asks if they want to enter this new individual. If yes, a researcher form pops up and they enter the name, phone, and mailing address. Really handy if you have to enter a few other pieces of data at the same time as you are adding a new name.

Here is the code I use. All you need to do is substitute a few names.


Private Sub ResearcherID_NotInList(NewData As String, Response As Integer)
On Error GoTo Researcher_NotInList_Err
'Add a new record to the Researcher table
'and requery the ResearcherID combo box
Dim NewResearcher As Integer, MsgTitle As String, MsgDialog As Integer
Const MB_YESNO = 4
Const MB_ICONEXCLAMATION = 48
Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
'Make sure the user really wants to add it
MsgTitle = "Researcher is not in the list"
MsgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
NewResearcher = MsgBox("Do you want to add the new Researcher?", MsgDialog, MsgTitle)
If NewResearcher = IDNO Then
Response = DATA_ERRCONTINUE
' Display a customized message.
MsgBox "You have chosen NOT to enter a new Researcher. Please choose a name from the list."
Else
DoCmd.OpenForm "fm: Researchers", acNormal, , , acAdd, acDialog
Response = DATA_ERRADDED
End If
Researcher_Exit:
Exit Sub
Researcher_NotInList_Err:
MsgBox Err.Description
Resume Researcher_Exit
End Sub

Hope this may help you.


--------------------
Seasons Greetings!!!