Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: please help with combo box code

    Hello,

    I have put a combo box on my access form and populated it with names from my table. When I click on a name from the list i want it to show all titles linked to that name in a list box.

    I have clicked on event procedure but do not know what code to put in.

    I have created a query of all artists and titles.

    So, if you click on rod stewart then all his titles will go into a list box.

    many thanks

    neil

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: please help with combo box code

    Put the code in the click event of the combo box.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Oct 2003
    Posts
    58

    Re: please help with combo box code

    Originally posted by SCIROCCO
    Put the code in the click event of the combo box.
    I guess I did not make my question clear. I was basically asking if somebody could give me the code. I have not got a clue what to write.

    Thanks

    neil

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    I don't know the names of your tables, fields, form, and control but you can try this...

    In the On Click event for your ComboBox:
    Code:
    If Not IsNull(Me.myComboBoxName) then
       Me.myListBoxName.RowSource = "SELECT [myMusicTableName].mySongTitlesFieldNameInTable " & _
       "FROM [myMusicTableName] WHERE (([myMusicTableName].[myMusicTableArtistName]=" & _
       "[Forms]![myFormName]![myComboBoxName]));"
    End If
    or something like that..

  5. #5
    Join Date
    Oct 2003
    Posts
    58
    Thanks for your help. I have wrutten your code into the click on event of my combo box. The result is that the list box displays the code that I wrote rather than the data.

    he is what I have written:

    If Not IsNull(Me.Combo36) Then

    Me.List40.RowSource = "SELECT [q-artist].title " & " from [q-artist]where (([q-artist].[artist]=" & "[forms]![frm-main]![combo36]));"

    End If
    ----------------------------------------------------------
    Combo box = combo36
    list box = list40
    music table name is a query called q-artist
    song title field = title
    music title artist=artist

    Thanks for your help

  6. #6
    Join Date
    Mar 2004
    Posts
    52
    Errors, a lot... Try this:

    me.list40.rowsourcetype="Table/SQL"

    Me.List40.RowSource = "SELECT title from [q-artist] where artist='" & me.combo36.column(0) & "'"

    me.list40.requery
    Last edited by ptrapp; 04-11-04 at 08:56.

  7. #7
    Join Date
    Oct 2003
    Posts
    58
    It still does not work. I guess I must be doing something else wrong.


    Thanks anyway.

  8. #8
    Join Date
    Mar 2004
    Posts
    52
    Try to debug it. Click before the rowsource line so that a red dot appears. When the code halts there, you know that it gets executed.
    Move your mouse over the combo36.column(0). In a yellow frame should appear the content of the combo. You can press F8 to cycle through the code.

    If that works, create a new query:

    SELECT title from [q-artist] where artist='<name here>'

    Replace <name here> with the name you have in the combo

  9. #9
    Join Date
    Oct 2003
    Posts
    58
    Hello,

    i am new to Access and so i find the debugging solution to be very perplexing. I selected the debug option but it did not appear to do anything for me.

    Look, I would really appreciate it if you could amend the code for me. I have attached a test DB, which has been scanned for viruses. This way I can learn how to do this in future.

    Thanks in advance.

    Neil
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2004
    Posts
    52
    OK, I adjusted some things.

    I took of the control source of your combobox, made it empty.
    Did the same thing for the listbox, but here, I also removed the rowsource (I'm talking about the properties).

    I adapted the query q-artists, because only alice cooper was coming out :-)

    VBA:
    I took column(1) instead of 0, because in column 0 was an invisible id number that access added (in a wizard)

    I changed Table/SQL to table/query. Sorry, that was my mistake.

    I was working from a dutch Access XP. It can be that in my code there is 'tabel/query' instead of 'table/query'. Change it if it complains.

    Kind regards
    Attached Files Attached Files

  11. #11
    Join Date
    Oct 2003
    Posts
    58
    thank you very much for your help. It now works. While I was waiting I was messing with your previous code and also got it to work with the following:

    Me.List40.RowSource = "select title from q-artist " & " where artist = " & "'" & Combo36.Text & "'"

    I have ordered a book on VBA from amazon as i am confused with all the brackets, quotes and apostophes.

    On reflection, is there a way to achieve what I wanted without writing code. Would I need to research sub forms maybe?

    Again, thank you very much for your time. i hope one day to answer problems in this forum.

    Kind regards,

    Neil

  12. #12
    Join Date
    Mar 2004
    Posts
    52
    Without code and with subforms? Should think about that. I'm rather a code freak, puts you more in control.

    Either way, subforms are a strong feature that often come in handy, so do learn them.

    BTW
    & means join two things together, so drop them where not needed.

    "select title from q-artist where artist ='" & Combo36.Text & "'"

    Here, you only should use them to glue the variable (combo36.text) to the string

    Text, like in 'alison cooper' should always be in single quotes, in SQL strings.

    Text, in visual basic falls between double quotes

    Regards

  13. #13
    Join Date
    Oct 2003
    Posts
    58

    Red face

    Hello,

    Just one more thing then I will go and study my book for a week. I am just impatient and eager to learn.

    when I choose alice cooper from the combo and all his titles appear in the list box. How do I get them into a report.

    I am thinking that I could add the contents of the list box into a new table called report. then I could produce a report on that table. I could then clear this table each time the form is loaded.

    my thinking is probably long winded.

    so I guess i need to know the commands to add to a new table. how do you reference a new table, do i need to create a new recordset?

    i promise this is my last post as i do not want to take advantage of your good nature.

    Thanks again for your support and patience

    Kind regards,

    Neil

  14. #14
    Join Date
    Mar 2004
    Posts
    52
    Don't put the items you want on the report in a table. That would lead to redundancy.

    Rather, create a query that selects everything you want on the report. BUT, do not include a "where artist=artist name'. That, you will specify when you open the report.

    In the 'on open' event of the report,

    put:
    me.recordsource="SELECT * FROM newquery WHERE artist=" & artistname

    artistname is a string variable. You assign a value to it (eg "alice cooper") in the form that is open before the report opens.

    Voila, that shoud put you on the road. Happy reading :-)

  15. #15
    Join Date
    Nov 2003
    Posts
    1,487
    NeoIce,

    Your posts are always welcome...ask as often as you like. It's nice to here from you.


Posting Permissions

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