Mile-O
Back once again...
- Local time
- Today, 06:12
- Joined
- Dec 10, 2002
- Messages
- 11,316
Summary: DAO method for adding an unknown item in a combobox's list to the underlying table
N.B. This article applies to users of the Data Access Objects (DAO) data access method. It is assumed that you are using MS Access 97 to follow this article. If you would like to use the DAO method with MS Access 2000 - or greater - then you may wish to refer to this thread. An ActiveX Data Objects (ADO) method can be found on this thread. Should you have a "User Defined Type not defined" error then refer to this thread.
On occasions whereby you have a set selection displayed within a form's combobox but would allow for the user to add a new option to the underlying table then you can make use of the combobox's NotInList event.
The NotInList event occurs at the moment when the user enters some data into the combobox that isn't already in said control's current list.
Some points to note:
Assuming that we have on our form a combobox called MyCombo, the following code will offer the user the option to add the new value into the underlying table and - if accepted - add said value.
N.B. This article applies to users of the Data Access Objects (DAO) data access method. It is assumed that you are using MS Access 97 to follow this article. If you would like to use the DAO method with MS Access 2000 - or greater - then you may wish to refer to this thread. An ActiveX Data Objects (ADO) method can be found on this thread. Should you have a "User Defined Type not defined" error then refer to this thread.
On occasions whereby you have a set selection displayed within a form's combobox but would allow for the user to add a new option to the underlying table then you can make use of the combobox's NotInList event.
The NotInList event occurs at the moment when the user enters some data into the combobox that isn't already in said control's current list.
Some points to note:
- This event is only triggered if the combobox's LimitToList property is set to 'Yes';
- The combobox's RowSourceType must be set to 'Table/Query';
- The combobox must have a RowSource set to either a table or a query.
Assuming that we have on our form a combobox called MyCombo, the following code will offer the user the option to add the new value into the underlying table and - if accepted - add said value.
Code:
Private Sub MyCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler
' provide text constants to reduce text later and allow for faster execution
' due to added speed from the compilation of constants
Const Message1 = "The data you have entered is not in the current selection."
Const Message2 = "Would you like to add it?"
Const Title = "Unknown entry..."
Const NL = vbCrLf & vbCrLf
' database and recordset object variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
' show message box and evaluate if the user has selected Yes or No
If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
' open a connection to the current database
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")
' using the recordset object
With rs
.AddNew ' prepare to add a new record
.Fields("MyField") = NewData ' add unfound data into field
.Update ' update the table
.Close ' close the recordset object
End With
Response = acDataErrAdded ' confirm record added
Else
Me.MyCombo.Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
End If
Exit_ErrorHandler:
' de-initialise our object variables
Set rs = Nothing
Set db = Nothing
Exit Sub
Err_ErrorHandler:
' display error message and error number
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Sub
Last edited: