look up question

[WStoreyII]

Hello I have a database with the following fields
Date,Type,Number,Payee,Category,Major,Minor,Memo,Posted,Amount

The Major is the sub category and the minor is the sub sub category

here is my question i want to make the category column a look up with a list
of all categories which will come form another table
i want the major to do the same thing but i want it to only show the majors
that are assigned to that category and the same with the minor only the
minors
assigned to that major.

My question is two parts

A) How do i set up this query
B) What is the Best Way to set up the categories table?

Also I have a table of dvds and i was wondering how to set it up so that i
can view it all by genre and then a plus sign to drop down the info
i know that i can make one table with the category and then link it to the
other table with a subdatasheet relationship but is there another way to do
this
so that i do not have to create a new table for everyview?

Thanks for your Help

WStoreyII

 

[Graham R Seach]

1. Setup a new table, tblCategories, with the following columns:
Category (Text)
Major (Text)
Minor (Text)
....and enter all the possible permutations of Category, Major and Minor.

2. Put three combo boxes on your form; cboCategory, cboMajor and cboMinor.

3. Set cboCategory's RowSource property to:
SELECT DISTINCT Category from tblCategories ORDER BY Category

4. Leave the RowSource property blank for both cboMajor and cboMinor.

5. Set the Enabled property to False for both cboMajor and cboMinor.

6. In cboCategory's AfterUpdate event, add the following code:
Dim sSQL As String, lReturn As Long

If Nz(Me.cboCategory, "") <> "" Then
sSQL = "SELECT Major FROM tblCategories " & _
"WHERE Category = """ & Me.cboCategory & """ ORDER
BY Major"
Me.cboMajor.RowSource = sSQL
Me.cboMajor.Enabled = True
lReturn = Me.cboMajor.ListCount
Else
Me.cboMajor.RowSource = ""
Me.cboMajor.Enabled = False
End If

7. In cboMajor's AfterUpdate event, add the following code:
Dim sSQL As String, lReturn As Long

If Nz(Me.cboMajor, "") <> "" Then
sSQL = "SELECT Minor FROM tblCategories " & _
"WHERE Category = """ & Me.cboCategory & _
""" AND Major = """ & Me.cboMajor & """ ORDER BY
Minor"
Me.cboMinor.RowSource = sSQL
Me.cboMinor.Enabled = True
lReturn = Me.cboMinor.ListCount
Else
Me.cboMinor.RowSource = ""
Me.cboMinor.Enabled = False
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia