Add to list box (Access 2002 SP-2)

Topic

New Reply

Is there a way to automatically add new items to an established drop down list box?
Clark

Reply | Quote

Replies

If the combo box has its Row Source Type set to Table/Query: no, you can’t. But you can change the Row Source in code to another table, query or SQL statement.

If the combo box has its Row Source Type set to List: yes, you can, use the AddItem method. (Note for Loungers using Access 97 or 2000: this was introduced in Access 2002)

Reply | Quote

Just a reminder that you can, but not using AddItem. You have to trigger the NotInList event and handle it in code the same way as you did in earlier versions of Access.

Reply | Quote

Good point. I forgot the obvious answer.

Reply | Quote

Would it possible for you to describe this procedure for me?
Clark

Reply | Quote

If you do a search for NotInList in this forum, you will find lots of posts dealing with it. For instance, post 146637 contains two code examples.

Reply | Quote

Modifying some of the code examples I was referred to I have come up with the following:

Private Sub Publisher_NotInList(NewData As String, Response As Integer)
If MsgBox(“The type ” & NewData & ” does not occur not in the list.” & vbCrLf & _
“Do you want to add it?”, vbYesNo + vbQuestion) = vbYes Then
Dim strSQL As String
strSQL = “INSERT INTO tblIn-Print Inventory2 (Publisher) VALUES (” & _
Chr$(34) & NewData & Chr$(34) & “)”
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Publisher.Undo
Response = acDataErrContinue
End If
End Sub

What I am trying to do is this: in the form “In-Print Inventory2”, based on the the table “In-Print Inventory2”, there is a field called “Publisher” which is a combo box based on the field “Short Name” in the table “Publishers”. I want to be able to add a new publisher to the combo box on the fly. I’ve placed the code above as an event procedure in the Not In List event property of the field “Publisher” of the form “In-Print Inventory2”. When I add a new publisher in the form I’m told the name is not on the list, would I like to add it. When I click yes I get “runtime error ‘3134’ syntax error in INSERT INTO statement” and I’m referred to this line in the code:
CurrentDb.Execute strSQL, dbFailOnError

Clark

Reply | Quote

One problem is that Access SQL does NOT like odd characters in table names, so you need to put the table name tblIn-Print Inventory2 in square brackets like this: [tblIn-Print Inventory2]. That way SQL knows that you’re not trying to do some kind of math with fields it’s never heard of. Try that and see if your error goes away.

Reply | Quote

Adding brackets (like [tblIn-Print Inventory2]) just brings up another error: “runtime error ‘3192’ could not find output table ‘tblIn-Print Inventory2’.”

Reply | Quote

In post 248963 farther up in this thread, you state[indent]

---

… based on the the table “In-Print Inventory2” …

---

[/indent]but in the SQL you use tblIn-Print Inventory2. Are you absolutely sure about the name? Any typo will cause an error message.

Reply | Quote

The table is named “In-Print Inventory2”. I was assuming that “tbl” had to be included in the SQL. Leaving it out brings a third error. So here’s the code:

Private Sub Publisher_NotInList(NewData As String, Response As Integer)
If MsgBox(“The type ” & NewData & ” does not occur not in the list.” & vbCrLf & _
“Do you want to add it?”, vbYesNo + vbQuestion) = vbYes Then
Dim strSQL As String
strSQL = “INSERT INTO [In-Print Inventory2] (Publisher) VALUES (” & _
Chr$(34) & NewData & Chr$(34) & “)”
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Publisher.Undo
Response = acDataErrContinue
End If
End Sub

Here’s the new error message:”Run-time error ‘3464’: Data type mismatch in criteria expression.” And the de###### references the same line of code (CurrentDb.Execute strSQL, dbFailOnError)

Reply | Quote

On looking back at an earlier reply by you in this thread, I see that[indent]

---

there is a field called “Publisher” which is a combo box based on the field “Short Name” in the table “Publishers”.

---

[/indent]This implies that you should be updating the Publishers table, not the In-Print Inventory2 table.

If Publishers has a numeric primary key, chances are that your combo box has 2 columns, of which the first is hidden because its column width it set to 0. The value of Publisher will be numeric in that case.

If the Publishers table has an AutoNumber field as primary key, the following should work:

Private Sub Publisher_NotInList(NewData As String, Response As Integer)
If MsgBox(“The type ” & NewData & ” does not occur not in the list.” & vbCrLf & _
“Do you want to add it?”, vbYesNo + vbQuestion) = vbYes Then
Dim strSQL As String
strSQL = “INSERT INTO Publishers ([Short Name]) VALUES (” & _
Chr$(34) & NewData & Chr$(34) & “)”
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Publisher.Undo
Response = acDataErrContinue
End If
End Sub

Otherwise, please specify:
– The names and data type of the fields of the Publishers table.
– The row source, the number of columns and the column widths of the Publisher combo box.

Reply | Quote

Ha! It worked! Thanks very much HansV. It’s so nice to have a place where dummies like me can come and get help.
Clark

Reply | Quote