Tim,
Step 1 - Assign all your CDs a title. Go through all your pictures note what CD
the picture is on and give each a title such as "Dad and Family". Don't
duplicate any titles. Write down the titles. As you go through the pictures,
write down one to three Search Words to associate with each picture such as
"Dad". Don't duplicate search words for the same picture but you can duplicate
search words for different pctures. For example, if you use "Dad" for eight
pictures, when you go to search and you search on "Dad", you will get all eight
pictures. The caution here is to not use too broad of search words that apply to
too many pictures!
Step 2 - Create the following tables:
TblCD
CDID
CDTitle
TblPicture
PictureID
PictureTitle
TblCDPicture
CDPictureID
CDID
PictureID
TblSearchWord
SearchWordID
CDPictureID
SearchWord
Make CDID, PictureID, CDPictureID and SearchWordID autonumber. Make CDID and
PictureID in TblCDPicture Number - Long Integer data type. Make CDPictureID in
TblSearchWord Number - Long Integer data type.
Step 3 - Create a form based on TblPicture and enter the titles of all your
pictures. (Search words come later!)
Step 4 - Create a form named SFrmCDPicture based on TblCDPicture and add a
subform named SFrmSearchWord based on TblSearchWord. SFrmCDPicture needs to be a
single record type form and SFrmSearchWord needs to be a continuous form. The
PictureID field on SFrmCDPicture needs to be a combobox based on TblPicture.
Step 5 - Create a form named FrmCD based on TblCD and make it a single record
form. With the form in design view, click on Windows - Tile Vertically at the
top of the screen and click and drag SFrmCDPicture into the detail section of
FrmCD. This makes SFrmCDPicture a subform on FrmCD. While still in design view,
select the subform control, open properties and go to the data tab. LinkMaster
and LinkChild need to be CDID. If they are not, click on the three dots to the
left of either and Access should fill them in.
Close out everything.
Open FrmCD. You will be able to enter the title of the CD. In the subform, you
will be able to select pictures form the combobox. In the sub-subform you will
be able to enter from one to three search words for each picture.
Step 6 - Create a query named QrySearchResults based on TblCD, TblPicture,
TblCDPicture and TblSearchWord. Include CDTitle, PictureTitle and include
SearchWord three times.
Enter the following expression for the criteria of the first SearchWord field:
Forms!FrmSearchResults!SearchWord1 Or (Forms!FrmSearchResults!SearchWord1 Is
Null)
Enter the following expression for the criteria of the second SearchWord field:
Forms!FrmSearchResults!SearchWord2 Or (Forms!FrmSearchResults!SearchWord2 Is
Null)
Enter the following expression for the criteria of the third SearchWord field:
Forms!FrmSearchResults!SearchWord3 Or (Forms!FrmSearchResults!SearchWord3 Is
Null)
Step 7 - Create a query named QrySearchWord1 based on TblSearchWord and only
include SearchWord.
Step 8 - Copy QrySearchWord1 and name the copy QrySearchWord2. Put the following
expression in the criteria of the SearchWord field:
Not Forms!FrmSearchResults!SearchWord1
Step 9 - Copy QrySearchWord2 and name the copy QrySearchWord3. Put the following
expression in the criteria of the SearchWord field:
Not Forms!FrmSearchResults!SearchWord1 And Not
Forms!FrmSearchResults!SearchWord2
Step 10 - Create a form named FrmSearchResults based on QrySearchResults. Make
the form continuous. In the header of the form add three unbound comboboxes
named SearchWord1, SearchWord2 and SearchWord3. Base SearchWord1 combobox on
QrySearchWord1, Base SearchWord2 combobox on QrySearchWord2 and Base SearchWord3
combobox on QrySearchWord3. In the detail section add the fields, CDTitle and
PictureTitle.
Step 11 - Add a command button on FrmSearchResults. Put the following code in
the Click event of the command button:
Me.Requery
After you have your data entered in the database, you will now be able to open
FrmSearchResults, select one to three searchwords and get a list of the CDs and
the pictures on the CDs that match the search word(s) selected.
