combobox adding a new record to a table

i have a combobox which works as far as pulling the total customers from a table and showing me them one so i can select him/her ?

is it possible to allow a user to type in a new name and then it could populate a new record for my table ?
LVL 1
aot2002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thenelsonCommented:
Yes, you would use the not in list event of the combobox.  Assuming you want to create a new record in the table that is the record source of the form the combobox is in:

Private Sub <ComboBoxName>_NotInList(NewData As String,  Response As Integer)

runcommand acCmdRecordsGoToNew       'goto new record
<TextBoxBoundToName> = NewData        'Put the new info that is not in list in the name field (the name field can be invisible)
Response = acDataErrAdded                     'Cancel the not in list error and requery the combobox

End Sub
aot2002Author Commented:
no i want to create the record via a different table then the current form the combo box is on?

can i execute sql ? example would help me
heer2351Commented:
The approach of thenelson is correct, however the code is a bit off.

This is a more elaborate example adapted from the MsAccess help. Please replace: yourComboBox, yourTable and yourField with the correct names.

You have to set the LimitToList property to Yes and create a NotInList event:

Private Sub yourComboBox_NotInList(NewData As String, Response As Integer)
  Dim ctl As Control
      
  'Return Control object that points to combo box.
  Set ctl = Me!yourComboBox
  'Prompt user to verify they wish to add new value.
  If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
    'Set Response argument to indicate that data is being added.
     Response = acDataErrAdded
    CurrentDb.execute "insert into yourTable (yourFieldName) values ('" & ctl.value & "')", dbFailOnError
  Else
    ' If user chooses Cancel, suppress error message and undo changes.
    Response = acDataErrContinue
    ctl.Undo
  End If
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aot2002Author Commented:


CurrentDb.Execute "insert into tblContactInformation LastName values ('" & Combo390.Text & "')", dbFailOnError

fail on insert statement ????
aot2002Author Commented:
CurrentDb.Execute "insert into tblContactInformation (LastName) values ('" & Combo390.Text & "')", dbFailOnError

got it thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.