Conditional Formatting

[DanOzDirect]

I am somewhat of a novice in MS Access, so this may be something quite simple, but, I am trying to prepare a report, based on two tables, which highlight a value that appears in both tables.

Table 1 is a complete, unchanging list, of the 1001 albums you should listen to before you die

Fields:
Year (number)
Artist (text)
Album (text)

Table 2 is a table that grows, of my entire CD collection
Year (number)
Artist (text)
Album (text)
Comments (text)

I am trying to produce a report that is Table 1 in its entirety, with the albums on the list that I have highlighted in red. I want the report to change the text colour based on the values in Table 2. so far I have had no luck getting a conditional formatting expression to work.

Basically I want the following (which works for a query) but as a conditional formatting expression



[Table1].[Album]=[Table2].[Album]

Cheers
Dan

[jzwp11]

Welcome to the forum!

First, I had some suggestions as to your table structure. An artist can make multiple albums, and some artists (i.e. more than 1) may collaborate to produce an album, this would be considered as a many-to-many relationship which is accomplished using a junction table.

tblArtists
-pkArtistID primary key, autonumber
-txtArtistName

tblAlbums
-pkAlbumID primary key, autonumber
-txtAlbumName
-longYearAlbumReleased
-logListenToBeforeIDie (yes/no field to identify this album as one of the 1001)
-dteAddedToMyCollection (date added to your own collection)

tblAlbumArtists (a table to relate albums and the artists associated with them)
-pkAlbumArtistsID primary key, autonumber
-fkAlbumID foreign key to tblAlbums
-fkArtistID foreign key to tblArtists

Just as a note, the word "YEAR" is a reserved word in Access, so it should not be used as a table or field name.

If you opted to not use the junction table and just wanted to have the artist listed with the album (not recommended) then the table would look like this. I would still have a table of artists, so you don't have to retype the artist name for each album (reduces typographical errors as well).

tblAlbums
-pkAlbumID primary key, autonumber
-txtAlbumName
-longYearAlbumReleased
-fkArtistID foreign key to tblArtists
-logListenToBeforeIDie (yes/no field to identify this album as one of the 1001)
-dteAddedToMyCollection (date added to your own collection)

Now as to your report, you will need a query to pull the list of the 1001 albums. You can do this by setting the criteria for the yes/no field to -1 (yes). You would bind your report to this query. As to the formatting, you would use the on format event of the section in which the album is listed. You would use the date field (dteAddedToMyCollection) as the conditional. If there is no date (null), you do not have the album in your collection; if there is a date, then you have it in your collection.

The code would look something like this (I just did the album name, if you want other controls to have the same font color then you would need to add them to the appropriate locations with their appropriate color within the IF..THEN statement.

If IsNull(Me.dteAddedToMyCollection) Then
Me.txtAlbumName.ForeColor = 8388608
Else
Me.txtAlbumName.ForeColor = 255
End If

I've attached an example database.

[DanOzDirect]

That’s for that, the example database is excellent, and in hindsight, I probably should have set up my database that way.

However, given that moving to that new (better, more robust) structure, will involve me starting from scratch, I was wondering if there was a simple way to format based on a comparison of fields from two separate tables.

[jzwp11]

You need to get the information together whether it is in a query or through the report itself. One way that might work is to use a domain aggregate function to populate the control on the report.. For example, you may base your report on the table that has the 1001 albums and then place a control on the report that uses the either the DCount() function. The function would basically search the other table to see if the album (album name?) exists there. If the DCount()>0, then you can change to one color and if =0 then another.