Solved

Combo box Not in List

Posted on 2014-04-15
7
418 Views
Last Modified: 2014-04-15
I have combo box based on field from table.  I want to add new item that is not In my combo box. In combo box cboProductType on Not in List event I have this code:

On Error GoTo ErrorH
   
      Dim strTmp As String
      Dim dbs As DAO.Database
   
    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Expense Type?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        'Append the NewData as a record in the tblProduct table.
        strTmp = "INSERT INTO tblProduct ( ProductTypeDescription ) " & _
            "SELECT """ & NewData & """ AS ProductTypeDesription;"        
                dbs.Execute strTmp, dbFailOnError        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    Else
        Response = acDataErrDisplay
    End If
   
ErrorH:
     MsgBox "When trying to process this new record add, error happened" & _
           vbCrLf & "Please contact the program designer and " & _
           "report the error as follows" & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
    Resume Next


When I enter new item I got message from error handler Error#91
Description: Object variable or With block variable not set

Any Idea?
0
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 40001596
You haven't included a SET statement for your dbs Object.  try this:


On Error GoTo ErrorH
    
      Dim strTmp As String
      Dim dbs As DAO.Database
       Set dbs = CurrentDB   '<<<<<---- add this line

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 40001608
You'll also need an Exit statement before your error handler to make the code exit normally if there are no errors:


On Error GoTo ErrorH
    
      Dim strTmp As String
      Dim dbs As DAO.Database
      Set dbs = CurrentDB   '<<<<<---- add this line

    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Expense Type?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the tblProduct table.
        strTmp = "INSERT INTO tblProduct ( ProductTypeDescription ) " & _
            "SELECT """ & NewData & """ AS ProductTypeDesription;"         
                dbs.Execute strTmp, dbFailOnError        
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
    Else
        Response = acDataErrDisplay
    End If
    Exit Sub  ' (or function) <<<<----- add this
 
ErrorH:
     MsgBox "When trying to process this new record add, error happened" & _
           vbCrLf & "Please contact the program designer and " & _
           "report the error as follows" & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description
    Resume Next

Open in new window

0
 

Author Comment

by:Taras
ID: 40001665
mbizup I applied that and got:

Microsoft Access
? The text you entered ins’t an Item in the list
Select an item from the list, or enter text that matches one of the listed itmes.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Taras
ID: 40001681
I can see that new  value is seating in table field, should I do some kind requery ?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40001842
<<Response = acDataErrAdded >>

This line should automatically force the requery.


Did you try the full code I posted at ID http:#a40001608  ?
0
 

Author Closing Comment

by:Taras
ID: 40001864
Thanks a lot.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40001868
What is the Row Source property of your combo box?
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

737 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