Rick:
The 'sign in' is quite simple to set up. As the application's opening form,
frmLogIn say, you just need an unbound dialogue form with a combo box, cboDJ
say, with the following as its RowSource:
SELECT DJID,
Firstname & " " & LastName AS FullName
FROM DJs
ORDER BY LastName, FirstName;
Set the combo box's BoundColumn property to 1, its ColumnCount to 2 and its
ColumnWidths to 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero).
Add a 'Confirm' button to the form with the following in its Click event
procedure:
If Not IsNull(Me.cboDJ) Then
DoCmd.OpenForm "frmPlayList"
Me.Visible = False
End If
where frmPlayList is a form bound to the PlayList table. I'd see this table
differently, however, so that its not just the song played that's recorded
(no pun intended!) but the version of the song , i.e. the song artist and
album (how my instances of Bob Dylan's 'Like a Rolling Stone' are there on
separate albums for instance?). This would mean changing the SongArtists
table to:
SongVersions
SongVersionID <autonumber primary key>
SongID <link to Songs>
ArtistID <link to Artists>
AlbumID <link to Albums>
And the SongID field in the PlayList table would now be SongVersionID.
In the Open event procedure of the play lis form set the DJID default value
to that selected in the log in form with:
Me.cboDJ.DefaultValue = """" & Forms("frmLogIn").cboDJ & """"
This would mean you'd have a cboDJ combo box on the play list form, but this
time bound to the DJID field in the PlayList table. The quotes in the above
line of code BTW are because the DefaultValue property is a string expression
regardless of the data type of the underlying field. Often they are not
essential, but its always best to include them as sometimes they can be
crucial when you might not think they'd be needed. This combo box should be
prominent on the form as it would be used if the DJ changes without having to
close down the application. Its AfterUpdate event procedure should set the
hidden log in form's combo box to the DJ select so the two are kept in sync
with:
Me.cboDJ = Forms("frmLogIn").cboDJ
On the play list form would be a list box bound to the SongVersionID field
with a multi-column list which lists the songs alphabetically along with the
artist and album in other columns of the list.
In answer to your point about the number of songs/albums etc. you don't need
to add all of the collection to the database at the start. It can be done on
the fly as each song version is first played, so the tables would build over
time. You'd just need a 'New Song Version' button on the form which would
open another form to add a new song version row to the SongVersions table and
then returns to the play list form with the new song version selected. As it
happens I've recently been working on a nursing qualifications database for a
hospital which, while the type of data is very different, does something very
similar to this in principle, so I know it can be done relatively easily.
You can combine the two approaches of course; for instance you might want to
set up a procedure where new CDs are added to the tables when acquired.
I you wish to automate the entry of the WhenPlayed date/time into the table
you could have the following code in the list box's AfterUpdate event
procedure so that current the date/time is automatically entered when a song
version is selected:
Me.WhenPlayed = Now()
Note that when a new song version is added in another form you'd have to set
the value of the WhenPlayed control in the play list form as well as
requerying and setting the value of the list box to the new song version.
This is because the AfterUpdate event only fires as a result of user action,
not the assignment of a value to a control in code.
Ken Sheridan
Stafford, England
