Solved

Combo box Not in List

Posted on 2014-04-15
7
412 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 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

17 Experts available now in Live!

Get 1:1 Help Now