Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Combo box Question

    Hi guys I have a question about combo boxes. We have a two combo boxes one is the [Catagory for hours] so when you look at the drop down it says "LifeSkills";"Education";"Culture";"Travel";"J ob Skills Training";"Parenting";"ITP";"Phone Calls";

    the next combo box has the subcatagories for the[Catagory for hours] its called [Services covered] and when you open the drop down here is what you see "Academic Assessment";"MER Completion- TANF";"Career Training Plan";"Counseling";"Phone Call";"EHS Training/Home Visits";"Training/Office Visits";"Indpendent Study";"Indpendent Job Skills Training";"Career Training Workshop";"Resume Writing";"SCAIR Soaring Eagles";"Regalia";"Pow Wow Participation";"GED/High School Diploma Preparation";"Adult Basics Education";"Computer Skills";"Indpendent Study";"Drivers Education";

    What I would like to happen is that when the user chooses ITP from the [Catagory for Hours] the only thing that shows up in the [Services Covered] combo box is "Academic Assessment";"MER Completion- TANF";"Career Training Plan";"Counseling";"Phone Call";
    and when they choose Parenting the only thing that will show in the [Services Covered] is EHS Training/Home Visits";"Training/Office Visits";"Indpendent Study";

    So basically when the User chooses a Catagory the only thing that shows up in the subcatagory combo box [Services Covered] is the subcatagories pertaining to that Catagory. is this possible??

    the reason why I have to do it this way is becuase there is a report that is based off these two combo boxes and the colum headings of the report are the based offf the [catagory for hours] the headings are Parenting, ITP, Job Skills training, Education and travel. so the combo boxes have to be seperated. Does that make sense??

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This type of thing?

    Cascading Combos
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Not sure If I'm doing this right, I have no idea how to do this

    Private Sub Catagory_for_hours_AfterUpdate()
    Me.cboServices_Covered = vbNullString
    Me.cboServices_Covered.Requery
    End Sub


    Dim strSource As String

    strSource = "SELECT Services_Covered " & "Academic Assessment", "MER Completion-TANF", "Career Training Plan", "Counseling", "Phone Call"
    "FROM TanfActivity_tbl " & _
    "WHERE Catagory_for_hours = '" & ORDER BY "
    Me.cboCities2.RowSource = strSource
    Me.cboCities2 = vbNullString

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Which method are you going to go with? The first looks okay, presuming the row source of the second refers to the first. The SQL built in strSource is not even close to valid SQL.
    Paul

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If it's a combobox and you need to change the rowsource, I personally like to make simply querys and set the rowsource to whatever the query name is depending on the situation (then I don't have to worry about strsource and constructing a sql string and worry about syntax, etc...)

    ie.

    me!mycomboboxname = null
    me.mycomboboxname.rowsource = "SomeQuery"

    (important: make sure you're keeping the first column consistently the same in each query so it's returning the ID field or whatever field you need to have the combobox return.) Otherwise you also need to manipulate the boundcolumn property for the combobox.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by pbaldy
    Which method are you going to go with? The first looks okay, presuming the row source of the second refers to the first. The SQL built in strSource is not even close to valid SQL.

    I got stuck on the bottom part

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I'll keep at it, stink at programming still learning

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just a quick tip I spotted in your SQL coding...

    strSource = "SELECT Services_Covered " & "Academic Assessment", "MER Completion-TANF", "Career Training Plan", "Counseling", "Phone Call"
    "FROM TanfActivity_tbl " & _
    "WHERE Catagory_for_hours = '" & ORDER BY "
    There are numerous problems here. There needs to be a comma after Services _Covered, All those fields with spaces need to be surrounded by [square brakets] and you have an unfinished WHERE clause.

    Here's a "corrected" one. Forgive if I have missed something here, I've really only skimmed the topic.

    strSource = "SELECT Services_Covered, " & "[Academic Assessment]", "[MER Completion-TANF]", "[Career Training Plan]", "Counseling", "[Phone Call]"
    "FROM TanfActivity_tbl " & _
    "WHERE Catagory_for_hours = " & <some value> & " ORDER BY SomeFieldName;"

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by desireemm
    I'll keep at it, stink at programming still learning

    NO! You don't "stink" at programming. You're only "learning" programming. After you have a few years under your belt and you've developed a few applications (even incorrectly), you learn what to do and what not to do.

    It's the developers who are stubborn in their ways and don't want to learn anything new (or don't even make an attempt to learn better ways of developing) that I classify as "stinking" at programming!!

    After 25 years of developing, I STILL learn something new almost daily.
    Last edited by pkstormy; 09-05-09 at 14:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by StarTrekker
    Just a quick tip I spotted in your SQL coding...


    There are numerous problems here. There needs to be a comma after Services _Covered, All those fields with spaces need to be surrounded by [square brakets] and you have an unfinished WHERE clause.

    Here's a "corrected" one. Forgive if I have missed something here, I've really only skimmed the topic.

    strSource = "SELECT Services_Covered, " & "[Academic Assessment]", "[MER Completion-TANF]", "[Career Training Plan]", "Counseling", "[Phone Call]"
    "FROM TanfActivity_tbl " & _
    "WHERE Catagory_for_hours = " & <some value> & " ORDER BY SomeFieldName;"

    HTH

    Oh ok I see, I understand the sql part, I was thinking I have to write it like VB code not sql. I understand that part thank you

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I still prefer just setting the rowsource to a query I've designed. I often see developers spend hours upon hours or days upon days troubleshooting sql syntax when they can use the query designer and avoid all that time.

    Another thing I like to do is to design the query (in the query designer), and then in the upper left corner, click the dropdown from "Design" view to "SQL" view. Then I just copy the syntax, put it in my code, get rid of the extra " that's in the syntax (or other unneeded syntax), and I have my sql statement. This works especially nice when you have joins and is a great way to learn about how to construct sql syntax.

    If you're a graphical type of developer like me, this works great. Otherwise I use it to qc my sql statement. Some developers prefer to just write out the syntax though. It's when they struggle with it for days that worries me.

    Since you've done VB, you're probably the non-graphical type. But the query designer is always there if you need it.
    Last edited by pkstormy; 09-05-09 at 15:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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