Provided by Allen Browne. Updated June 2006.
Every database application uses combos for selecting a value from a lookup table.
In the newer versions, combos and list boxes have new properties to make it easy to add items to the list. (The old ways still work as well.)
To use the new properties:
When you are using this form, you can now right-click the combo, and choose Edit List Items.
By setting the combo's LimitToList property to Yes, you can use the NotInList event to append a new entry to the lookup table.
If several fields are to be entered (e.g. adding a new client), open a data entry form in dialog mode:
DoCmd.OpenForm "MyForm",,,, acAdd, acDialog
Dialog mode pauses your code until the entry is complete. You can then use acDataErrAdded to cause Access to find the new value.
In other situations only a single field is needed, such as a category, or a title like Mr/Ms/Dr/... Opening a form is unnecessary, as the user has already typed the new value. The function below verifies the entry and appends it to the lookup table.
This function identifies the lookup table from the combo's RowSource property. It assumes the field name in the lookup table is the same as the combo's ControlSource, i.e. the primary key name and foreign key name must be the same.
Follow these Steps:
This function will not work with Access 2 without modification.
Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer On Error GoTo Err_Append2Table ' Purpose: Append NotInList value to combo's recordset. ' Assumes: ControlSource of combo has the same name as the foreign key field. ' Return: acDataErrAdded if added, else acDataErrContinue ' Usage: Add this line to the combo's NotInList event procedure: ' Response = Append2Table(Me.MyCombo, NewData) Dim rst As DAO.Recordset Dim sMsg As String Dim vField As Variant ' Name of the field to append to. Append2Table = acDataErrContinue vField = cbo.ControlSource If Not (IsNull(vField) Or IsNull(NewData)) Then sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?" If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then Set rst = CurrentDb.OpenRecordset(cbo.RowSource) rst.AddNew rst(vField) = NewData rst.Update rst.Close Append2Table = acDataErrAdded End If End If Exit_Append2Table: Set rst = Nothing Exit Function Err_Append2Table: MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()" Resume Exit_Append2Table End Function
|Home||Index of tips||Top|