Perhaps I might make a few points supplementary to those already made her.
1. You are not limited to one method of data entry. You can for instance
have a Songbooks form with a Songs subform, i.e. the songbook is entered in
the parent form and the songs entered in the subform by selecting from a
combo box. The combo box's list should be multi-columned so you can
distinguish between different songs with same title, e.g. La Mer ( I know the
Debussy piece is really a tone poem not a song, but I'd guess there must be
piano transcriptions of it). A new song can be entered via the combo box
with some code in its NotInList event procedure (I've included an example
below. You can also have a Songs form with a Songbooks subform, i.e. you
enter a song and select which songbooks contain the song in the subform. At
any time you can chose which method to use depending on the circumstances.
One you've entered data via one form the other will reflect the new data of
course, so if you've entered a songbook and then selected the songs it
contains, if you then go to one of those songs in the Songs form its
Songbooks subform will include the songbook you entered.
2. Don't use distinct tables for composers and arrangers. As a composer
might also be an arranger there is the risk of inconsistencies arising if the
same person's details are recorded in two tables ( I once came across a
database in which there were three different versions of my name as the
author of technical papers!). There are two ways you could approach this.
One would be to regard whether a person is a composer or arranger as an
attribute of the relationship type between songs and people, i.e. to have a
columns SongID, PersonID and something like Role in the table which models
this relationship type.
The other approach, is to see Composers and Arrangers as subtypes (aka
sub-classes) of type People. Types and subtypes are modelled in a relational
database by having one table for the (super)type and one for each subtype,
the latter being related one-to-one to the (super)type. The primary key of
each subtype table is therefore also a foreign key referencing the primary
key of its (super)type. In your case you'd have a table People and tables
Composers and Arrangers.
Which of these approaches is most appropriate depends on whether there are
attributes of composers which are not shared by arrangers. Attributes like
FirstName, LastName etc are obviously shared so would go in the People table.
Whether there are any non-shared attributes which would go in either of the
other two tables I'll leave you to decide. From a layman's point of view it
seems unlikely to me that there are and the first approach is probably the
most appropriate. With this a song in a songbook would be modelled by two
rows in the table if it had a composer and an arranger, i.e. having the same
SongID and SongbookID but different Role values, Composer in one row and
Arranger in the other. It would have the same PersonID value if the composer
and arranger are one and the same person, different ones if they are not.
Its worth noting here that SongID, PersonID and Role are in fact a composite
foreign key in this table as they will reference a further table in which the
composition or arrangement of a song by a particular person is recorded.
That table itself models a relationship between Songs and People, each of
which would be modelled by a table. You'll see that the model is becoming a
little more extended now; I'd advise that you spend some time drawing it out
diagrammatically on paper before creating the tables. That way you can
mentally test it and make any necessary amendments before getting in too deep.
3. Generating a report on the basis of a song title will be quite a simple
task, simply requiring you to join the necessary tables in a query for use as
the report's RecordSource and creating a dialog form in which you can select
the song title and open the report filtered to that song. You could use a
combo box to select the song, or if you wanted something more flexible you
could use a multi-select list box to select one or more songs simultaneously.
Whichever you use there are pretty standard routines for this.
Finally here's the sample code for a combo box's NotInList event procedure
which I mentioned above. This is for adding a new city, and opens a form in
which other data about the new city is added, but the code in your case would
be very similar:
Private Sub cboCities_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
Along with the above code the following goes in the frmCities form's Open
event procedure to set the default value of the city control in the form to
the value which was entered in the combo box on the first form:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub
Ken Sheridan
Stafford, England
