none
Access Forms, Combo Boxes RRS feed

  • Question

  • I have an ACCESS 2007 form which has two combo boxes on it, both selecting information from the same table - Items. The first is 'Genres' [in a combobox called GenreBox] and uses a seperate query 'Genres' as its row source (the query uses the SQL "SELECT DISTINCT Items.[Genre] FROM Items;"). For the second combo box, I want to list the sub-genres associated with the selected genre but I can't find a method of doing it. My latest try is to put an SQL sring into the Row SOurce property of the combo box - the SQL I tried was "SELECT DISTINCT GenresAndSubGenres.[SubGenre] FROM GenresAndSubGenres WHERE GenresAndSubGenres.Genre=+me.GenreBox.value; ". That doesn't work!

    Anyone got any ideas?

    Sunday, June 12, 2016 9:29 AM

Answers

  • Hi SteveLong,

    I try to reproduce your issue on my side I create form with 2 combobox.

    I create 2 tables Genere and subgenere. Enter the value in that and try to assign value of subgenere on the base of genere value in combobox1.

    Following is the subgenere table.

    Following is the form.

    you can see that if Genere combo select with "Fruit" then subGenere displays the value of Fruits.

    I use the Following Row Source.

    SELECT subgenere FROM subgenere WHERE genere=[Forms]![Form8]![Combo0]; 
     

    you can see that it is working correctly.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, June 18, 2016 9:09 AM
    Moderator

All replies

  • Try

    "SELECT DISTINCT GenresAndSubGenres.[SubGenre] 
    FROM GenresAndSubGenres 
    WHERE GenresAndSubGenres.Genre= Forms("YourFormName")!GenreBox" 
    Matthias Kläy, Kläy Computing AG

    Sunday, June 12, 2016 11:13 AM
  • Thanks Matthias, that would possibly work in VBA, but putting it into the Row Source property of the combo box yields an error "Invalid use of ., !, 0 in query expression".

    It might be that I have to write it into VBA

    Sunday, June 12, 2016 3:33 PM
  • Hi SteveLong,

    you can use this query in Access too with referring control with !. when we use "!" it does not mean the code written in VBA.

    first I would recommend you to run this query separately in Access and check whether it worked or not if not try to make it correct there.

    once it run successfully then try to place in Row Source property.

    Regards

    Deepak  


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 13, 2016 4:20 AM
    Moderator
  • Hi SteveLong,

    did your issue solved or you are still having an issue with it?

    if your issue is solved then would you like to share the solution with our community so that any other member have same issue like you can also get solution from your post.

    If your issue is still exist and did not solved then please let us know so that we can provide you further help.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, June 18, 2016 8:45 AM
    Moderator
  • Hi SteveLong,

    I try to reproduce your issue on my side I create form with 2 combobox.

    I create 2 tables Genere and subgenere. Enter the value in that and try to assign value of subgenere on the base of genere value in combobox1.

    Following is the subgenere table.

    Following is the form.

    you can see that if Genere combo select with "Fruit" then subGenere displays the value of Fruits.

    I use the Following Row Source.

    SELECT subgenere FROM subgenere WHERE genere=[Forms]![Form8]![Combo0]; 
     

    you can see that it is working correctly.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Saturday, June 18, 2016 9:09 AM
    Moderator