none
Combobox not holding value RRS feed

  • Question

  • Hello,

    I have a popup form that has a number of combo boxes on it. The form is used to search our inventory and return the results to an inventory form. Everything works well, but one issue has been bugging us for a while. On one of the combo boxes, after a selection is made, if you return to it, the combox list goes back to the first record.

    There are other combo boxes on this form when after a selection is made, it retains the value and goes back to that value if you enter that combo box. I have compared the two combo boxes and they are identical except that the one that is not working properly is not limited to the list. But if I change it to "limit to list", the behaviour is the same. 

    Other notes: The search results appear in a subform and the focus moves between mainform & subform. The combo box that is troublesome is the first field on the form and tabstop 0 (I haven't tried moving it, but did remove the tab stop. The combo box that works well only has one field, whereas the other has 3). Both boxes are bound to a temporary table used for search histories.

    I stepped through all the code and there are no refresh, requery, recalc, etc.

    Any ideas greatly appreciated.

    Thank you,

    Albert


    Albert S

    Tuesday, June 18, 2019 6:14 PM

All replies

  • Can you provide a sample of the form to review?

    Any chance the bound column isn't unique in its values?


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, June 18, 2019 6:38 PM
  • Ok, you are correct. There is a possibility that there are duplicates. It contains a list of composer names (we are a music store) and could be some dups. The composers table has an ID column.

    Do you think there is a way to overcome this problem?

    Thank you,

    Albert


    Albert S

    Tuesday, June 18, 2019 6:49 PM
  • Hello,

    I checked another combo box on the form that has duplicate records and it doesn't show this behaviour. But, it has some code behind & is unbound. I'll post back as soon as I can find out what is going on.

    Thank you!

    Albert


    Albert S

    Tuesday, June 18, 2019 6:51 PM
  • Ok, I tried to set up the composers field like one of the other unbound fields. I think the problem stems from the other fields lookup being ID based and the composer field lookup being text based. The other fields have hidden text boxes that are storing their IDs and those IDs are used in the search query. Whereas the composer field can't do this since all of the searches on that field are approximate - i.e. someone enter "Bach" and get all the results with "Bach*" as composer.

    I'll keep experimenting to see if I can come up with something.

    Thank you,

    Albert


    Albert S

    Tuesday, June 18, 2019 7:37 PM
  • If you get stick, feel free to post a sample db for us to examine.  We'll be more than happy to look things over and help you as best we can.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, June 18, 2019 7:50 PM
  • Personal names alone are not really suitable as a basis for searching.  With Bach, Presto Music lists the following composers:

      Bach, Heinrich (1615-92)
      Bach, Jan (b.1937)
      Bach, Johann (1604-73)
      Bach, Johann Bernhard (1676-1749)
      Bach, Johann Christian (1735-82)
      Bach, Johann Christoph (1642-1703)
      Bach, Johann Christoph Friedrich (1732-95)
      Bach, Johann Ernst (1722-77)
      Bach, Johann Friedrich (1682-1730)
      Bach, Johann Jacob (1682-1722)
      Bach, Johann Lorenz (1695-1773)
      Bach, Johann Ludwig (1677-1731)
      Bach, Johann Michael (1648-94)
      Bach, Johann Michael (1745-1820)
      Bach, Johann Nikolaus (1669-1753)
      Bach, Johann Sebastian (1685-1750)
      Bach, Wilhelm Friedemann (1710-84)
      Bach, Wilhelm Friedrich Ernst (1759-1845)

    You can't reliably use the LIKE operator or you'd also get:

      Bacharach, Burt (b.1929)
      Bache, Francis Edward (1833-58)
      Bachelet, Alfred (1864-1944)
      Bachmann, Arthur Marc (b. 1961)
      Bachofen, Johann Caspar (1695-1755)
      Bachrich, Ernst (1892-1942)

    Bach is an extreme example;  somebody once calculated that there were around 140 members of J S Bach's extended family in the music business!  Even today, though, there's John Adams and John Luther Adams.

    So in addition to the name you really need to include some other data in a combo box's list to differentiate between people of the same name.  You can do this by including additional columns in the control's drop down list.  In Presto's lists the composers' dates do this, e.g.

      Bach, Johann Michael (1648-94)
      Bach, Johann Michael (1745-1820)

    You'll find a more prosaic example of how to do this in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    In the section on 'entering data via a form/subforms' you'll find a simple orders form in which a customer can be selected by name in a combo box.  To differentiate between customers of the same name their address data is included in the drop down list by means of the following query as the control's RowSource property:

    SELECT Contacts.ContactID, [LastName] & ", " & [FirstName] AS Customer, Contacts.Address, Cities.City, Regions.Region, Countries.Country
    FROM Countries INNER JOIN (Regions INNER JOIN (Cities INNER JOIN Contacts
    ON Cities.CityID = Contacts.CityID) ON Regions.RegionID = Cities.RegionID)
    ON Countries.CountryID = Regions.CountryID
    ORDER BY Contacts.LastName, Contacts.FirstName;

    By virtue of the control's AutoExpand property, as the user enters types each character the nearest match will progressively be selected, so, in your case, if a user entered Bach they'd then be able to quickly scroll down to the relevant one.

    The most important point, however, is that the distinct ContactID column is the one referenced by the BoundColumn property of 1, so this is the value of the control.  The ContactID column is hidden by setting the ColumnWidths property to 0cm;3cm;4cm;3cm;3cm;3cm, but is the value of the control when a row is selected, and consequently identifies a specific contact even if there are multiple contacts with the same name.  An unbound search combo box set up in this way would therefore find the specific contact with the ContactID in question.


    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, June 19, 2019 5:21 PM Typo corrected.
    Wednesday, June 19, 2019 5:16 PM
  • Thank you for the detailed answer! I did try one where the ComposerID (field 0) was hidden in the dropdown and the bound column was field 1 (Name). It didn't quite work out. I will try it again today to see if I can get ti to work.

    Take care,

    Albert


    Albert S

    Wednesday, June 19, 2019 5:32 PM
  • Ok, I eliminated all the duplicates in that table, but still the same behaviour. So the uniqueness doesn't seem to be the problem.

    Thank you,

    Albert


    Albert S

    Wednesday, June 19, 2019 11:41 PM
  • What are the following properties of the combo box?

    RowSource
    BoundColumn
    ColumnCount
    ColumnWidths

    If the RowSource property is the name of a query, what is the query's SQL statement?

    Ken Sheridan, Stafford, England

    Thursday, June 20, 2019 10:23 AM
  • RowSource = Table/Query:

    SELECT tblComposers.ID, tblComposers.ComposerAuthor, tblComposers.ComDates
    FROM tblComposers
    ORDER BY tblComposers.ComposerAuthor;

    BoundColumn: 2

    ColumnCount: 3

    ColumnWidths: 0";2.25";1.25"

    Thank you,

    Albert


    Albert S

    Thursday, June 20, 2019 5:56 PM
  • The BoundColumn property should be 1.  The value of the control will then be the distinct ID for the selected composer.  With a bound combo box the ControlSource would then usually be a foreign key in a referencing table.  With an unbound search combo box the code which implements the search would do so on the basis of the ID value.  The following example is code from the AfterUpdate event procedure of an unbound combo box which navigates to a selected contact record on the basis of the ContactID in the first (hidden) column in the control's RowSource query:

    Private Sub cboGotoContact_AfterUpdate()

        Const MESSAGETEXT = "No matching record"
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        If Not IsNull(ctrl) Then
            If ctrl = 0 Then
                ' go to new record and move focus to FirstName control
                DoCmd.GoToRecord acForm, Me.Name, acNewRec
                Me.FirstName.SetFocus
            Else
                With Me.RecordsetClone
                    .FindFirst "ContactID = " & ctrl
                    If Not .NoMatch Then
                        ' go to record by synchronizing bookmarks
                        Me.Bookmark = .Bookmark
                    Else
                        MsgBox MESSAGETEXT, vbInformation, "Warning"
                    End If
                End With
            End If
        End If
        
    End Sub

    This example also allows the user to navigate to an empty new record.  The code in the Else construct is that pertinent to navigating to an existing record.

    Ken Sheridan, Stafford, England

    Thursday, June 20, 2019 10:05 PM
  • Hello,

    Thank you for the message. Unfortunately, this form is just a search form. A query string is built off this form and sent to SQLServer. The search form has 22 fields and 10 check boxes, so the query can be pretty complex. The query works fine with all the possibilies.

    I was hoping that it would be a simple fix, but I don't think it will be. There are two other comboboxes on this form that retain the selection, but you can't search by approximate values on them.

    I'll keep experimenting and see if I can come up with something.

    Thank you,

    Albert


    Albert S

    Thursday, June 20, 2019 11:47 PM