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.
David
DavidDF1913Asked:
Who is Participating?
 
Tony HungateDirector of TrainingCommented:
David

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.

~:TLH:~
EEClarification.png
0
 
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:

http://support.microsoft.com/kb/197526

Note the
0
 
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.
David

  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."
    Else
         ' 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)
         Loop
         ' Create a new record.
        Rs.AddNew
        ' Assign the NewNo to the Client No. field.
         Rs![cCNO] = NewNo
         ' Save the record.
         Rs.Update
.
         Response = acDataErrAdded
     End If
Exit_ClientNoCombo_NotInList:
        Exit Sub
Err_ClientNoCombo_NotInList:
        MsgBox Err.Description
        Response = acDataErrContinue
End Sub
0
 
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.

David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.