Combo-box in Sub-form

[Chris]

Could you please help me with a problem I have with Access 2003? I am not
very experienced with Access; I have done a college course at Level 2 (CLAIT
Plus), so I know the basics but this has got me stumped.

My database is for my CD collection. I use a Main Form for data entry which
contains details of Artist, Album Title, Number, Music Category etc. In the
Artist and Misic Category fields I use a Combo-box which holds all previously
entered names in a drop-down list. If a new name is required I double-click
on the box and enter the name in the linked form.
Within the Main Form there is a Sub-form which lists the Track Number, Track
Title and Track Length. So far, so good.

This is not adequate for "compilation" CDs with a different Artist on each
track, so I introduced a new field in the Sub-form called Track Artist and
put in a Combo-box to perform the same function as the Artist box in the Main
Form. But it doesn't work! I have tried fiddling around with the
Relationships but nothing seems to work.

Can you help, please? I hope I have explained it sufficiently to show you
what I am trying to do.

 

[John W. Vinson]

Could you please help me with a problem I have with Access 2003? I am not
very experienced with Access; I have done a college course at Level 2 (CLAIT
Plus), so I know the basics but this has got me stumped.

My database is for my CD collection. I use a Main Form for data entry which
contains details of Artist, Album Title, Number, Music Category etc. In the
Artist and Misic Category fields I use a Combo-box which holds all previously
entered names in a drop-down list. If a new name is required I double-click
on the box and enter the name in the linked form.
Within the Main Form there is a Sub-form which lists the Track Number, Track
Title and Track Length. So far, so good.

This is not adequate for "compilation" CDs with a different Artist on each
track, so I introduced a new field in the Sub-form called Track Artist and
put in a Combo-box to perform the same function as the Artist box in the Main
Form. But it doesn't work! I have tried fiddling around with the
Relationships but nothing seems to work.

Can you help, please? I hope I have explained it sufficiently to show you
what I am trying to do.

As Douglas and Daryl say, there's really not enough to go on here!

One possible problem though: it sounds like you're making a very common
mistake, designing your FORMS first. Data is not stored in Forms! Just adding
a control on the Subform called Track Artist will not work: the form doesn't
store the artist, that information is stored in the Table.

Does your Tracks table have a field for the Track Artist?

Is one Track Artist sufficient, though? What about ensembles? If you've got a
track performed by a trio or quartet of artists, will you not want to record
each of them? I suspect you may want a TrackArtists *TABLE*, with fields for
AlbumID, TrackNumber, ArtistID (and perhaps other fields indicating just what
this artist is contributing to this track).

Bottom line: get your table structure right *FIRST* - and only then work on
the forms!

 

[Chris]

Len -

Did you add the artist field to the table behind the subform? And is the
new combo-box bound to this new field?

If so, please tell us what is not working...

Thanks for your interest.
I have a Track Artists table with field headings: Track Artist ID, Track
Artist, Recording ID & Track ID. There are 3 names entered.
When I click on a Track Artist field in the Sub-forn, a down arrow appears.
If I click on the arrow a drop-down list appears but it is empty. When I
double click on the field, nothing happans.
As far as I can see, I have set this combo box up the same as the ones for
Music Category and Artist but I just can't enter any names.
Hope this makes things a bit clearer.

Chris

 

[Chris]

"doesn't work" doesn't give us much to go by...

What problem are you experiencing?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)





.
Thanks for your interest.

I have a Track Artists table with field headings: Track Artist ID, Track
Artist, Recording ID & Track ID. There are 3 names entered.
When I click on a Track Artist field in the Sub-forn, a down arrow appears.
If I click on the arrow a drop-down list appears but it is empty. When I
double click on the field, nothing happans.
As far as I can see, I have set this combo box up the same as the ones for
Music Category and Artist but I just can't enter any names.
Hope this makes things a bit clearer.

 

[Chris]

As Douglas and Daryl say, there's really not enough to go on here!

One possible problem though: it sounds like you're making a very common
mistake, designing your FORMS first. Data is not stored in Forms! Just adding
a control on the Subform called Track Artist will not work: the form doesn't
store the artist, that information is stored in the Table.

Does your Tracks table have a field for the Track Artist?

Is one Track Artist sufficient, though? What about ensembles? If you've got a
track performed by a trio or quartet of artists, will you not want to record
each of them? I suspect you may want a TrackArtists *TABLE*, with fields for
AlbumID, TrackNumber, ArtistID (and perhaps other fields indicating just what
this artist is contributing to this track).

Bottom line: get your table structure right *FIRST* - and only then work on
the forms!
--

John W. Vinson [MVP]
.
Thanks for your interest.

I have a Track Artists table with field headings: Track Artist ID, Track
Artist, Recording ID & Track ID. There are 3 names entered.
When I click on a Track Artist field in the Sub-forn, a down arrow appears.
If I click on the arrow a drop-down list appears but it is empty. When I
double click on the field, nothing happans.
As far as I can see, I have set this combo box up the same as the ones for
Music Category and Artist but I just can't enter any names.
My Tracks table does have a field for Track Artist.
One Track Artist per track is sufficient for my needs.
Hope this makes things a bit clearer.

 

[John W. Vinson]

I have a Track Artists table with field headings: Track Artist ID, Track
Artist, Recording ID & Track ID. There are 3 names entered.
When I click on a Track Artist field in the Sub-forn, a down arrow appears.
If I click on the arrow a drop-down list appears but it is empty. When I
double click on the field, nothing happans.
As far as I can see, I have set this combo box up the same as the ones for
Music Category and Artist but I just can't enter any names.
My Tracks table does have a field for Track Artist.
One Track Artist per track is sufficient for my needs.
Hope this makes things a bit clearer.

The Track Artist table is not needed then. Just put a TrackArtistID field in
the Tracks table (so you can enter just one ID for that track).

You will not be able to type new names into a combo box with your current
structure; what you'll need is some code in the combo box's NotInList event to
pop up an Artists form when a new entry is needed.

 

[Chris]

The Track Artist table is not needed then. Just put a TrackArtistID field in
the Tracks table (so you can enter just one ID for that track).

You will not be able to type new names into a combo box with your current
structure; what you'll need is some code in the combo box's NotInList event to
pop up an Artists form when a new entry is needed.
--

John W. Vinson [MVP]
.
John: sorry for the delay in replying. I do appreciate your help.

Surely I do need a Track Artist Table to store all the names, so that I
don't have to ebter the same name every time it occurs? Music Category and
Artist both have tables and forms behind their combo boxes.
Can you suggest what code I would need?