Trouble Creating Form

[adrian007uk]

I am having real trouble working out how to create a form to link three tables.

I have:

Artists_Table
Artists ID (PK) (1)
Artists Name

Performance Table
Track ID (PK) (many)
Artist ID (PK) (many)

Tracks_Table
Track ID (PK) (1)
Track Number
Track Title
Recording ID

I have two other tables but my problem centres around these tables (i think
because I have a joining table to split the many - many relationships).

Could somebody please talk me through how i can get the artists table to
interact with the tracks table? This is not happeneing at the moment. I
keep trying to change the 'control source' for the primary keys from the
diffent tables (i.e., Track ID) but i seem to be missing something very
obvious.

I am also trying to make the Artists Name a combo box but i am confused
about whether it should look up a value, or the second option, and whether it
should rememeber the value or store the value in a field.

Thanks

Adrian

 

[Richard]

Adrian,

Microsoft has a few ready made templates of this type. Maybe download one
and de-construct it to what they have done. Here is a DVD collection template
you could look at.
http://office.microsoft.com/en-gb/templates/TC010186441033.aspx

Richard

 

[Maha Arupputhan Pappan]

I suggest we tackle one issue at a time.

First of all you must identical fields name in both table. For example, I
created 2 tables as below:

tblArtist (TABLE)
ArtistID (PK) - text field type
ArtistName - text field type

tblTracks (TABLE)
TrackID (PK) - text field type
TrackNo - text field type
TrackTitle - text field type
RecordingID - text field type
ArtistID - text field type (FIELD SIZE MUST BE THE SAME IN tblArtist)

Then I created one-to-many relationship (tblArtist-to-tblTracks) using the
ArtistID field.

1. Create a form via Form Wizard.
2. Select tblArtist from Tables/Queries combobox.
3. Move both the available fields to Selected fields.
4. Select tblTracks from Tables/Queries combobox in the same Form Wizard.
5. Move all available fields except ArtistID.
6. Click Next.
7. Click Next again.
8. Click Next again.
9. Click Next again.
10. Replace the Form name to frmArtist.
11. Replace the Subform name to frmTracksSubform.
12. Click Finish.

You'll have your forms. Please confirm if you managed to get this form
correct. Then we'll go the next stage.

 

[adrian007uk]

Hi Maha

I will attempt to make the changes you have suggested. I am confused as the
only reason i created the "performance table" was to remove the many to many
problem from artists to tarcks. However, you are now telling me to forget
this issue.

Adrian

 

[John Spencer]

Good Structure. It is well designed. The only change I would make would be
to remove any spaces in the names. It is best if field names and table names
consist of only letters, numbers, and the underscore character. However, if
you want to use spaces then you are going to have to use brackets around the
names when you refer to the fields or tables.

I'm a bit confused on what you mean by interact with the tracks table. Do you
mean for data entry purposes - use forms and subforms or for reporting purposes.

You would have a main form based on the Tracks table. This form would need to
be in Single Form view. Then you need a second form based on the Performance
table. This second form will be your subform on the tracks table and it will
be a continuous form. On this form you can have a combobox whose control
source is bound to [Performance Table].[Artist ID]. Its row source will be
the Artists_Table (or a query based on that table). If the wizard is building
the combobox then it will lookup the values from the table.

Now add the second form as a subform to the Tracks form. When asked link the
forms on the track id fields in the two tables.

You should now be able to enter multiple artists for any track.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[adrian007uk]

"I'm a bit confused on what you mean by interact with the tracks table. Do
you

mean for data entry purposes - use forms and subforms or for reporting purposes".

I meant for data entry. I have another two tables (not listed in my orginal
post: a Records table containing data on title, purchase price, notes etc and
a music categories table). I do not have a problem linking the Records_Table
to the Tracks_Table in a form. The wizard makes the Records_Table as the
master form with the Tracks_Table as the sub-form (which is good). What i am
trying to do is get the [artistID] as a combo box onto the records form. The
problem is with the three tables i have detailed (I cannot get the primary
key links to activate if that makes sense whern i start using the records
form. It has someting to do with the trackID i think).

Adrian

Good Structure. It is well designed. The only change I would make would be
to remove any spaces in the names. It is best if field names and table names
consist of only letters, numbers, and the underscore character. However, if
you want to use spaces then you are going to have to use brackets around the
names when you refer to the fields or tables.

I'm a bit confused on what you mean by interact with the tracks table. Do you
mean for data entry purposes - use forms and subforms or for reporting purposes.

You would have a main form based on the Tracks table. This form would need to
be in Single Form view. Then you need a second form based on the Performance
table. This second form will be your subform on the tracks table and it will
be a continuous form. On this form you can have a combobox whose control
source is bound to [Performance Table].[Artist ID]. Its row source will be
the Artists_Table (or a query based on that table). If the wizard is building
the combobox then it will lookup the values from the table.

Now add the second form as a subform to the Tracks form. When asked link the
forms on the track id fields in the two tables.

You should now be able to enter multiple artists for any track.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am having real trouble working out how to create a form to link three tables.

I have:

Artists_Table
Artists ID (PK) (1)
Artists Name

Performance Table
Track ID (PK) (many)
Artist ID (PK) (many)

Tracks_Table
Track ID (PK) (1)
Track Number
Track Title
Recording ID

I have two other tables but my problem centres around these tables (i think
because I have a joining table to split the many - many relationships).

Could somebody please talk me through how i can get the artists table to
interact with the tracks table? This is not happeneing at the moment. I
keep trying to change the 'control source' for the primary keys from the
diffent tables (i.e., Track ID) but i seem to be missing something very
obvious.

I am also trying to make the Artists Name a combo box but i am confused
about whether it should look up a value, or the second option, and whether it
should rememeber the value or store the value in a field.

Thanks

Adrian