Create a combo box which allows user to add a client record

I have a table called client in my Client_form.   The primary field is clientNo (numeric) and two other fields, clientName and ClientAddress.  I want to create  a combo box that allows user to add a new client record if not in the client list (After a prompt like: Not in List.  Add new Client?)   ClientNo field is unbound in the combo box. I also want to display ClientName field in the combo box. I would like to see the event code and form properties (not-in-list,limit to list…).  Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Tony HungateTechnical Writer/Trainer | CISSPCommented:

I'm not sure what version of access you are using but in 2007 and 2010 there is an option to "All Value List Edits" and a List Items Edit Form" options under the Data tab of your combobox.  If you set the Allow Value List Edits to "Yes" and then select the form that you would like to have opened which allows for the input of a new Client this should meet your need.

Your rowsource for the combobox should be something like this:

SELECT [ClientNo], [ClientName] FROM [YourTableName] ORDER BY [Field of your choice]

Hope this helps. You can find some good examples of this in the Northwind Sample DB that is provided by MS.


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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note that those settings metioned above only apply if you're using 2007/2010 AND you've chosen to use the new Multivalued Fields. Most experienced developers strictly avoid those MVF items, and you would be wise to do so as well.

You can all an item to the combo's underlying recordsource using the NotInList event. To do so, you must insure that the "LimitToList" property must be set to "Yet". This article from Microsoft provides complete information on working with that event:

Note the
DavidDF1913Author Commented:
I have attached my combo not-in-list code.  I am using Access 2010.  The combo box is called ClientNoCombo and Limit-to-list is set to yes.  I want the user to be able to add a client no. that is not already in the list to the client table. This code forces the user to re-enter the client no, I would like to accept the new number if the user wants to add it. The only field to be added through the combo box is the client no. (client.cCno).  The procedure should check that it is a unique (numeric) value.  

I am aware that there is a least one error in BuildCriteria(). Thanks for any help.

  Private Sub ClientNoCombo_NotInList(NewData As String, Response As Integer)
  Dim Db As DAO.Database
  Dim Rs As DAO.Recordset
  Dim Msg As String
  Dim NewNo As String
 On Error GoTo Err_ClientNoCombo_NotInList
  ' Exit this subroutine if the combo box was cleared.
      If NewData = "" Then Exit Sub
    ' Confirm that the user wants to add the new client
     Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
     Msg = Msg & "Do you want to add it?"
     If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
         ' If the user chose not to add a client, set the Response
         ' argument to suppress an error message and undo changes.
         Response = acDataErrContinue
         ' Display a customized message.
         MsgBox "Please try again."
         ' If the user chose to add a new client, open a recordset (client table)
         Set Db = CurrentDb
         Set Rs = Db.OpenRecordset("Client", dbOpenDynaset)
         ' Ask the user to input a new Client No.
         Msg = "Please enter a " & vbCr & "Client No."
         NewNo  = InputBox(Msg)
         Rs.FindFirst BuildCriteria("ccno", dbText, NewNo)
         ' If the NewNo already exists, ask for another new unique
         ' Client no.
         Do Until Rs.NoMatch
            NewNo  = InputBox("Client No. " & NewNo & " already exists." & _
                     vbCr & vbCr & Msg, NewNo & " Already Exists")
            Rs.FindFirst BuildCriteria("cCno", dbText, NewNo)
         ' Create a new record.
        ' Assign the NewNo to the Client No. field.
         Rs![cCNO] = NewNo
         ' Save the record.
         Response = acDataErrAdded
     End If
        Exit Sub
        MsgBox Err.Description
        Response = acDataErrContinue
End Sub
DavidDF1913Author Commented:
I substituted dblong in the buildcriteria function and the errors stopped. I also added rs.edit after rs.addrcd.  

when user imputs a number not in the list and confirms that he wants it added to the client table, I want it added to a new record immediately without further confirmation, like it does now.   Now it adds the record but the new client value is not displayed in the form immediately.

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 Applications

From novice to tech pro — start learning today.