Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95

    Unanswered: 2 questions: Assigning values to combobox/ Showing 2nd 'hit'

    Hallo humans,

    1. Is it possible to assign values dynamically to a combobox with VBA? I want the values to change according to which roomnumber (in another combobox on the form) I select. Or is there another way to do this? Readonly is enough, it only needs to show values.

    2. I can retrieve the first 'hit' (findfirst) from a table when an ID (roomnumber) is selected, but how do I retrieve the 2nd or 3rd in a row (when there are more then 1 hits)? There's no findsecond-command

    Thx in advance for any insights

  2. #2
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Sorry, I don;t really understand what you're trying to do.

    You have a combo box (1) and once a value is selected you want combo box (2) to change depending on what (1) is?

    Or is it something else??

  3. #3
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by daveo61
    Sorry, I don;t really understand what you're trying to do.

    You have a combo box (1) and once a value is selected you want combo box (2) to change depending on what (1) is?

    Or is it something else??
    Thats about it. Picture a form with 2 comboboxes; the left combobox contains officenumbers which the user can select. If one office is selected, the second combobox (which was previously empty) should now contain the numbers of all walloutlets in that room, so the user can now select which wall outlet they want info about.

    It's such a common thing I suspect something very simple, built in in Access ;D

  4. #4
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Not sure how much you have done on combo boxes so I have a quick and dirty lash to.

    Why not create a whole bunch of combo boxes and set the .visible = false.

    On combo (1) on update set the one you want to show to .visible = true.

    1 thing to bear in mind. they change combo (1) you'll need to hide the combo (2) on show, so you might want a global variable that keeps the name so you can set the .visible = false again.

    Hope this helps, if not, we can go though how a combo works and how you can assign values dynamically!

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by avlan
    Hallo humans,

    1. Is it possible to assign values dynamically to a combobox with VBA? I want the values to change according to which roomnumber (in another combobox on the form) I select. Or is there another way to do this? Readonly is enough, it only needs to show values.

    2. I can retrieve the first 'hit' (findfirst) from a table when an ID (roomnumber) is selected, but how do I retrieve the 2nd or 3rd in a row (when there are more then 1 hits)? There's no findsecond-command

    Thx in advance for any insights
    1. Yes

    2. Nope. Not with a combobox. You need to use a listbox for multiple selections ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    1. Yes

    2. Nope. Not with a combobox. You need to use a listbox for multiple selections ...
    No but you can fake it.....

    build the combo boxes row source using selection(s) from the list box. requery the combo box and it will have the correct records

    the option call is if you want to requery every time the list box is clicked or wait for the the user to finish selections and then get them to press a command button to build the SQL.

    It probably isnt a smart idea to redefine the combo box rowsource every time the user selects (or delselects something).

    you will need to iterate through the list boxes selected collection (at least thats what I think its called) and then build your SQl as appropriate

    remember to clear the combobox in the forms on current event (so that the previous records information is deleted.

  7. #7
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by healdem
    No but you can fake it.....

    build the combo boxes row source using selection(s) from the list box. requery the combo box and it will have the correct records

    the option call is if you want to requery every time the list box is clicked or wait for the the user to finish selections and then get them to press a command button to build the SQL.

    It probably isnt a smart idea to redefine the combo box rowsource every time the user selects (or delselects something).

    you will need to iterate through the list boxes selected collection (at least thats what I think its called) and then build your SQl as appropriate

    remember to clear the combobox in the forms on current event (so that the previous records information is deleted.
    Well of the things I dont know how to do is how to change the rowsource-query (except opening properties and typing, obviously) so if you could help me with that one, it would help a lot ..

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    strsql="selct blah, blah1, blah2 from table boo"

    strwhereclause ="where 1=1"
    for each item in listbox.selectec ' not sure of the code / propoerties you need to check

    strwhereclause=strwhereclause & " AND blah=" & criteria
    loop

    strsql=strsql & strwhereclause & " order by blah;"

    mycombobox.rowsource=strsql
    mycombobox.requery

    its a bit rough, the above is air code, noit tested but it should gicve you some pointers

    the combo box must however NOT be a bound control.

  9. #9
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by healdem
    strsql="selct blah, blah1, blah2 from table boo"

    strwhereclause ="where 1=1"
    for each item in listbox.selectec ' not sure of the code / propoerties you need to check

    strwhereclause=strwhereclause & " AND blah=" & criteria
    loop

    strsql=strsql & strwhereclause & " order by blah;"

    mycombobox.rowsource=strsql
    mycombobox.requery

    its a bit rough, the above is air code, noit tested but it should gicve you some pointers

    the combo box must however NOT be a bound control.
    Great, it works idd!
    I'm curious, since one can use mycombobox.rowsource, is that more or less usable for all attributes of a certain tool (like combobox)? Like, if you wanna change the recordsource, it would be smt like mycombobox.recordsource = tblMain?
    Thx again, now I can REALLY make a dynamic GUI :P I'll prolly be back here within a few hours with the next mystery

  10. #10
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    sure you can change rowsource "on the fly". but about your actual task .. you could have accomplished it quite easy by setting the cbo's rowsource to a query which takes the other cbo's value as a parameter. so u got the same effect without a single line of coding

  11. #11
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by bachatero
    sure you can change rowsource "on the fly". but about your actual task .. you could have accomplished it quite easy by setting the cbo's rowsource to a query which takes the other cbo's value as a parameter. so u got the same effect without a single line of coding
    Comes down to the same thing, but what nobody is explaining: Can I just use a variable in a SQL-statement? How do I 'read-out' the selected value and use this in the next SQL-statement without a single line of code?

    Another thing: How do I set the startvalue of the dropdownbox after I have assigned the values through an SQL-statement. Now I have:

    Outletz.RowSource = "SELECT [Main].[Outlet] FROM [Main] WHERE [Kamernrs] = " & IDWhere
    Outletz.ListIndex = 0

    But I think I cannot use ListIndex in combination with a SQL-statement here... Or am I doing smt wrong?

  12. #12
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    what are the names of the comboboxes and of the form they're sitting in?

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bachatero
    sure you can change rowsource "on the fly". but about your actual task .. you could have accomplished it quite easy by setting the cbo's rowsource to a query which takes the other cbo's value as a parameter. so u got the same effect without a single line of coding
    but you will have to use VBA if you want to identify more than one item from the list box.

    If it was a single entity fro the list box populating the combobox you'd be right, its the requirement to show records from multiple selected items in the loist box that forces the VBA route

  14. #14
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    uk, in that case there is no way 'round without coding

  15. #15
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by bachatero
    uk, in that case there is no way 'round without coding
    Np, its working fine atm. I prefer to code as much as possible in VBA tbh, cuz otherwise I have to check every control manually when an error occurs if maybe the row source statement or smt is incorrect (had that once already )

    Atm I am not using a dropdown-box anymore but a scrollbox which I already make big enough so no scrollbars are shown. This way al 'results' are directly viewable by the users, (in this case) thats better anyways.

    The possibility to set the rowsource through code is great, opens a lot of possibilities now ... And I can keep all boxes unbound in this way... looking better

Posting Permissions

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