Solved

Not in list event  Access 2010

Posted on 2012-03-29
6
726 Views
Last Modified: 2012-03-30
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
0
Comment
Question by:lucyLuna
6 Comments
 
LVL 39

Expert Comment

by:als315
ID: 37785866
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 37787012
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
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 37788475
Here is some boilerplate code to add a single new item to the table that is the row source of a combo box.  

Private Sub cbo________NotInList(strNewData As String, intResponse As Integer)
'Set Limit to List to Yes
'See Add-to Combo Boxes (AA 161).mdb
'Created by Helen Feddema 24-Apr-2011
'Last modified 7-Apr-2010

On Error GoTo ErrorHandler
   
    Dim cbo As Access.ComboBox
    Dim dbs As DAO.Database
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName 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 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(strFieldName) = strNewData
        rst.Update
        rst.Close
   
        'Continue without displaying default error message.
        intResponse = acDataErrAdded
     End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 37788485
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

Open in new window

0
 

Author Comment

by:lucyLuna
ID: 37789447
@Helen, Thanks much for the code...I'll start working on it right away....I do appreciate your help.
0
 

Author Closing Comment

by:lucyLuna
ID: 37789975
Thank you very much!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now