Daily Access Tips

Here is a common situation:  Bill the salesperson / order taker is on the phone with a new customer and discovers that Ajax Wire Co., the new customer, is not yet available on the “Select a Customer” drop down pick list on the computer screen.  Bill must now keep this new customer on hold while he leaves one computer screen to go add the new customer before continuing.  Bill explains that the computer is slow as an excuse when the customer gets a bit impatient. Failure to automate this costs your company by presenting a poor first impression and possibly a lost sale now or a non-returning customer.

This can be easily avoided if you know what to ask your computer programmer for…

Here is what your computer automation should be doing:

1. The program must first detect automatically when a user is trying to add an item that is not on the selection list.
2. Find out from the user if it should be added and gaining the user’s permission.
3. Add this to the list by opening a maintenance form automatically and add the first bit of data that the user has already type in the drop down list.
4. Save the data about this newly added item and close the maintenance form.
5. Return the user to the original data entry form that the user was on.
6. Refresh the drop down list so the newly added item now shows on the list.
7. Select the newly added item for the user.

Why do you need the drop down pick list to restrict data entry to only acceptable values?

• A drop down list (a type of form control on a Microsoft Access form) can be used to restrict data entry to an existing list of items thus preventing wrong type of data from being entered.
• It prevents in-consistency of data (Example: Spelling variations – wrongly spelled versions of same item).
• Allows for additional data to be stored out side of the current Access form and the data that feeds it.
• Speeds data entry by presenting a list of previous items.
• Allows for relational links between data tables to be maintained, if a unique value in the lookup table* is stored.

*Microsoft Access Definition: A lookup table is an Access table that serves as a data storage area for data that will be used more than once and will be stored in an other Access table.

Here is a video demonstrating this:

The code below may used by your programmer or if he needs an example working demonstration file it’s found at:

Microsoft Access Show Me Demontration Files

Select example file #48

The programming code to be place in a public (Application Wide) code module:
Option Compare Database
‘Declarations
Public Not_In_List_In_Progress As Boolean
Public frmNot_In_List_Form As Form
Public ctlList_Control As Control
Public frmLookup_Form As Variant
Public ctllookup_Control As Variant
Public ctlLookupKey_Control As Variant

Code used in the NotInList Event of your combo box:
Public Sub Not_In_List_Starting_Handler(Not_In_List_Form As Form, List_Control As Control, Lookup_Form As Variant, Lookup_Control As Variant, vNewData As Variant, vLookupKey_Control As Variant)
On Error GoTo errline
Dim MsgBoxAnswer As Variant
‘Set public variables
Set frmNot_In_List_Form = Not_In_List_Form
Set ctlList_Control = List_Control
frmLookup_Form = Lookup_Form
ctllookup_Control = Lookup_Control
ctlLookupKey_Control = vLookupKey_Control
‘Request permission
MsgBoxAnswer = MsgBox(“Do you want to add this new item to this list?”, vbYesNo, “Add New customer?”)

If MsgBoxAnswer = vbNo Then
List_Control = Null ‘Make the list control empty for the time being.
Not_In_List_Form.SetFocus ‘Move cursor to the form.
List_Control.SetFocus ‘Move cursor to the list control.
GoTo exitline ‘Exit this Subroutine code.
Else ‘Permmision granted add new list item, so open lookup maintenance form to fill out a new list entry record.
DoCmd.OpenForm (Lookup_Form) ‘Open the lookup maintenance form.
Not_In_List_In_Progress = True ‘Set the Not In List flag for this coded solution.
DoCmd.GoToRecord , , acNewRec ‘Open new record to add list item.
DoCmd.GoToControl (Lookup_Control) ‘Move cursor to the control to receive the value the user tried to enter on data entry form.
Screen.ActiveControl = vNewData ‘Write the value that the user tried to enter in the list, in the correct control on the now open lookup maintenance form.
List_Control = Null ‘Make the list control empty for the time being.
End If

exitline:
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: Error Number: ” & Err.Number & “  ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
End Sub

Code used for the OnClick event of the maintenance for used in for the look up table:

Public Sub Not_In_List_Returning_Handler()
On Error GoTo errline
If Not_In_List_In_Progress = True And CurrentProject.AllForms(frmNot_In_List_Form.Name).IsLoaded = True Then
ctlList_Control.Requery ‘Refresh the list, so item just entered is now on list
DoCmd.GoToControl (ctlLookupKey_Control) ‘Go to the control containing the key value for the list control to store
ctlList_Control = Screen.ActiveControl.Value ‘Select the key value in the list.
Not_In_List_In_Progress = False ‘Turn off the Not In List flag for this code solution.
DoCmd.Close acForm, frmLookup_Form ‘Close the lookup form
frmNot_In_List_Form.SetFocus ‘Put cursor back in the original data entry form
ctlList_Control.SetFocus ‘Put cursor back in the original list control
Else
DoCmd.Close acForm, Screen.ActiveForm.Name
End If
exitline:
Exit Sub
errline:
Select Case Err.Number
Case 2467 ‘Form variable frmNot_In_List_Form references a form that is not currently open.
‘ This means that the user closed the form that had the not on list trigger before pressing the ‘Save/Close’ button
‘ on the lookup form that is now active.
Not_In_List_In_Progress = False
DoCmd.Close acForm, Screen.ActiveForm.Name
Exit Sub
Case 91
DoCmd.Close acForm, Screen.ActiveForm.Name
Exit Sub
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: Error Number: ” & Err.Number & “  ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
Resume Next
End Select
End Sub

Find example file at:

Choose Example file #48

Bob Heifler
MyAccessProgram.com

Contacthttp://myaccessprogram.com/Contact.html

Example fileshttp://myaccessprogram.com/Download.html

Free video tutorialshttp://myaccessprogram.com/Tutorial.html