Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

I have yet another question about m

I have yet another question about m

I have yet another question about m

I have yet another question about my database, and it is linked to my previous problem of drop down lists triggering sub-lists.

First my thanks once again to Paul Bent for his original solution; but now I need to take this a step further. I created the filtered lists in a separate small database and they worked fine. However, I need to show these results in a main database, and whilst I made exact copies of the filtering rules, the sub-category list in the main database  keeps pulling up all subjects instead of the ones allocated to each category.

I don't know what it is that is making the difference between the two databases...help!

RE: I have yet another question about m

The principle is the same whatever the database. If you got it working in one apr file then it should work fine in another one. You'll just have to recheck those settings again.

If the subcategory is filtered on the combination of two previous choices, make sure the modification formula for the combined fields is correct and the field has been updated in existing records.

Paul Bent
Northwind IT Systems

RE: I have yet another question about m

OK, I discovered that the problem with the unfiltered lists was because the field boxes on the form were pointing to the main database instead of the one in which I created the original lists.

Now however, although I can click on one category and get a corresponding list appear in the sub category, I cannot change the record permanently because a pop up message informs me that the ID number in the original categories database must be filled in. I have looked at the field definition in the ID number field, but there are no validation boxes checked. I even gone into the join options between databases and tried various option combinations, but nothing works.

It seems that the only way to avoid this message appearing is by pointing the field boxes back to the main database again, which leaves me with unfiltered lists and means I am right back where I started!

RE: I have yet another question about m

The form has a main table and you can only enter data in fields from that table. Other fields from joined tables on the same form are just to display data and should be read-only The drop down lists can be bound to fields in the main table but the lists can be created from fields in another table and filtered by the value entered in the previous main table field.

Suppose you have a "Products" table containing all possible products broken down into categories:

IDNum   Group     Category      Subcat
1       Fruit     Apple         Cox
2       Fruit     Apple         Bramley
3       Fruit     Orange        Satsuma
4       Fruit     Orange        Mandarin
5       Vegetable Cabbage       Savoy
6       Vegetable Cabbage       Spring
7       Vegetable Onion         Red
8       Vegetable Onion         Spanish

The main table contains the IDNum field, the two table are joined and all join options are deselected. The reason for the id # join is to achieve a many-to-one relationship between main and products. If we joined on the Group field alone it would be an (illegal) many-to-many relationship.

In order to achieve the second level of filtering, a text field named GroupCat is added to both tables, long enough to hold the combined contents of Group and Category. Both fields must have a default modification formula:

Combine(Main.Group, Main.Category)

Combine(Products.Group, Products.Category)

Then on a form based on the main table you set up the drop down lists like this:

1. Main.ID. List created from Products.ID. Description field from Products.Group. You would also put the Products.Group field on the form and make it read-only to display the Group description when the id # is selected.

2. Main.Category. List created from Products.Category. Filtered by Products.Group using the current value in Main.Group

3. Main.Subcat. List created from Products.Subcat. Filtered by Products.GroupCat using the current value in Main.GroupCat

Paul Bent
Northwind IT Systems

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close