Add new data to combo/list box

[Pattie]

Is there a way to add a new record to a combo or list box
during data entry in a form? I want the end user to be
able to select only from the items (company name)in the
box, but there are times when they are dealing with a new
company that hasn't been added to the database. What I'd
like to do is, if they enter a name that is not in the
list, a dialog box comes up similar to the one that
already does, and asks "This is not in the list, do you
want to add it?" or something on that order...

TIA,
Patti

 

[tina]

try something along the lines of

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

If MsgBox("Do you want to add a new company to the list?", _
vbDefaultButton1 + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmCompanies", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!Combo0 = Null
Me!Combo0.Dropdown
End If

End Sub

substitute the correct control and form names, of course. opening the form
as Dialog will suspend the code until the form is closed. then Response =
acDataErrAdded will cause the system to automatically requery the combobox
so it will include the newly added item.
the above assumes that your combo box is getting the companies from a table,
and you want to add a new company to that table.

hth

 

[Pattie]

Thanks, Tina. That worked great!!

-----Original Message-----
try something along the lines of

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

If MsgBox("Do you want to add a new company to the list?", _
vbDefaultButton1 + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmCompanies", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!Combo0 = Null
Me!Combo0.Dropdown
End If

End Sub

substitute the correct control and form names, of course. opening the form
as Dialog will suspend the code until the form is closed. then Response =
acDataErrAdded will cause the system to automatically requery the combobox
so it will include the newly added item.
the above assumes that your combo box is getting the companies from a table,
and you want to add a new company to that table.

hth





.

 

[tina]

you're very welcome.

Thanks, Tina. That worked great!!