Making a subForm Editable

[drums_4_life]

Here is a screen shot of my Form that i need help with.

I would like to be able to input the date at the top so the list comes up in the subform but then i would like to be able to add more titles to the subform. Does anyone know how I can do this? I would love the help I am really stuck here.

Thanks

 

[Lifeseeker]

what's the relationship between your main form and the subform? i am thinking it's one-to-many?

Try to unlock your mainform because if you lock your mainform, the subform is locked with the mainform.

And my advice is....base your form on a query, then you can make changes and updates through subform at any time.

 

[drums_4_life]

Hey thanks for the input but im still cant edit the form.

I type the date I want at the top and click down in the subform and this pops up

"The changes you requested to the table were not successful because the would create duplicate values in the index primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

When I click ok the subform goes to the correct date ans shows the correct set list of songs.

My subform in based off a query called Service Song Query. And I do not know what my relationships are so here is a screen shot of my relationships.

Thanks for everything!

 

[AccessMc]

I may be wrong, because I don't use the same version of Access as you, but I think your relationships may be broken.

You have Church Service Date, and Service Date related - they should be the same name and identical fields. I think that explains the problem you're getting. You must use identical fields in relationships in my experience.

Similarly, you have Song Title and Title - they also need to be identical. The only difference between related fields in my experience is one must be a primary key, the other not.

I presume that'd also explain the strange absence of 1 and many symbols on the relationships.

Try fixing the relationships by using identical fields, and see if that works.

Good luck.

 

[drums_4_life]

Hey I got the Church Service Date fields to work they now show the 1 to many symbols. But I cannot get the Song Title field from the Church Service Set List table and Song Table to work with the one to many symbols. Got any advice?

My song table i do not want duplicates but on my Church Service Set List I do want duplicates so I cannot make them the same fields.

 

[AccessMc]

So, you have a song table that has Song Title, and you need to allow multiple Song Titles in the Church Service Set List form?

First of all, a very important thing to consider is an ID field as a primary key for the song. Songs can very easily have the same names - and whether that's relevant to you or not it's extremely good practice. The date field in service set list should really be an id too, but if you're including the year it shouldn't be a problem.

What I believe you will need to do is build 2 linking tables between the Song List and service set list. You will then need to develop a Form with a Sub-form that will allow you to build up each service.

I know that sounds complicated, and I'm not by any means an expert myself but I'll try to explain it:

TBL_Songs
All song info goes in here. For the purpose of explaining this, I will use the following fields:
-Song ID
-Song Title
-Song Author

Of course you will have more, that's just to explain.

TBL_ServiceInfo
All the information for a service goes in here. Again, I'll use only a few fields in this explanation:
-Service ID
-Service Date

Do not put any relationships in these tables yet (no fields are the same)

TBL_ServiceSongs
This is one of two linked tables for TBL_Songs and TBL_ServiceInfo. This table will contain information on each song selected for any service. This will be filled in using the sub-form. Required fields will be:
-ServiceSongsID
-Song ID <--This must be spelt the same as the primary key in TBL_Songs
-SongLists ID <--This must be spelt the same as the primary key in TBL_SongLists (below)

TBL_SongLists
This is the second of the linked tables for TBL_Songs and TBL_ServiceInfo. This will be used to bring all the songs from one service together to create a working 1 to many relationship. Required fields will be:
-SongLists ID
-Service ID <--This must be spelt the same as the primary key in TBL_ServiceInfo

Relationships
I don't know how relationships work in Access above 2003, but if you have options to enforce referential integrity, and cascade changes, make sure you choose them in all cases. I suggest if you have problems you look up relationships in your version.

Make the following relationships:
-Song ID in TBL_Songs, to Song ID in TBL_ServiceSongs
-SongLists ID in TBL_SongLists, to SongLists ID in TBL_ServiceSongs
-Service ID in TBL_ServiceInfo, to Service ID in TBL_SongLists

Forms with sub-form
You will now have a situation where you can set-up a usable form with sub-form to enter data into the tables.

Using the wizard (again, I only have knowledge of 2003, sorry):
-Send across all fields from TBL_ServiceSongs
-Send across all fields from TBL_SongLists EXCEPT Songlists ID
-Send across all fields from TBL_Songs EXCEPT Song ID
-Send across all fields from TBL_ServiceInfo EXCEPT Service ID

Select the option which puts all the Service fields at the top of the form, and all the Song fields in the sub-form. From here you will be able to choose multiple songs for each service successfully.

I hope that helped a little bit with the relationships side between the two tables, and sorry if I gave any information you already knew or appeared patronising - I'm not sure of your experience, sorry.

 

[drums_4_life]

Hey thank you very much. I really appreciate your help. You have no idea. I will let you know what I come up with once I do what you explained. Thanks again.

 

[AccessMc]

No problems, best of luck with it. I hope it works out well, but I don't know anything about access 2007. If Access 2007 has Combo boxes in Forms, I'd consider one of them too for the song selection.

And if there are any fields in your current system you'd like help with where to put them, feel free to ask and I'll try to explain.

 

[drums_4_life]

Hey so far everything you have told me worked great i didn't start over with the database but I did get my Set list sub form to become editable. Now that i achieved that task I want to have a musicians subform that can be editable so I can choose what musicians are going to be playing on a specific Service date. I was able to get it to work once but then I closed my database and opened it back up and the new sub form would not let me add musicians to that service date. When I click on a name to add it to the subform a window pops up which says "field cannot be updated" I cannot find anything different between the sub form that works and the sub from that does not work so im lost haha. Do you have any ideas?

Grant

 

[AccessMc]

I'm glad you got it to work, but as for the musicians side I'm not too sure. Can you send me a screenshot of the relevant relationships?

 

[drums_4_life]

here is the screen shot. I am confused because the relationship is identical to the set list part and the set list part works just fine.

 

[AccessMc]

Only thing I can think of is that you need to check that both church service date fields are identical and both contact names are.

Some generic advice too - avoid using these fields as primary keys. It would be much safer to have ID fields with autonumbers, and when you join on them the foreign key is a number. Much easier, and much safer.

 

[boblarson]

1. You should not be storing the Tempo, and Key in the Church Service Set List. All you need is the SongID (should be song ID and not song title, if you want to get it right).

2. Then you should only have one relationship line between Church Service Set List and Song.

 

[drums_4_life]

well I got everything to work. As soon as I took out instrument in the query for musicians it would let me update the query. Im not sure why that is but i can do without the instrument field.

Thanks all.

 

[boblarson]

It is those confounded multiselect fields that cause problems in queries. I would not use them myself.