Subform - lookup 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.

 

[Jeff Boyce]

Shaun

Not sure how this is a .tablesdbdesign question -- seems more like
..forms-oriented.

Add an unbound combo box. Make its row source a list of artists. If your
database design doesn't include an artists table (one artist, many albums),
you can use the DISTINCT to show each one once, as you indicated. This is
less than optimal design -- consider looking into more fully normalizing
your data.

In the combo box's AfterUpdate event, requery the subform. The 'source' for
your subform needs to be a query, with a criterion on the Artist set to look
at the combo box. Your criterion will look something like (actual syntax
may vary):

Nz([Forms]![YourMainFormName]![YourComboBoxControlName],0)

Note1: this assumes that your combo box is located on your main form.
Note2: this gives a 'default' value of 0 when the form loads

Good luck

Jeff Boyce
<Access MVP>