Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not In List Revisited (A2K)

    Edited to provide link to post by HansV - see <!help=19>Help 19<!/help>

    In [Post:#=300488] back in Oct. '03, the following problem was posted:

    I have a combo box on a form that gets its' list from a table. I'm trying to set the LimitToList property to No, and I'm getting this error message:
    "The first visible column which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first, and then set the LimitToList property."

    Having exactly the same problem, I borrowed and altered the pertinent names ( identified with the ' xxxx comments ) in the following code:

    Private Sub txtSupplierName_NotInList(NewData As String, Response As Integer)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qry AGMEst - Models - Quotes") ' xxxxxxxxxx Name of Source Query

    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
    ![CCM SupplierName] = NewData ' xxxxxxxxxx Name of Data Source Field
    .Update
    End With
    Response = acDataErrAdded
    Else
    Me.[txtSupplierName].Undo ' xxxxxxxxxx Name of ComboBox
    Response = acDataErrContinue
    End If

    rst.Close
    Set dbs = Nothing
    Set rst = Nothing

    End Sub

    Here's what happens when I attempt to enter an item not in list:

    Object Not Found: The Contractor Name 'BaseCrete' is not in the list. Would you like to add it?
    I click on Yes and get:
    The text you entered isn't an item in the list.
    Select an item from the list
    Cheers,
    Andy

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

    Re: Not In List Revisited (A2K)

    You must either provide detailed information about the combo box (Column Count, Column Widths, Row Source [if it is the name of a table, what are the fields of the table?], Bound Column) or attach a stripped down copy of the database.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List Revisited (A2K)

    Hans,

    Please see attached,
    Cheers,
    Andy

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

    Re: Not In List Revisited (A2K)

    You're adding a record to the wrong table. If the user enters a non-existing supplier, you should create a new record in the suppliers table, not in the Quotes table. You should set the CorpName field in this table.

    Notes:
    - I would open and close the recordset only if you want to add a record.
    - Please tick "Require Variable Declaration" in Tools | Options in the Visual Basic Editor.
    - Using spaces in the names of tables, fields etc. makes life more difficult.

    I have attached a modified database. I had to remove 12 fields from the row source of the combo box because they were not present in the underlying table.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List Revisited (A2K)

    Hans,
    Thanks again for the words of wisdom. I'll have a go at it right now.
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List Revisited (A2K)

    Hans,

    Worked like a charm. This is a serious question. Have you ever considered trying to write a book on Access Programming? Yours would be made up of real life situations, and not just whimsical thoughts. I'd be first in line to purchase it.
    Thanks again,

    Andy
    Cheers,
    Andy

Posting Permissions

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