lucyLuna
asked on
Not in list event Access 2010
Hi All,
I am trying to figure out how to create a "Not In List Event" my drop down list is related to the auto industry, I need the user to be able to add Make and Model if not found in dropdown.
Thanks much
I am trying to figure out how to create a "Not In List Event" my drop down list is related to the auto industry, I need the user to be able to add Make and Model if not found in dropdown.
Thanks much
Sometimes, my combobox source table contains more info that shown in the combo. In these cases, I create a bound "list" form to the combo source table with the ability to add records. Then, from the DOUBLE-CLICK event of the combo, I open this form, in dialog mode, let the user add the required record, then - when the form is closed, the code requeries the combo box to include the newest record... me.cboYourBox. requery
Scott C
Scott C
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you need to add more than one field, the situation is more complex; in this case I open another form for filling in the data, then close it and requery the combo box. Here is code for that situation:
Private Sub cbo________________NotInList(strNewData As String, intResponse As Integer)
'Created by Helen Feddema 7-May-2007
'Last modified 7-May-2007
On Error GoTo ErrorHandler
Dim cbo As Access.ComboBox
Dim dbs As DAO.Database
Dim frm As Access.Form
Dim intMsgDialog As Integer
Dim intResult As Integer
Dim rst As DAO.Recordset
Dim strDescription As String
Dim strEntry As String
Dim strFieldName As String
Dim strFilter As String
Dim strForm As String
Dim strMsg As String
Dim strMsg1 As String
Dim strMsg2 As String
Dim strTable As String
Dim strTitle As String
'The name of the table that is the combo box's row source
strTable = "_______________"
'The name of the form
strForm = "_______________"
'The type of item to add to the table
strEntry = "_______________"
'The field in the lookup table in which the new entry is stored
strFieldName = "_____________"
'The add-to combo box
Set cbo = Me.ActiveControl
'Display a message box asking whether the user wants to add
'a new entry.
strTitle = strEntry & " not in list"
intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
strMsg1 = "Do you want to add "
strMsg2 = " as a new " & strEntry & " entry?"
strMsg = strMsg1 + strNewData + strMsg2
intResult = MsgBox(strMsg, intMsgDialog, strTitle)
If intResult = vbNo Then
'Cancel adding the new entry to the lookup table.
intResponse = acDataErrContinue
cbo.Undo
GoTo ErrorHandlerExit
ElseIf intResult = vbYes Then
'Add a new record to the lookup table.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
rst.AddNew
rst.Fields(strFieldName) = strNewData
rst.Update
rst.Close
cbo.Undo
'Continue without displaying default error message
intResponse = acDataErrContinue
'Open form for adding more data for new item
DoCmd.OpenForm strForm
Set frm = Forms(strForm)
strFilter = "[" & strFieldName & "] = " & Chr$(39) _
& strNewData & Chr$(39)
Debug.Print "Filter string: " & strFilter
frm.FilterOn = True
frm.Filter = strFilter
End If
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
=============================
Code for form buttons
=============================
Private txt As Access.TextBox
Private cbo As Access.ComboBox
Private frm As Access.Form
Private prj As Object
Private strCallingForm As String
Private Sub cmdDiscard_Click()
'Created by Helen Feddema 20-Mar-2007
'Last modified 20-Mar-2007
On Error GoTo ErrorHandler
Set prj = Application.CurrentProject
'Name of the form with the add-to combo box
strCallingForm = "_______________"
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
If prj.AllForms(strCallingForm).IsLoaded Then
Forms(strCallingForm).Visible = True
Else
DoCmd.OpenForm strCallingForm
End If
Set frm = Forms(strCallingForm)
'Name of add-to combo box
Set cbo = frm![_______________]
cbo.Requery
ErrorHandlerExit:
DoCmd.Close acForm, Me.Name
Exit Sub
ErrorHandler:
If Err.Number = 2467 Then
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
End If
End Sub
Private Sub cmdSave_Click()
'Created by Helen Feddema 20-Mar-2007
'Last modified 20-Mar-2007
On Error GoTo ErrorHandler
Set prj = Application.CurrentProject
'Name of the form with the add-to combo box
strCallingForm = "_______________"
'The textbox control on this form that holds the key value
Set txt = Me![____________]
If prj.AllForms(strCallingForm).IsLoaded Then
Forms(strCallingForm).Visible = True
Else
DoCmd.OpenForm strCallingForm
End If
Set frm = Forms(strCallingForm)
'Name of add-to combo box
Set cbo = frm![_______________]
cbo.Requery
cbo.Value = Nz(txt.Value)
ErrorHandlerExit:
DoCmd.Close acForm, Me.Name
Exit Sub
ErrorHandler:
If Err.Number = 2467 Then
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number _
& " in " & Me.ActiveControl.Name & " procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End If
End Sub
ASKER
@Helen, Thanks much for the code...I'll start working on it right away....I do appreciate your help.
ASKER
Thank you very much!
http://www.blueclaw-db.com/access_notinlist_advanced_example.htm