A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trouble Creating Form



 
 
Thread Tools Display Modes
  #1  
Old August 6th, 2009, 10:38 PM posted to microsoft.public.access.gettingstarted
adrian007uk
external usenet poster
 
Posts: 67
Default Trouble Creating Form

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
  #2  
Old August 6th, 2009, 11:17 PM posted to microsoft.public.access.gettingstarted
Richard
external usenet poster
 
Posts: 1,419
Default Trouble Creating Form

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/te...186441033.aspx

Richard


"adrian007uk" wrote:

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

  #3  
Old August 7th, 2009, 04:16 AM posted to microsoft.public.access.gettingstarted
Maha Arupputhan Pappan
external usenet poster
 
Posts: 15
Default Trouble Creating Form

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.

--
Maha Aruppthan Pappan
Nacap Asia Pacific


"adrian007uk" wrote:

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

  #4  
Old August 7th, 2009, 01:03 PM posted to microsoft.public.access.gettingstarted
adrian007uk
external usenet poster
 
Posts: 67
Default Trouble Creating Form

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

"Maha Arupputhan Pappan" wrote:

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.

--
Maha Aruppthan Pappan
Nacap Asia Pacific


"adrian007uk" wrote:

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

  #5  
Old August 7th, 2009, 01:55 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Trouble Creating Form

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 wrote:
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

  #6  
Old August 7th, 2009, 02:46 PM posted to microsoft.public.access.gettingstarted
adrian007uk
external usenet poster
 
Posts: 67
Default Trouble Creating Form

"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


"John Spencer" wrote:

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 wrote:
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:59 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.