Results 1 to 12 of 12

Thread: Combo Box

  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box

    I have a form that is used to populate Table A. On that form are 2 Combo Boxes. In Combo Box 1, I select the name of a company from another table (Table [img]/forums/images/smilies/cool.gif[/img]. How do I get Combo Box 2 to list ONLY the transactions in Table B associated with the company I selected in Combo Box 1? I created a query that will return the information I need, but the query will NOT use the company name from Combo Box 1 as it's search criteria. When I run the form, I'm presented with a separate dialog box in which I must, again, type the company name. This is extremely frustrating. I can't believe I can't make this happen. I look forward to and greatly appreciate any help you can provide. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box

    Check this post for a description of a method to use.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box

    Charlotte,

    Thank you for the guidance. I still can't get things to work, however. Here's the code I've created:

    Private Sub Company_AfterUpdate()
    'This is the AfterUpdate event of the Company combo box
    Call SetFindSrc
    End Sub
    Public Sub SetFindSrc()
    'Modified 6/12/01
    Dim strSQL As String

    If Not IsNull([Company]) Then
    strSQL = "SELECT DISTINCTROW tblSUBINFO.AgreementType, " _
    & "tblSUBINFO.AgreementNumber " _
    & "FROM tblSUBINFO " _
    & "WHERE tblSUBINFO.Company Like Company"
    Me!Agreement.RowSource = strSQL
    Me!Agreement.Requery
    Me!Agreement.SetFocus
    End If
    End Sub

    Again, what I'm trying to do is select a company name from a combo box on a form. After I select a company, I want to populate another combo box on the same for with just those actions associated with that company. The company name and actions are in Table A. The form is being used to populate Table B. When I run this code as the After Update event for the company combo box, the other combo box remains blank, with nothing in the drop down list. I know this is confusing and greatly appreciate the help. Thank you.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box

    Look at Charlotte's post again carefull. it's not readily apparent due to the line continuation characters but you have to concatenate the company field in the where clause like this:

    "Where company like '" & [company] & "*';"

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box

    Paul,

    Thanks for the guidance. But, as my code below shows, I made your corrections but I still can't get it to work. Again, this code is the AfterUpdate event for the Company combo box. However, when I pick a company, the Agreements box remains blank. Is this because I can't assign the RowSource for the Agreement Combo Box on the current form (Me!Agreement) to strSQL? I'm sorry if this is confusing. I greatly appreciate the help.

    Private Sub Company_AfterUpdate()
    'This is the AfterUpdate event of the Company combo box
    Call SetFindSrc
    End Sub
    Public Sub SetFindSrc()
    'Modified 6/12/01
    Dim strSQL As String

    If Not IsNull([Company]) Then
    strSQL = "SELECT DISTINCTROW tblSUBINFO.AgreementType, " _
    & "tblSUBINFO.AgreementNumber " _
    & "FROM tblSUBINFO " _
    & "WHERE tblSUBINFO.Company Like '" & [Company] & "*';"
    Me!Agreement.RowSource = strSQL
    Me!Agreement.Requery
    Me!Agreement.SetFocus
    End If
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box

    First, you change the line
    <hr>& "WHERE tblSUBINFO.Company Like Company"<hr>
    to this
    <hr>& "WHERE tblSUBINFO.Company Like '" & [Company] & "'"<hr>
    If that didn't work, are you actually using the company name as the key, or is there a numeric key behind it? If it's a numeric key, take the single quotes out from around the [Company] value and definitely lose the asterisk. You're picking from a combobox, so you won't have any partial values to match.

    Then if it didn't work try changing the If Not IsNull([Company]) to If [Company]<>"" or else try If [Company].ListIndex<>-1. Company is a string, I assume, so it can't really be null. Try testing for an empty string or use the ListIndex to tell you if a selection has been made.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box

    Charlotte,

    I appreciate your patience. I still can't get this thing to work. Here's my current code:

    Private Sub Company_AfterUpdate()
    'This is the AfterUpdate event of the Company combo box
    Call SetFindSrc
    End Sub
    Public Sub SetFindSrc()
    Dim strSQL As String

    If [Company] <> "" Then
    strSQL = "SELECT DISTINCTROW tblSUBINFO.AgreementNumber " _
    & "FROM tblSUBINFO " _
    & "WHERE tblSUBINFO.Company Like '" & [Company] & "'"
    Me!Agreement.RowSource = strSQL
    Me!Agreement.Requery
    Me!Agreement.SetFocus
    End If
    End Sub

    Again, I have a form with 2 combo boxes-Company and Agreement. I want to select the Company and have only those agreements associated with that company show up in the Agreement combo box. The current form is used to populate Table tblSUBEVAL. The Company and Agreement data is stored in Table tblSUBINFO. What am I doing wrong? Thank you for your help and patience.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box

    Put a breakpoint on this line:

    Me!Agreement.RowSource = strSQL

    In the VBE, show the Immediate Window/debug window and type in "? strSQL" without the quotes there. That will show you want was actually put into the SQL string. End the code and then take that SQL string and paste it into the SQL window of a new query and try to run it. Does it return any rows? If not, then the problem isn't one that can be solved here, it's a problem with your query having the correct fields/field names it needs to return records or not having any matches for the criteria you gave it.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box

    Charlotte,

    I did exactly what you suggested and the query return the correct rows. I just can't get those rows to show up in the Agreement combo box. Suggestions?

    Thanks for your help and patience.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box

    What exactly are you expecting to *see* in the Agreement combobox? All you're asking the query to return is AgreementNumber., so I assume that's all you want to display. Now look at the combobox control itself. What are you showing as the RowSourceType? How many columns are you showing for the combobox and what are their widths? What are you showing as the RowSource (it should be blank)?
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box

    Charlotte,

    Thank you. I finally got it. After changing the Row Source Type to Table/Query, it worked. I'm sorry it took so long to get there, but I'm learning this as I go along. Thanks for the help and education.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo Box

    That's OK, other Loungers will learn by following the thread and will be able to avoid that kind of problem. And don't feel bad--I occasionally do this myself, even after all these years of working with Access.
    Charlotte

Posting Permissions

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