Help! I'm new to using Filters

[tpalmer00]

Hiya,

I've got several tables which i'm trying to cross reference and can't figure out how use the filters/write the code.

I've got a table called Course Categories which holds a list - Health&Safety, Engineering, Emergency Response etc.

I've then got the courses which belong to each of these categories broken out into 11 tables - ie.- a table containing all Health & Safety courses, another for all courses belonging to Engineering etc.

I'm trying to create a form that when you select the Course Category from a list (say Engineering) it automatically populates the next field (course title) with the available selections from the Engineering category (which are in a different table). How do I cross reference these different tables?

Any advice is greatly appreciated,

Thanks, Trish

 

[CautionMP]

tpalmer00,

I've then got the courses which belong to each of these categories broken out into 11 tables...

If you can you should put all the courses in the same table with a field that tracks the Course Catagories. By keeping the courses in 11 seperate tables you rob yourself of all the tools that make a database, well, um, a database.

That aside you can code around this:

Private Sub cboCategories_Change()
  Dim sqlRowSource As String
  Select Case cboCategories
    Case "Health&Safety"
      sqlRowSource = "SELECT * FROM tblHealth_Safety;"
    Case "Engineering"
      sqlRowSource = "SELECT * FROM tblEngineering;"
    '...
  End Select
  Me.cboClases.RowSource = sqlRowSource
End Sub

Hope this helps,
CMP

[small]P.S. Speaking from experince: You can learn a lot about coding from a less that optimal database design, but development is more enjoyable if start with good design.[/small]

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[tpalmer00]

Hi CMP,

Thanks for the advice. I've amalgamated the courses into one table as you suggested. What I still can't figure out is how to go about looking up values. For instance: I want to creat a field called COURSE CATEGORY. i've added a combo box to list the various categories.

Now I want to add a field called COURSE TITLES. I would like to be able to populate this field according to what was selected in the previous field (the course category). Can I use a query for this or does it need code?

Thanks so much,
Trish

 

[GingerR]

Hi--So you should have this set up:

Table: CourseCategories
Field: CourseCategoryID (Autonumber)
Field: CourseCategory

Data:

1 Health & Safety
2 Engineering

===============================================
Table: CourseTitles
Field: CourseTitleID (Autonumber)
Field: CourseCategoryID (Number, from table above)
Field: CourseTitle

Data:

1 1 Your Health
2 1 Safety In the Home
3 1 CPR
4 2 Drafting and Engineering
5 2 How To Dress Like An Engineer

===============================================

Right? Something like that?
So each CourseTitle is related to a CourseCategory in the 2nd table.


In your second post, you say you want to add a "field", but what you really want to add is a form control. A "field" is in a table. A "form control" is a text box, combo box, list box, radio button, etc.

In your second combo box, go into the properties. The RowSource Type should be Table/Query. Below that in Rowsource, pick your 2nd table and click the BUILD button (the little button on the right with the 3 dots on it). Put the fields down into the query grid. In the criteria row of the CourseCategoryID field, put:

[Forms]![FormNameHere]![FirstComboBoxNameHere]

and uncheck the box so that this field doesn't show.

Substitute your form name and the name of the first combo box into the statement above.

What this is saying is to only show the Course Titles that have the CourseCategoryID that is chosen in the CourseCategory combo box.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html