Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add to combo box if not there (97 sr2)

    I have a combo box that pulls info from a table of items to choose from (tblObjectType). If a user clicks on the drop down and the item he/she wants isn't listed, how can I set it up so that they can type the information and it will add it to both the source table (tblObjectType) and the destination table (tblObjects). The way it's set up now, if the user types something in, there is an error that says it's not a valid entry. I was thinking that I could set Limit to List to No. But, will that add the new entry to both tables?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to combo box if not there (97 sr2)

    I came across the article mentioned below on the Internet several months ago.
    It is headed Access 2000, but I had a quick glance at the code and I can't see any obvious reason why it why it won't work in access 97. It uses DAO code and not ADO.

    ACC2000: Use NotInList Event to Add a Record to Combo Box (Q197526)

    The information in this article applies to:

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to combo box if not there (97 sr2)

    Put this code in the combo box "not in list" event.

    <pre> Dim dbs as DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg as String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblObjectType")

    strMsg = "The object '" & NewData & "' is not in the list. Would you like to add it?"

    If MsgBox(strMsg, vbYesNo + vbInformation, "Object Not Found") = vbYes Then
    With rst
    .AddNew
    ![Field name from table here] = NewData
    .Update
    End With
    Response = acDataErrAdded
    Else
    Me.[combo name here].Undo
    Response = acDataErrContinue
    End If

    rst.Close
    Set dbs = Nothing
    Set rst = Nothing</pre>


    There are many great variations to doing this if you do a search on "notinlist" in this forum.

    Hope that helps

    Don

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add to combo box if not there (97 sr2)

    Wow! Thanks for the help!

Posting Permissions

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