I would appreciate any help in creating a form that will add records to the tblSong table. (Database is zipped up and attached).

I have 4 tables and they are all joined - thanks.

GRicks

Be warned, this is nothing fancy. There's probably quite a few changes I'd make if I had more time to mess with it.

I knew that was the case. I have been reading all of the articles on normalization, and have tried to implement that thought process.

I am in the middle of designing a database here at work, and I am trying to get an idea on how to create forms to add records when there are multiple linked tables involved.

I modified the form and got it to add a record (thanks for the VBA behind the form)... but it is automatically adding a new record to the Album, Artist, and Genre.

Also - How can we get it to automatically assign the ID number - can we use a recordset call to get the max ID number - add 1 - and then use that?

Thanks.

This post has been edited by grickstx: Mar 27 2012, 05:25 PM

I cannot look at your attached db as I don't run 2007/2010, but as a rule being able to add Records to multiple Tables requires using a Main Form and one or more Subforms. When this scenario is set up correctly, Access automatically populates the Foreign Key in the Subform Tables with the Primary Key from the Main Form.

When there have to be multiple Subforms, as it sounds here, they are usually palced on the Pages of a Tabbed Control.

Linq ;0)>

OK, I changed up your tables and your relationships to make them a little more "normal".

Whether a song or an album should be marked with the genre could be open to debate. Tagging each song would give you the most flexibility. I suspect that in some databases they actually tag both separately. I chose to design it so that each album has a genre, not each song.

The queries are just examples of how getting data works when you have your data spread out across different tables. The queries aren't for entering or editing records. Queries that will be used to enter or edit data need to include the primary key and generally do not include fields from other tables.

If I get time I might make up some forms that would show you how to setup data entry/editing with this new design. Getting your tables right is very important because it is so foundational. That's the reason I only edited your tables in this version. Study this and see if you can make sense out it first. The forms need to be built on top of well designed tables.

Thanks - I did try to create a Main Form with Subforms. I am having an issue because it is not populating the foreign keys on the subform - plus it is making me enter the Primary Key on the main form.

Does anyone have a link that shows step by step the process to build a Main Form with a subform with this scenario?

Thanks.

Your problems probably come from bad table design. I'll see if I can get you another version with a form in a few minutes.

I agree with the new setup of the tables and the queries - thanks!

Do you know of any links that speak to building forms with a table setup such as this?

Thanks again.

OK, I did what I feel had to be done here. It makes a clumsy form design because you have nested subforms and you have to use the navigation buttons on the bottom of the album form to move between albums. I'm not crazy over using the built in navigation buttons and I remove them every chance I get but I find that I'm in the minority. I think most Access database programmers like those buttons.

When it comes to tutorials on building forms, I don't really have any place to point you. Take a look at what I build here and see if you can make any sense out of it. I didn't use any code at all.

Wow - exactly what I needed!

Will take a look at the forms and the setup you used to get there - like the fact that there is no coding involved to get there.

Thanks again - made my night!

Hi

Adding my 2 Bobs worth

I also advocate Hiding all navigation options

See the Modified Db supplied by HK1

 Song3.zip ( 43.34K ) Number of downloads: 9

I like the format of the forms in this one - I know that some people do not like the record navigation buttons - so this is great as well.
Thanks again!

Hi

Glad to help

yeah, nice work mike60smart.

I think continuous forms, as you used here, are probably a better choice. I will say that I generally favor datasheet views, especially when there's lots of records. My users like to switch the columns around and change the sort order all on the fly which can't be done with continuous forms. The one major downside is that datasheet views don't let you put any controls in them, namely buttons and image controls. It comes down mostly to choosing the right form for the right job but what you get used to also has a lot to do with which style you expect to see. When I started for the company I'm working for now, they were already used to using datasheet views with lots of rows and columns and they change up the sort order off and on all day long. I've given them the most powerful filtering options (and easy ones too) but they often prefer to view all records and use sorting instead to find the range of equipment they are looking for.

Quick question - what did you do to the Song Subform to allow it to add records without having to enter the ID?

Thanks again -

I think I just changed SongID to autonumber but I can't remember for sure.

Thanks - that was it - I appreciate it.