Predictive dialers generate profit

Menu
+ Free On-Line Tutorial

-Avoid #Error

-Customize 'Not In List' msg

-Programmatically add items to a combo box list

-Programmatically hide controls

-Security

MS Access Tools

About Us

Consulting Services




Tools, Add-ins, and Tutorials for Microsoft Access Developers 888-287-9497
linegld.gif

Question:
How do I programmatically add an item to a combo box that wasn't originally in the list? When my users type in a value that is not in my combo box list, they get an error. Instead, I want a message to ask the user if they want their desired item added to the list. How do I do this?

 

Solution:

MS Access provides us a event procedure called OnNotInList. By adding some simple code to this event procedure, we can accomplish just want you want. To discover exactly how to do this, we must know if the combo box's record source is based off a Value List or Table/Query. The next two examples will show how to accomplish this in both scenarios.

 

For the first example, we'll assume that a combo box named City has a table as a records source. The table is also called Cities and has a autonumber field as a primary key. If a user tries to type in a city that is not yet in the combo box, a customized error message will popup asking if the user wished to add the desired city to the table. If the user says "Yes", then the city will be added to the Cities table as a new record.

www.moretools.com

Private Sub City_NotInList(NewData As String, Response As Integer)

'Suppress the default error message.

Response = acDataErrContinue

' Prompt user to verify if they wish to add a new value.

If MsgBox("The city of " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

' Set Response argument to indicate that data is being added.

'Open a recordset of the Cities Table.

Dim db As Database

Dim rstCity As Recordset

Dim sqlCities As String

Set db = CurrentDb()

sqlCities = "Select * From Cities"

Set rstCity = db.OpenRecordset(sqlCities, dbOpenDynaset)

'Add a new City with the value that is stored in the variable NewData.

rstCity.AddNew

rstCity![City] = NewData

rstCity.Update

'Inform the combo box that the desired item has been added to the list.

Response = acDataErrAdded

rstCity.Close 'Close the recordset

End If

End Sub

 

For the following example, we will assume that a combo box named Clothing Size uses a Value List as it's records source. Small, Medium, and Large are the current values of the list. If a user tries to type in X-Large into the combo box, a customized error message will popup asking if the user wished to add X-Large to the list of possible clothing sizes.

 

Private Sub Clothing_Size_NotInList(NewData As String, Response As Integer)

'Suppress the default error message.

Response = acDataErrContinue

'Prompt user to verify if they wish to add a new value.

If MsgBox(" " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then

'Add a new Size to the list with the value from the variable NewData.

[Clothing Size].RowSource = [Clothing Size].RowSource & ";" & NewData

'Inform the combo box that the desired item has been added to the list.

Response = acDataErrAdded

End If

End Sub

 

 


[Free On-Line Lessons] [ MS Access Tools ]  [Consulting] [About Us] [ Home ]


Copyright More Web Sales, LLC All Rights Reserved.
2245 N. Green Valley Parkway, #212
Henderson NV  89014 
orders@morewebsales.com
Office: 1-888-287-9497 (toll free)
Fax: 702-447-1401
International Callers