asked on

Combo box Not in List

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?

mbizup

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Taras

ASKER

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.

Taras

ASKER

I can see that new value is seating in table field, should I do some kind requery ?

mbizup

<<Response = acDataErrAdded >>

This line should automatically force the requery.

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

Taras

ASKER

Thanks a lot.

mbizup

What is the Row Source property of your combo box?