Notinlist to create default on form for combo box (1 Viewer)

All,

As I said in the intro forum, many thanks for such a helpful and informative website. It has helped me immensely in building my database.

Now to the question. I am building myself a music database. As part of this it uses many to many tables (and junctions). For example, we have

Songs (all unique)
Composers (all unique)
Composers are on more than one Song and Songs can have multiple Composers

There is more info on Albums, Artists, Genres, Formats and so on. Tracks are made up of Songs by different Artists.

For data entry I have a Form with Subforms that essentially has Album details and underneath a Tracks subform. When entering Tracks I use a combo box to enter Songs. A Notinlist fires to allow a new Song entry (using the Allen Browne code).

This is all fine and seems to work well. However, I then need to open a separate form ("ComposedBy") to enter Composers for each new Song (as a given unique Song can have multiple Composers).

Is there any way to use Notinlist to open the ComposedBy form with the Song field temporarily entered as the new Song against which I can enter record(s) for Composer(s)? I tried to use the OpenArgs property and the "On Load" function but can only get this to work when a Text Box is used and not to take a new entry and populate a Combo Box on a different form.

This probably is not that clear so questions welcome! Thanks.


PS I can follow some of the VBA stuff on these forums (and have used it in part) but become out of my depth very quickly!

I hope your description means you only have 1 table for Composers.
where songs can have mutilple comps or just 1, do youd have a 3rd table: tSongComps
(similar to humans having 1 or > 1 child)

tComposers for every unique person
[CompID], [Name]
1, Henry Mancini
2, Paul McCartney

tSongComps to show who wrote what
[CompID], [SongID]
1, 1
2, 43
1,22
2,22

The composer entry form would be a subform of the Song master form.
The composer combo box would never be NotInList, because they would all be in tComposer table.

If they are Not in the list, I have a button to open the tComposer form to add missing ones,
come back to the Song Master/Comp sub entry THEN pick the newly added comps.
(you must click Refresh All button on the toolbar to see the new ones)


.

Thanks for the reply.


Yes, I have a junction table that has a foreign key for Songs and a foreign key for Composers, exactly as you describe.


However, my main data entry form is Albums with sub-forms for Tracks. A Track has Song, Artist, Album Number etc. When a new Song is entered as a Track it needs its composers to be entered. I want a form to pop up with the new Song text included to allow the composer(s) to be noted (whether new or existing). Otherwise I have to do this separately which takes a little longer. It's really to save some time on data entry.

In case of interest to future novices this is what I figured out:

1. I enter data into an Album form including a subform for Track information.
2. One of the requirements is SongName which takes entries from a combobox.
3. If the SongName is Not In List then it asks me if I want to add it. After I do it then pops up a new form for adding ComposerName(s). The trick was to default the value for the autonumber SongID in the pop up ComposerName form to Max (SongID) as it will always be this for a new song. The pop up form also has a Not In List for the Composer combo box if required.

The information on this forum has been immensely helpful on my journey through Access!