Subform Record Creation and Linking/Relationships

[sh3p]

Let me start off by saying this is my first post here and I was unsure which category to post this under because it involves tables and forms. Please let me know if I need to bump it to a different thread!



Here is what I have built:

tblAlbums (Primary key field is albumID)
tblSongs (Primary key field is songID)

tblAlbums has a lookup field called songIDs, which allows multiple values so that one album can have multiple songs.

I have tried to create a form that allows you enter the album information (tblAlbums) and the song information (tblSongs) at one time and save the whole thing.

I created a main/master form, frmAlbum, and placed controls that allow the user to input album information. I then created a subform control for songs, so I can enter all the songs at the same time. I set the master property to AlbumID and the child property to songID. I have also tried setting the master property to songIDs (the lookup field in the album table).

When I open the form to use it, I can successfully create a new album, and as many songs as I want (the subform for song entry looks like a data sheet - not my preference, but I thought I could worry about handling an unknown number of record entries for songs later).

The problem I need help with is when I create the songs, I need them to link to the master record in tblAlbums and I am not sure how to accomplish this. Any pointers would be helpful, and if you need further information, please let me know! Thanks in advance for your assistance

[orange]

You may want to read this and rethink your set up.

Always start a database project with a plan.
See and work through this tutorial to get a grasp of database concepts -entities, attributes, relationships, tables, fields, normalization.
Good luck.

[sh3p]

I will check that out. I have no issues querying a lookup field and I am fully aware that in the background it stores the ID while displaying the value from whichever field you wanted to see. I was looking for some pointed advice - for example, if you think that the lookup field is causing the problem, can you explain to me why? Do you have a suggestion for how I should do it differently?

Let's say for the sake of argument, that I redo things without the lookup fields and just establish relationships directly. I will still have the problem of needing to create the records and link them to the master (the "one" side of the relationship) without somehow creating that link. That is what I would like some help with.

[orange]

Glad you understand the Lookup field within a Table.
Do you have only the 2 tables you mentioned: tblAlbums, tblSongs?

Here is a free model that goes beyond your description. The model includes Sales/Purchases of Albums or Singles and has info about Artists.
In addition, the modeller (I just found it online) has used a supertype/subtype set up where Item can be an Album or SongTitle(Single). You may only be interested in the righthand side of the model.

In your set up can a song appear on multiple Albums?
I can help with setting up relationships and would strongly advise not putting Lookups within a Table field. Lookups are fine as table Lookups on forms.

[ssanfu]

I agree with Orange 100% about look up FIELDS. I NEVER use look up FIELDS. (I also NEVER use multi-value fields.)
In design view, change the Display Control on the Lookup Tab to Text Box.

I have tried to create a form that allows you enter the album information (tblAlbums) and the song information (tblSongs) at one time and save the whole thing.

This didn't work because there MUST be a record in the tblAlbums to be able to save records in tblSongs. Because you are entering the Album name at the SAME time as the songs, the Album name record has not been created yet. No record in tblAlbums, records cannot be saved in tblSongs.

I created a main/master form, frmAlbum, and placed controls that allow the user to input album information. I then created a subform control for songs, so I can enter all the songs at the same time. I set the master property to AlbumID and the child property to songID. I have also tried setting the master property to songIDs (the lookup field in the album table).

