Making a form entry dependent on another

[vnfoster]

I know this is probably in here somewhere not sure where or what to look under to find it. Basically I have two form entries one called Program and one called category. When a user selects a lookup entry I only want certain categories to show. I have created tables containing these look up options. Should I be using an iif/then vba or something different?

 

[Beetle]

Have a look at Cascading Combo Boxes.

 

[vnfoster]

Here is the code I tried but it just coming in with blank options

On Error Resume Next
Select Case CboFSMISS.Value
Case "Casualty-Disaster"
cboCategory.RowSource = "Casualty_Lookup"
Case "Exceptional Family Member"
cboCategory.RowSource = "EFM_Lookup"
Case "Deployment"
cboCategory.RowSource = "Deployment_LookUp"
Case "Family Advicacy"
cboCategory.RowSource = "FAP_Lookup"
Case "Family Employment Readiness"
cboCategory.RowSource = "FERP_Lookup"
Case "Moblization & IA Support"
cboCategory.RowSource = "IA_Lookup"
Case "General Life Skills"
cboCategory.RowSource = "LifeSkills_Lookup"
Case "New Parent Support"
cboCategory.RowSource = "NPS_LookUp"
Case "Ombudsman Support"
cboCategory.RowSource = "Ombudsman_LookUp"
Case "Personal Financial Managment"
cboCategory.RowSource = "PFM_LookUp"
Case "Relocation Assistance (Includes Typical I&R)"
cboCategory.RowSource = "Relocation_LookUp"
Case "Sexual Assault Prevention &Response"
cboCategory.RowSource = "SAPR_Lookup"
Case "Transistion Assistance"
cboCategory.RowSource = "TAMP_Lookup"
Case "Victim Advocate"
cboCategory.RowSource = "VA_Lookup"

End Select
End Sub

 

[Beetle]

In what event did you put the code, and what do you mean by "it just coming in with blank options"? Do you mean the Category combo box remains blank regardless of the choice made in cboFSMISS?

Also, why did you create separate tables for all the categories? Do they all have different sets of attributes?

 

[vnfoster]

yes the category box remains empty no matter what choice you make. Everything in the unique category tables are a subdivision of FFSMIS
Here is an example
Program: Disaster Response
Subcatogory: Training Exercise, Collateral Outreach, Crisis Incident Training

Program: Family Advocacy
Subcatogory: Basic FAP Prevention (Youth Services), Basic FAP Prevention (GMT)

Some programs have two subcatogories and some have up to 15.

 

[vnfoster]

I put the event in cboCatogory

 

[Beetle]

Based on what you've said so far I don't see a need for you to divide the subcategories into separate tables. As far as Programs and their subcategories go, it sounds like you only need two tables, like the following example;

tblProgram
ProgramID (Primary Key)
Description

this table would have values like;

ProgramID___Description
1__________Disaster Response
2__________Family Advocacy
3__________Relocation Assistance
etc.

Then the categories table;

tblCategories
CategoryID (Primary Key)
ProgramID (Foreign Key to tblPrograms)
Description

which would have values like;

CategoryID_____ProgramID____Description
1________________1________Training Excercise
2________________1________Collateral Outreach
3________________2________FAP Prevention (Youth Services)
4________________2________FAP Prevention (GMT)
5________________3________Relocation Category 1
6________________3________Relocation Category 2

You then have two combo boxes on a form, one for Programs and one for Categories. The Row Source of the Program combo box would be a query that returns the two fields from that table;

Select ProgramID, Description From tblProgram Order By Description;

It will also be important to set the Bound Column, Column Count and Column Width properties of this combo box accordingly;

Bound Column: 1
Column Count: 2
Column Widths: 0", 1" (or whatever width works best for the second column)


The Row Source of the Category combo box would be a query that returns the three fields from that table and has a criteria reference to the Program combo box in the ProgramID column;

Select CategoryID, ProgramID, Description From tblCategories Where ProgramID = [Forms]![YourFormName]![cboProgram]

and the column properties would be;

Bound Column: 1
Column Count: 3
Column Widths: 0", 0", 1" (adjust the last column as needed)

Then in the After Update event of the Program combo box you requery the Category combo box;

Private Sub cboProgram After_Update ()

    Me!cboCategory.Requery

End Sub

Post back if you have questions or if I have misunderstood your table structure.