Results 1 to 14 of 14
  1. #1
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85

    Entering Values in ComboBox that populate Table

    I have a form that contains comboboxes that lists the values in a particular table to choose from. Problem is that I cannot enter new values in the comboboxes because I get an error message about the column width. For instance, if I have a combobox of cities to choose from (Atlanta, DC, Miami, San Francisco, Dallas), I can drop down this combobox on the form and choose one of them for this entry. But if I try to add Philadelphia by typing it in the combobox on the form, I get an error about columnwidths. Any idea on how to fix this? Thanks.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Please post the error number and error message text. There are a few errors that could relate to this, but I can't see how the width of a column would be a problem. You can have more characters in a column than what will fit in there, but IIRC, what doesn't fit is just truncated. From the behaviour you describe, I'd also expect it could be an issue with the number of characters in the list value not fitting into the table field, assuming you did anything to set that. The default is 255, but you can set it to anything less. However, you wouldn't get that error message. Thus, the number and message would be helpful.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    To clarify. I get this error when I try to change the combobox property "limit to list" to "no". I get the following error:'

    Msoft Access can't set the limittolist property to No right now. 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.

    Thanks again for any help.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The first column (left to right) that is not 0" in width is the column to which the limit to list property applies. That column cannot be 0", yours probably is.
    From what I have read, you can also get this error if the bound column is bound to a lookup field in a table (lookup fields in table = bad).

    IIRC, if you set the bound column property to any column other than the first visible column, or if you set BoundColumn 0, the LimitToList property is automatically set to Yes.
    You might also want to research Not In List event to see if that will help you.

    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    I agree with Micron, NotInList Event is much better
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    Thank you all!

  7. #7
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    Do you have an example of NotinList procedure code? Thanks.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Hans

    Here with one example:-

    Code:
    Dim strSQL1 As String
        Dim i As Integer
        Dim Msg As String
     
        'Exit this sub if the combo box is cleared
        If NewData = "" Then Exit Sub
     
        Msg = "'" & NewData & "' The city is not currently in the list." & vbCr & vbCr
        Msg = Msg & "do you want to add it ?"
     
        i = MsgBox(Msg, vbQuestion + vbYesNo, "This is a new city...")
        If i = vbYes Then
                strSQL1 = "Insert Into tblCity ([City]) " & _
                     "values ('" & NewData & "');"
     
            CurrentDb.Execute strSQL1, dbFailOnError
            Response = acDataErrAdded
              
        End If
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    That worked. Thank you. Once the new value is entered and the dropdown is updated it immediately goes to the next record in the form. Is there any way to get it to stay on that same record in the form after adding the value to the dropdown?

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Hans
    After the update of the Combobox the normal is for View is to remain on the current Record.

    You must have other VB to go to a new record?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    I sure don't see any other vb to go to a new record. Not sure what is up with that. How can I get the new data I insert to display in the drop down in alphabetical order? Do I need to add an order statement to the vb with respect to the table that stores the data? Thanks.

  12. #12
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    I notice that even without the code if I choose a value from the combobox and then click enter it goes to the next record without giving me a chance to enter data into the subform. So, it appears to be an issue with my form and not the code.

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    For your main form try this:

    Click image for larger version. 

Name:	cycle.png 
Views:	13 
Size:	11.6 KB 
ID:	47077

  14. #14
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    that worked!! Many thanks!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 11-07-2018, 06:15 AM
  2. Replies: 8
    Last Post: 07-09-2014, 08:00 AM
  3. Replies: 2
    Last Post: 03-17-2014, 11:51 AM
  4. Replies: 2
    Last Post: 08-22-2012, 07:59 AM
  5. Replies: 4
    Last Post: 08-15-2011, 10:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums