Solved

Not in list event  Access 2010

Posted on 2012-03-29
6
730 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 40

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 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