Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add to list box (Access 2002 SP-2)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add to list box (Access 2002 SP-2)

    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)

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add to list box (Access 2002 SP-2)

    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.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add to list box (Access 2002 SP-2)

    Good point. I forgot the obvious answer. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to list box (Access 2002 SP-2)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add to list box (Access 2002 SP-2)

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

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to list box (Access 2002 SP-2)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Add to list box (Access 2002 SP-2)

    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.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to list box (Access 2002 SP-2)

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add to list box (Access 2002 SP-2)

    In <post#=248963>post 248963</post#> farther up in this thread, you state
    <hr>... based on the the table "In-Print Inventory2" ...<hr>
    but in the SQL you use tblIn-Print Inventory2. Are you absolutely sure about the name? Any typo will cause an error message.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to list box (Access 2002 SP-2)

    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)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add to list box (Access 2002 SP-2)

    On looking back at an earlier reply by you in this thread, I see that
    <hr>there is a field called "Publisher" which is a combo box based on the field "Short Name" in the table "Publishers".<hr>
    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.

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to list box (Access 2002 SP-2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •