Filter based on field selected in previous combo box 2

[tookieholder]

Hi!
I am really new to this, but have been thrown into Access programming at work. I have a database of hundreds of technical procedures which we want to be able to quickly access through a form which contains search/filter capabilities.
The procedures are in the "Procedures" table, listed by the "procedure number" (primary key), "functional area" (procedure catagory), and "related procedures" (discriptive title of document).
I have created a form which contains a combo box listing the "functional area" catagories for the user to choose from. Below it is a box which I want to display the "related procedures", filtered based on the choice made in the "functional area" combo box. When a choice is made from the "related procedures" box, I need to have the chosen procedure information to appear in the remaider of the form below.
I have tried to create event procedure "OnEnter" to make this occur, but that doesn't work. I will be the first to admit that I'm probably making a very elementary error, but I don't have anyone around me that knows Access97 any better! Any help will be greatly appreciated!!!

 

[FuzzyBear9]

Hi,

Can you give me your email address? I have put together a small test database that does what (I think) you want.

 

[tookieholder]

That would be fabulous!!!
jfinley@poloralphlauren.com
Thanks for the help!

 

[FuzzyBear9]

Hi,

I have just sent the test database. I hope it helps.

 

[tookieholder]

FuzzyBear9-
You don't know how much I appreciate your help with this database.
I still can not get the stupid thing to work though.
I have tried to set my tables up as close to yours as possible, even using the same field names. I have begun fresh with a totally new database, importing only the necessary tables.
There seems to be some problem with my relationships.
I had them set up before, but now when I try to set up the 1-many relationships, I get nothing but errors.
I have the following tables, with the following fields:

TblFunctionalArea7
FunctionalAreaID
FunctionalArea

TblProcedures7
ProcedureID
FunctionalArea
Procedure

TblRelatedProcedures7
ProcedureID
ProcedureTitle
ProcedureLink
DateProbOccurred
LastChanged
System
FunctionalArea
ProcedureType

When I try to relate "tblRelatedProcedures7.ProcedureID" to "tblProcedures7.ProcedureID" with join type of "Include all from 'tblRelatedProcedures7', and only those = in 'tblProcedures7'", and try to enforce referencial integrity, I get the error "Invalid field definition 'ProcedureID' in definition of index or relationship".
I get the same error if I set the join as "Include all from 'tblProcedures7' and only those = in 'tblRelatedProcedurs7'". It will take it if I don't set the integrity, but then I don't get the 1-many relationship, (as I understand it).

When I try to relate "tblProcedures7" to "tblFunctionalArea7" with join "Include all from 'tblProcedures7', and only those = in 'tblFunctionalArea7'", and try to enforce referencial integrity, I get the error "Relationship must be on the same number of fields with the same data types".

I figure this has to be my problem (that my tables are not relating to one another), but I have tried everything I know, (not much) to correct it, and still can't fix the error.

Can you please help?!?!!!!
I appreciate all of the time you have spent so far, and any additional you may be able to spare for a true Access "Newbie".
Thanks again!
Tookieholder

 

[montrose]

Tookieholder, If you had them set up correctly before, check to make that that when you improted, the data type imported correctly. If you're using Autonumber for your priamry keys, make sure that it is still showing as Autonumber, no duplicates for your primary keys.

Then check your foreign keys and make sure that they're showing up as Number, Long Integer, and allow duplicates. Sometimes with importing your data types may not come in correctly. HTH, Montrose Learn what you can and share what you know.

 

[FuzzyBear9]

Can you send me a zip of the database, or some of the sample data that you are importing? Then I can provide a 'walk-through' of how (I hope!) to resolve these issues.

Don't give up!

 

[eden]

Fuzzy,

I have a similar problem, and would appreciate it if you could mail me the test database also.

Kind regards

Eden

e__101@hotmail.com

 

[tookieholder]

Thanks everyone for your help, (especially FuzzyBear9!!). The experts who answer the questions here go WAY above and beyond to help out. I have gotten my database up and running great now, with all of the kudos for this going to you. This site ROCKS!!!
Tookieholder :0)
(P.S. Do I now need to do something to "remove" this question from the forum to make room for others, or does it just automatically archive and disappear as it gets old?)

 

[jflachman]

There is a FAQ on this subject that I think might give some signficant detail to how to make this work.

faq702-777

Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[tookieholder]

Since you guys have been so helpful, here are two more "tweaking" questions I have for the wonderful new database you have helped me produce.

In my form view, in my first form "IS Procedure Search Form", I have a text box that controls the results shown in the subform. I can use the "Record" back and forward arrows to scroll through the various options that the user has to choose from. (ie.. text box "Functional Area" shows results "Shipping", "Receiving", "Inventory", etc, as the user scrolls with the record arrows).
What I would like is one of two refinements to that, if possible.

1) Could the "Functional Area" text box be changed to a list or combo box which displays all 26 options for the user to choose between, then allow the user to click on their choice ("Shipping", "Inventory", etc) and have the issues that pertain to that catagory appear in the subform. (This operation would be the BEST of the two refinements).

2) OR... could the "Functional Area" text box be set up in such a way that the user can input the first letter of what they THINK the area that they are looking for might be under, and have that appropriate functional area appear in the text box. (ie... user types "I" and they get a list box showing "Inventory", "Interfaces", "Invoicing" etc., where they can then have the option of chosing between those areas listed. When they choose "Interfaces", for example, they would then see the information regarding interfaces procedures in the subform.
I know I have seen this (the form autofilling based on the alpha character typed into the field), but I can't remember where it was located, and I can't even think of any key words to use for a lookup, either on this site, or in my OVERabundance of user texts. So far I have had no luck finding any reference to this function.

Any help on either of these possible refinements would be great!
Thanks again for all of your help!!
Tookieholder

 

[FuzzyBear9]

Hi,

Have a look at RickSpr's faq on: "How do I limit the contents of one combo box based on the selection in another." (Go to advance search, select FAQs, then type 'Combo boxes' in the KEYWORD SEARCH box and click enter).

Combo boxes have a setting called 'AutoExpand' (found under the properties tab). When this is set to yes. Access will automatically fill in the combo box with items that match those letters, eg. when you type 'I' Access will move to the first item in the list beginning with 'I'. As you proceed to type letters, eg. INV, Access will highlight only those items that match the criteria (or the nearest match)