Want to know if I can do this and how??

[Cricker]

I have a form created and a sub-form on the bottom. On the sub-form I have a table for it with three headings.
1. Main Issue
2. Sub Issue
3. Issue

Under each Main Issue there is a Sub Issue but only certain Sub Issues pertain to the Main Issue and same with the the Issue regarding the Sub Issue.

Example -
Community Outreach (Main)
Arts & Heritage (Sub)
Arts Group (Issue)
Heritage Group (Issue)
Community Associations (Sub)
Fringewood (Issue)
...... and so on.....

I would like for the user to see all the Main Issues in a drop down box. When the user selects the Main Issue I would want them to see all the Sub Issues that pertain to that Main Issue. When they select the Sub Issue I want all the Issues shown pertaining to that selection with the Sub Issue.

Can this be done. I'm using Access 97.

Thanks
Chris

 

[PaulBricker]

Normally this would be fairly straight forward. In the AfterUpdate Event for the MainIssue combo you would have this type of code.

Dim strSQL as String
strSQL = "Select Table.SubIssue From Table Where Table.MainIssue = '" & Forms!FormName!ControlName & "'"
Me.SubIssueComboName.RowSource = strSQL

or substitute this if your control is on a subform.

Dim strSQL as String
strSQL = "Select Table.SubIssue From Table Where Table.MainIssue = '" & Forms!MainFormName!SubformName!ControlName & "'"
Me.SubIssueComboName.RowSource = strSQL

This should be the outline you use for the SubIssue and Issue Combo/List boxes.

Paul

 

[Cricker]

Somewhat confusing but I'll try to understand it. I am using a Table format for the sub-form. So how could I use that for the table??

Chris

 

[PaulBricker]

Are the subissues and issues in dropdown boxes also? That was my original impression.

Paul

 

[Cricker]

I'm finishing off someone elses program. Not a nice one. They have a subform on a form that is a table with all those headings - Main Issue - Sub Issue - Issue. Each heading is from another table that all the info is inputted. Making sense???

Chris

 

[PaulBricker]

Sorry Chris. I'm still a bit confused. You say the subform is a table with all those headings. I assume you mean it's in Datasheet View (which looks like a Table). But the question still remains- Are the Main Issue, Sub Issue and Issue Fields on the Subform Combo Boxes or Text Boxes and are you trying to set the RowSource for these using code? That is what your example implies. Select a value from the Main Issue Dropdown box. Then when you go to the Sub Issue dropdown, it only show Sub Issues related to the Main Issue. Then after you select something from the Sub Issue, the Issue dropdown only shows Issues related to the Sub Issues. Does that sound right.

Paul

 

[Cricker]

Yes it's in Data sheet view. The fileds on the subform are Combo boxes. Yes, that is what I'm trying to do. But I have a form outside that to add more Main Issues, Sub Issues and Issues.

Chris

 

[PaulBricker]

Chris, you should be able to do what you want with the code I posted this morning. It should look like this
Dim strSQL as String
strSQL = "Select Table.SubIssue From Table Where Table.MainIssue = '" & Forms!MainFormName!SubformName!ControlName & "'"
Me.SubIssueComboName.RowSource = strSQL

If you have problems understanding what to do, post the name of your table,the name of your Main Form, the name of the subform and the Field Names involved and I can rewrite the code to include your information. Then you will need to cut and paste it to the AfterUpdate Events.

Paul

 

[Cricker]

Okay. I changed the form. The subform is now changed into a form not in dataview. These are the names:

Main Form - ConstituentTable ** do u need this???

SubForm - QRYTEST

Tables:
TBLControl ** this is where i get the ID's for each to
bring up the name - ID Number value

TBLIssueCategory ** Main Issues
- IssuesID ** Number to bring it up
- Issue ** Name

TBLsubIssues ** Sub Issues
- SubIssueID
- SubName

TBLIssueID ** Issues
- IDIssueSub
- IssueSub

Hope this all makes sence. The person before me did the shytes on naming. The program is a mess.

If easier to Email me u can do so.... Chris.Whitten@ottawa.ca

Thanks
Chris