Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43

    Cascading Combo Box, Keep Last Choice and Limit List

    Hi All,



    I have 2 questions.

    1. How to maintain combo box shortlisted options when the form is reopened?

    2. How can I have the concatenated (first name + last name) appear in combo box 2 when I make a selection from the short list?


    Private Sub CboCompany_Change()
    Dim Contact As String


    Contact = "SELECT [tblContacts].[ID]," & _
    " [tblContacts].[CompanyID]," & _
    " [tblContacts].[FirstName]," & _
    " [tblContacts].[LastName] " & _
    "FROM tblContacts " & _
    "WHERE [CompanyID] = " & Me.CboCompany.Value
    Me.CboContact.RowSource = Contact
    Me.CboContact.Requery
    End Sub

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Suggest you use the After Update event instead as the Change event fires after every key entry.

    Concatenation -add a 'field' tblContacts.FirstName & " " tblContacts.LastName as the second column and set column widths equal to 0;3;0;0 etc

    Do you really need CompanyID in the second combo?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Which field of query you are displaying in your CboContact? You can have many columns in query used as combo box source, but:
    1. You can link the combo box only to one field;
    2. You can show in combo box only one field.

    You have to determine a lot of properties with combo box to determine those settings. ControlSource (determines the table field the bound combo box is linked to), RowSource (the query or the array of values the combo box is getting data from), BoundColumn (determines the column linked with source field), ColumnWidths (determines the column displayed - the 1st one with width > 0), ColumnCount (must be >= as any of column numbers used in previously mentioned properties).

    So the simplest design for CboContact would be:
    Code:
    ControlSource = [ContactID] (I replaced [ID] With [ContactID]);
    RowSource = 'SELECT c.ContactID, Trim(c.FirstName & " " & & cLastName) AS [FullName] FROM tblContacts c WHERE c.CompanyID = 1' (for CompanyID you can use any existing CompanyID value);
    BoundColumn = 1;
    ColumnCount = 2;
    ColumnWidths = '0, 2.5' (or '0; 2.5, I myself have '0; 2,5' - it depends on regional settings and I don't have a clue what will be delimiter in US settings).
    You need an AfterUpdate (Not Change!) event for CboCompany to update the RowSource of CboContact whenever another company is selected/entered (Change event is fired for every keystroke). Something like (assumed CompanyID is autonumeric/numeric)
    Code:
    Private Sub CboCompany_Change()
    ...
    Me.CboContact.RowSource = "SELECT c.ContactID, Trim(c.FirstName & ' ' & & cLastName) AS [FullName] FROM tblContacts c WHERE c.CompanyID = " & CbbCompany
    ...
    End Sub
    You also must have an OnCurrent event for form, where you update Me.CboContact.RowSource depending on value of CbbCompany (and you have to cope with case, when current record is new one).

  4. #4
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Thank you for the detailed response.

    The Cbo has been setup as follows,

    1. Company Cbo: Control Source = CompanyID, Row Source = in form query with two fields (CompanyID and Company Name), Bound Column 1, Column Count 2 (0cm;2.5cm).

    2. Contact Cbo: Control Source = ContactID, Row Source =in form query with 3 fields (ContactID, Honorific, ContactName (this is a concatenated field), Bound Column 1, Column Count 3 (0cm;0cm;2.5cm).

    I have moved the code to the After Update.

    However...

    I dont fully understand your response. Are you saying I should add "CboCompany_Change()" to hold the contact name in the second combo box when the form is closed and reopened? Picture below shows how my form appears to the user.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	32 
Size:	5.0 KB 
ID:	34126

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Sunny8760 View Post
    I dont fully understand your response. Are you saying I should add "CboCompany_Change()" to hold the contact name in the second combo box when the form is closed and reopened? Picture below shows how my form appears to the user.
    Sorry! I copied the row from your post, but forgot to edit it There must be
    Private Sub CboCompany_AfterUpdate()

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Sunny8760 View Post
    <snip>
    2. Contact Cbo: Control Source = ContactID, Row Source =in form query with 3 fields (ContactID, Honorific, ContactName (this is a concatenated field), Bound Column 1, Column Count 3 (0cm;0cm;2.5cm).
    You could use code (from Arvil)
    Code:
    RowSource = 'SELECT c.ContactID, Trim(c.Honorific) & " " & Trim(c.FirstName & " " & & cLastName) AS [FullName] FROM tblContacts c WHERE c.CompanyID = 1'
    With Column Count 2 (0cm;2.5cm)

    Now the Honorific is included with the name and back to 2 columns.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ssanfu View Post
    Code:
    RowSource = 'SELECT c.ContactID, Trim(c.Honorific) & " " & Trim(c.FirstName & " " & & cLastName) AS [FullName] FROM tblContacts c WHERE c.CompanyID = 1'
    This will let FullName preceeded with space when Honorific is an empty string, and will prevent ordering by FullName in case OP wants to add ORDER BY Clause. And it occurred me, that in case some of those 3 fields are empty, the query will not work! So the right query string must be
    Code:
    RowSource = 'SELECT c.ContactID, Trim(Nz(c.Honorific,"") & " " & Trim(Nz(c.FirstName,"") & " " & Nz(cLastName,""))) AS [FullName] FROM tblContacts c WHERE c.CompanyID = 1'

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The only problem with code like:
    Code:
    FullName1: Trim(Nz(c.Title,"") & " " & Trim(Nz(c.FirstName,"") & " " & Nz(cLastName,"")))
    is that you get unwanted spaces if the title or first name fields are blank

    The following isn't quite as neat but solves that issue:
    Code:
    FullName2: IIf(Nz([Title],"")<>"",[Title] & " ","") & IIf(Nz([FirstName],"")<>"",[FirstName] & " ","") & IIf(Nz([LastName],"")<>"",[LastName] & " ","")
    I've allowed for missing last names above though this could just be
    Code:
    FullName3: IIf(Nz([Title],"")<>"",[Title] & " ","") & IIf(Nz([FirstName],"")<>"",[FirstName] & " ","") & [LastName]
    Example results:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	25 
Size:	10.5 KB 
ID:	34142
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ridders52 View Post
    The only problem with code like:... is that you get unwanted spaces if the title or first name fields are blank
    There is a reason why there are two Trim() functions in formula!
    Code:
    =Trim(Nz(c.FirstName,"") & " " & Nz(c.LastName,"")) 
    returns either "", "FirstName", "LastName", or "FirstName LastName". No leading/trailing spaces for whatever combination. Lets name the result string as String1.
    Code:
    =Trim(Nz(c.Title,"") & " " & String1)
    returns either "", "Title", "String1", or "Title String1". Again no leading/trailing spaces.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I used your version as FullName1.
    The extra spaces are caused by the " " between fields which Trim doesn't affect
    So 'Ms Brown' has a double space between the fields
    Jack Straw with no title has a space at the front and Jones hjas 2 leading spaces

    That was what the yellow highlighting was showing
    Do you see what I'm getting at?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    I don't get any trailing or leading spaces!
    Attached Files Attached Files

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Arvil

    Yes I can see that.
    I had missed out the table name in each expression as all fields were in the same table.
    Code:
    Trim(Nz([Title],"")) & " " & Trim(Nz([FirstName],"")) & " " & Nz([LastName],"")
    causes the leading spaces & double spaces I showed earlier

    However, using the table prefix removes them:
    Code:
    Trim(Nz([Contacts].[Title],"") & " " & Trim(Nz([Contacts].[FirstName],"") & " " & Nz([Contacts].[LastName],"")))
    Now the question is WHY? To my mind they should produce the same result. Any ideas?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ridders52 View Post
    Now the question is WHY? To my mind they should produce the same result. Any ideas?
    Honestly I haven't slightest idea! Maybe an oddity of newer versions!

    In older versions there was no difference in using table names or aliases in single-table queries. As I almost always use aliases in queries lately, I haven't encountered such behavior before.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Nor me.
    I've used my version for over 15 years (Access 2000 onwards).
    Your code is neater but as I say I normally omit table names where there is only 1 table in a query.

    There is another even more concise method of concatenating using '+' instead of '&' which I meant to include originally
    This can be useful to handle nulls

    No need for Trim, Nz or table prefix!!!

    Code:
     [Title] + " " & [FirstName] + " " & [LastName]
    No leading, trailing or double spaces!
    Try it. What do you think?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ridders52 View Post
    There is another even more concise method of concatenating using '+' instead of '&' which I meant to include originally
    This can be useful to handle nulls

    No need for Trim, Nz or table prefix!!!
    ...
    No leading, trailing or double spaces!
    Try it. What do you think?
    Have read about this, and memorized it.

    By the way, when I created this test database, I created the query step-wise. There last step was to include Nz(). When I checked the query before this (I had 4 rows in Contacts table then), I was baffled as all records were returned correctly. It looks like query will work without Nz() too! As in this case (combobox row source) the speed was not essential, I included Nz() anyway - to be on safe side. (You never know with MS )

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combo box limit to list doesnt limit
    By mcmcd99 in forum Access
    Replies: 6
    Last Post: 11-09-2017, 07:31 PM
  2. Replies: 0
    Last Post: 03-19-2017, 01:29 PM
  3. Cascading Combo / List Boxes
    By plowe in forum Programming
    Replies: 5
    Last Post: 09-07-2012, 10:55 AM
  4. Replies: 2
    Last Post: 05-27-2011, 08:12 AM
  5. List box update from combo box choice
    By allykid in forum Forms
    Replies: 1
    Last Post: 03-08-2011, 10:06 PM

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