subform - looking up data

[Shaun Allan]

I have a customer table and a music table. On a form,
based on the customer, I have a subform where I pick an
artist, then pick their album title. What I need to do on
the subform is, when the artist is chosen, only show the
album titles from that particular artist - for example,
Westlife have numerous albums, so their name appears more
than once - using DISTINCT on the Artist field, I get it
to only show their name once. WHat I then want is to go
to the Title field, and when I click on it (a combo box) I
only want it to show the titles by Westlife, instead of
every album title in the database. The subform is shown
as a datasheet, but I don't mind if it needs to be a
form. I've done this on a main form, but can't seem to
get it to work as a subform.

 

[Trygve Lorentzen]

Hi Allan,

hope I have understood your problem correctly

In the combobox (named combobox_Titles here) Row Source put something like:

select title from titles where artistid = [Forms]![Music]![artistid] (your
need to have the right "path" to the subform here, maybe one of these will
work:
[Forms]![Music]![artistid]
Forms.Item("Customer").Music.Form.artistid
Forms.Item("Customer").Music.artistid
[Forms]![Customer]![Music]![artistid]

then in the combobox On Got Focus event put:

Me.combobox_Titles.Requery

 

[Jeff Boyce]

Already answered in another group you posted to.

If you find it necessary to post to more than one group (rarely needed),
include the groups in your To:/Newsgroups: field rather than posting
individually to each. That way, a reply in one shows in the other.

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks! Didn't know that!