This solution allows the user to "double-click" the combo box and add a new entry to the list. Further, this custom situation will also even allow the user to immediately insert this new item on the list into their current entry right then & there; it will specifically prompt them for this specific thing.
The overall steps:
The user has a PO invoicing system. The field which has a list which needs to be maintained is a chart of accounts. (Note: in this case, the actual account number--not an autoid field linked to the number--is what will be inserted into the entry.) You want to create a specific "add new" form which allows the user to insert a new item into the list of accounts. This form will only show the minimal fields needed to get the entry done quickly. (If, for example, you were maintaining a list of vendors which also had the name, address, phone number, zip, etc, for the "add new" form you may choose to have it only add the name, and then add the other information later on when you "clean up" the list.)
Then, when the user "double-clicks" the combo box (how to do this explained later), you would see something like this:
Notice the "add new" form on the bottom-right.
Notice:Then, after the user enters the entry and clicks the EXIT button, the following question appears (the code for it is encoded in the EXIT sign, which explains why the X [close] button on the form is hidden):
| Tab | Property | Setting |
| Format | Record Selectors | No |
| Caption | Adding New Account Number to list | |
| Navigation Buttons | No | |
| Control Box | No | |
| Min Max Buttons | None | |
| Close Button | No | |
| Other | Cycle | Current Record |
| Allow Design Changes | Design View Only (Access 2K & XP Only) |
Private Sub cmdExit_Click() ' Close the form if the user clicks EXIT without entering a new entry If IsNull(Me.account_number) Then DoCmd.Close GoTo 99 End If Dim response As Integer, message As String, x As Integer message = "Do you wish to plug this new account into your current entry?" response = MsgBox(message, vbYesNo, "Insert New?") If response = vbNo Then DoCmd.Close GoTo 99 End If x = Me.account_number Forms![frmMainPos].Form.SetFocus Screen.ActiveControl.Value = x DoCmd.Close acForm, "frmAddNewChartofAccounts" DoCmd.RunCommand acCmdRefresh 99 End Sub |
Then, assign the following code to the "double-click" event procedure of the combo box:
Private Sub account_number_DblClick(Cancel As Integer) DoCmd.OpenForm "frmAddNewChartofAccounts", acNormal, "", "", acAdd, acNormal DoCmd.GoToControl "account_number" End Sub |
Derived From Microsoft Knowledge Base Articles Q161007 and Q88148. Also check out this article from ME.
This article shows you two methods of using the NotInList event to add a new record to a combo box on a form.
This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.
MORE INFORMATION
The following examples use the Orders form in the Northwind sample database to demonstrate two methods of using the NotInList event to add a record to a combo box. The NotInList event fires when you type a new company in the CustomerID (labeled Bill To:) field on the Orders form.
The first method uses Visual Basic for Applications code to programmatically add a new record to the Customers table. The second method opens the Customers form and lets you add a new record yourself.
CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file or perform these steps on a copy of the Northwind database.
Method 1: Using Code to Add a Record to a Table
Private Sub CustomerID_NotInList (NewData As String, Response As _
Integer)
Dim Db As Database
Dim Rs As Recordset
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Confirm that the user wants to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
' If the user chose not to add a customer, 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 customer, open a recordset
' using the Customers table.
Set Db = CurrentDB
Set Rs = Db.OpenRecordset("Customers", DB_OPEN_TABLE)
' Let code execution continue if a run-time error occurs.
On Error Resume Next
' Create a new record.
Rs.AddNew
' Ask the user to input a new Customer ID.
Msg = "Please enter a unique 5-character Customer ID."
Rs![CustomerID] = InputBox(Msg)
' Assign the NewData argument to the CompanyName field.
Rs![CompanyName] = NewData
' Save the record.
Rs.Update
If Err Then
' If a run-time error occurred while attempting to add a new
' record, set the Response argument to suppress an error
' message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox Error$ & CR & CR & "Please try again.",vbExclamation
Else
' If a run-time error did not occur, set Response argument
' to indicate that new data is being added.
Response = acDataErrAdded
End If
End If
End Sub
|
Method 2: Using a Form to Add a New Record
Private Sub CustomerID_NotInList (NewData As String, Response As _
Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Customers form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Customer form's Form_Load event
' procedure.
DoCmd.OpenForm "Customers", , , , acAdd, acDialog, NewData
End If
' Look for the customer the user created in the Customers form.
Result = DLookup("[CompanyName]", "Customers", _
"[CompanyName]='" & NewData & "'")
If IsNull(Result) Then
' If the customer was not created, 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 customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub
|
Set the form's OnLoad property to the following event procedure:
Private Sub Form_Load ()
If Not IsNull(Me.OpenArgs) Then
' If form's OpenArgs property has a value, assign the contents
' of OpenArgs to the CompanyName field. OpenArgs will contain
' a company name if this form is opened using the OpenForm
' method with an OpenArgs argument, as done in the Orders
' form's CustomerID_NotInList event procedure.
Me![CompanyName] = Me.OpenArgs
End If
End Sub
|