IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
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|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'NotInList: Adding values to lookup tables (old version)'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...