This sounds correct. You must have a foreign key field in tblSongs (something like albumID_FK) linked to tblAlbums.albumID.
IIRC, moving the focus to the sub form saves the main form record automatically.
(BTW, you don't say but I use an Autonumber field as my PK field.)

The problem I need help with is when I create the songs, I need them to link to the master record in tblAlbums and I am not sure how to accomplish this. Any pointers would be helpful

This is how I set up my forms:
The record source for forms (main form/ sub form) is a query (Not a table!).
The main form details section has the fields from the main form query.
The sub form (record source is the subform query) is in the main form footer. To have the main form current record PK field value entered automatically in the sub form FK field, in design view, open the property sheet for the sub form.
Click on the DATA tab is not already selected.
Click in the "Link Master Fields" box. Click the ellipsis on the right.
Select the PK field from the Master (main) fields drop down box, then select the FK field from the "Child Fields" drop down box.

The main form and the sub form are now linked. Selecting a main form record will now cause the sub form to display related records.

The sub form can be in datasheet view or one of the form views. I very rarely use datasheet view - I "create my own datasheet" by setting the form to "Continuous Forms" and placing the controls very close together. I feel I have better (more) control than a datasheet provides.
Or I use "Single form view". Depends on what I am trying to do - data entry or displaying data.

[sh3p]

I agree with Orange 100% about look up FIELDS. I NEVER use look up FIELDS. (I also NEVER use multi-value fields.)
In design view, change the Display Control on the Lookup Tab to Text Box.

Thank you for the tip, I like that.

This didn't work because there MUST be a record in the tblAlbums to be able to save records in tblSongs. Because you are entering the Album name at the SAME time as the songs, the Album name record has not been created yet. No record in tblAlbums, records cannot be saved in tblSongs.

That is what I was missing. This is one of those times where someone shows you a different (albeit obvious) perspective...and it all seems to click.

This sounds correct. You must have a foreign key field in tblSongs (something like albumID_FK) linked to tblAlbums.albumID.
IIRC, moving the focus to the sub form saves the main form record automatically.
(BTW, you don't say but I use an Autonumber field as my PK field.)

I always use an auto-number PK as well.

This is how I set up my forms:
The record source for forms (main form/ sub form) is a query (Not a table!).

For me sometimes it is and sometimes it isn't. Usually if it isn't, that's because I don't want to take the time to make one as I am just testing a theory. Most times for deployable forms and reports, etc., I use a query because I use Boolean values to determine whether or not an item is archived/historical and at a minimum I query "active" items only.

The main form details section has the fields from the main form query.
The sub form (record source is the subform query) is in the main form footer. To have the main form current record PK field value entered automatically in the sub form FK field, in design view, open the property sheet for the sub form.
Click on the DATA tab is not already selected.
Click in the "Link Master Fields" box. Click the ellipsis on the right.
Select the PK field from the Master (main) fields drop down box, then select the FK field from the "Child Fields" drop down box.

The main form and the sub form are now linked. Selecting a main form record will now cause the sub form to display related records.

I'm not tracking why you put the subform in the footer? Is this just a design preference or is there some additional benefit to it?

The sub form can be in datasheet view or one of the form views. I very rarely use datasheet view - I "create my own datasheet" by setting the form to "Continuous Forms" and placing the controls very close together. I feel I have better (more) control than a datasheet provides.
Or I use "Single form view". Depends on what I am trying to do - data entry or displaying data.

I only had the datasheet view on this one because I was trying to get the parent/child relationship working, then I would redesign the form -- I knew I was treading new ground for myself, and maybe this wasn't the best idea, but that's what I did. I have successfully deployed continuous forms on a number of occasions, but never where each item in the continuous form was linked to a singular master/parent record. Thank you for your assistance!

[ssanfu]

I'm not tracking why you put the subform in the footer? Is this just a design preference or is there some additional benefit to it?

Mostly design preference. I want to see all of the main form records that I can. If the subform is in the detail section, I usually get to see only one or two main form records because the subform takes a lot of space. Sometimes Access complains about this arraignment, but I cancel the message and continue.

I use subforms when I have to add/edit linked records. I will use list boxes when I want to see data; I can select a line in the list box and open a form using the double click event of the list box.

I only had the datasheet view on this one because I was trying to get the parent/child relationship working, then I would redesign the form -- I knew I was treading new ground for myself, and maybe this wasn't the best idea, but that's what I did.

What ever it takes.
I've done the same type of thing trying to get the display look and feel that I want.