Select value multiple times

[GelC]

Hi all,
I'm thinking of doing this but not sure if it's doable in MS Access.
I want to have a box where I can pick up name multiple times from a author table and store the value in author field in tblsongs.
For example, I want to enter a song that was composed by two authors, so I want to pick Rick G. from the list, then pick Paul J. The author field in tblsongs should be Rick G., Paul J.
Please guide me. Thanks in afvance.

 

[Pampers]

Hi GelC,

I think that will be doable. You could use a combobox with all the author names and a button which will add the author to your author(s)-field on the form (and table). Then you have to check if the author you add is the first, second, third (etc) or last because of adding a ",". But I would think you have little normalisation-issue here. Since one song can have an infinitive number of songwriters. (1 to many). So maybe the best way to set it up is a songwriter table for every song...

You could also put, to make it maybe easier, a couple of author names in your tblSongs (author1, author2...) and then concatenate them when needed... The add button checks if author1-field is yet populated, if yes, put in author 2, if author2 is populated, put it in author3 and so on.





Pampers
Keeping it simple can be complicated

 

[GelC]

It sounds complicate to me.
So the add button function must be build with coding behind it, correct. It doesn't seem to be easy to me at all.
Could you give me more hints?

 

[Pampers]

Hi GelC.

Well using some code is not that complicated.
If you want to put all the authors in one field, you could use this code (on a button click)

Private Sub Command0_Click()

If Me.Authors & "" = "" Then
    Me.Authors = Me.cboAuthors
Else
    Me.Authors = Me.Authors & ", " & Me.cboAuthors
End If

End Sub

Pampers
Keeping it simple can be complicated

 

[Remou]

In a normalized database (see

http://www.r937.com/relational.html),

you would need a junction table:

[tt]tblSongAuthors
SongID FK ) Combined as unique key
AuthorID FK ) for this table[/tt]

If your main form is songs, you can add a subform for authors based on tblSongAuthors with SongID for the Link Child and Link Master fields. The field AuthorID can be bound to a combobox with a row source from the authors table. Similarly, an authors main form could have a songs subform with AuthorID for the Link Child and Link master fields. These forms can be easily built using wizards.

http://lessthandot.com