Help needed in displaying cross related "forms" for data input

[dwcrockford]

Hi All, it's been a few years since I've sought out Access help and need some guidance once again.

I am designing a choir music database... most of which is completed but I am having a problem it getting a form to display info correctly on two forms that I want to be inter-related (for lack of a better term.

The two tables involved are:

tblMusicDetails (contains all information about the songs; title, publisher, composer, etc.)
tblPerformances (contains info regarding performance title, location, date)

Both tables have a relationship to SongID for referencing

For data entry purposes, the "forms" are:
frmMusicDetails
frmPerformances

I am having difficulties in the following:

On the frmPerformances form, I want to add a set list of songs performed at that event - a combo box drop down in a datasheet view. Based on the song titles in the tblMusicDetails table.

Secondly, on the frnSongDetails form, I want to add the list of all performances that the particular song was performed in - again, in datasheet view based on the tblPerformances table.

Intent-wise, is to be able to enter information on EITHER of the forms, and the information would automatically appear in the other form in the appropriate area.

I have attempted adding a subform to the Performance form to allow such a thing, however, the combobox does not show any results. If I look at the subform directly (as it's own form), everything needed shows up (songID, SongTitle).... If I try to enter song title in the parent form, there is nothing in the dropdown (datasheet view) to choose the song, or to begin typing it and it shows up). It also gives me the error of not allowed to enter info, even though the subform is set to allow edits.

I have a "temp" form set up for performances that allows me to add songs (based on MusicDetails table) but each song is it's own combo box rather than a datasheet view (don't ask - it's the only way I could get it to work "OK") but not the results I am looking for (info enter in Performances, does not currently populate info on the MusicForm.

I know, most will need more information/details, but this is a start. I'm obviously missing something (field?) I need in one or both of the tables, but my brain cannot yet figure it out based on the current structures.

Help is appreciated.

Daryl

 

[JonXL]

Can you explain what frmSongDetails is and what it's bound to?

Reading up to that point (which is where I get lost), it sounds like you need a third table housing tblMusicDetails and tblPerformances FKs. This table is how you would link your list of songs and performances, looking something like this:

ID	MusicDetails_ID	Performances_ID
1	2	5
2	2	6
3	3	6
4	1	7

The form setup then is based on this table.

 

[Micron]

I agree with the answer and will add as an FYI to dwcrockford that this is known as a junction table. It's needed when you have 2 or more entities (represented as a table where you should not repeat the entity data) but each of the entities is part of a many to many relationship. The m2m thing is key. In your case there can be many different performances that use the same song, plus a performance consists of many songs (records). However, you should only have one record for the performance details, and one record only for the song details. Without this junction, your performance data (place, time, conductor, etc. - details of the performance only) would need to be repeated for each song. That is one clear indicator that you need a junction table. Furthermore, you only need one subform for managing the performance/song data - a main for a specific performance and a subform linked to junction table for the many songs. Ideally, you'd have separate form(s) for inputting performance data and song data.