Subform based on selection 1

[wlong]

Is it possible to show one of three different subforms depending on a slection from a drop dwon list on the main form?
I would like the subform to show a particular cleaning checklist depending on which tool is being cleaned.
I've been searching for days, and I can't find any information to indicate that it's even possible.

 

[jdgreen]

You can do this with either a If, Then, Else statement or a Select Case. After you do that just choose which subform you want to be visible. Like:
If YourComboBox "Clean1" Then
Subform1.Visible = True
Subform2.Visible = False
Subform3.Visible = False
ElseIf YourComboBox "Clean2" Then
Subform1.Visible = False
Subform2.Visible = True
Subform3.Visible = False
and so on....




John Green

 

[JeremyNYC]

John's way should work fine. I might think about it differently, though. If you've got tools and cleaning tasks, I would keep those data in just two tables. It's hard to be sure from what you wrote, but it sounds like you've got different tables for the different tools, or at the very least different forms to filter the cleaning tasks for the different tools. If either of those are the case, I'd suggest you make some changes.

I'd put all of the cleaning tasks in one table, with a column to indicate the tool to which they correspond. Then your combo box can have as its rowsource something like this: SELECT ToolID, ToolName FROM tblTool ORDER BY ToolName. Set up the cbo so there are two columns, with widths "0;" and make the first column be the bound column. The user will see just the names of the tools. Access will see just the ToolID.

In the AfterUpdate event of the cbo, do something like this:
me!NameOfControlThatHoldsYourSubform.form.filter = "ToolID = me!cmbTool"
me!NameOfControlThatHoldsYourSubform.form.filteron = true

Hope that helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[grnzbra]

If it's possible that any given task could be required by several tools (ie. the same task shows up on several of your subforms), it might be best to use a ToolTable, a TaskTable and a ToolTaskTable, which would have a record for each task for each tool.

You could have a combo box on the form for selecting the tool and use this as criteria for a query selecting records from the ToolTaskTable joined on the TaskID to the TaskTable.

If this drove a subform set up for continuous forms the required tasks would show up in the same manner for each tool and adding or deleting a process would require nothing more than the addition or deletion of a record from the ToolsTaskTable.

I did this with a database full of reports which required various parameters to be fed to them and it worked out very nicely.

If you are going to use the If statment methode as suggested by jdgreen, design all the subforms to be visible = false. Then create a sub that sets them all to visible = false. Then, your if statement needs to only contain one line calling the ResetToInvisible sub and one line setting the desired subform to visible = true. That way, if you add another subform, you need only add one line to the reset sub and copy one of the existing if statments and change the name of the subform to be made visible.

 

[wlong]

grnzbra,
Most tools do use one checklist. They recently decided they wanted different items for just a few tools.
What I have now is a tool info table which has the ToolNum as the key, then a tool preventative maintenance history table. The pm history table is mostly a number of yes/no fields, and the form is a series of check boxes. (probably not the most efficient way to set it up, but I din't know the first thing about DBs when I started it.)
I added a field to the tool info table to identify which checklist it should use. On the form, I've got a hidden text box to pull that identifyer once the tool number is selected. I planned on using that to select which subform to use. I tried the If statement above, but I couldn't get it to work. I may not have set up the sub correctly. I don't have the best handle on vb code.
Actually, it doesn't even have to be a subform. It could be a pop up if that's easier.

 

[grnzbra]

OK.

Let's see if I understand this correctly.

Most tools have the same checklist of cleaning operations. Now there are a few tools that have either different cleaning operations or additional cleaning operations.

The way to make the above code work (the if statements) would be something like this.

One form with a combo box for tool selection and a bunch of check boxes for the cleaning operations. In the design of the form, set the Visible property of all the check boxes and their labels to False.

The OnCurrent event would take code something like

Sub OnCurrent()

Call ResetVisible()

If ToolComboBox = Tool1
Me.Checkbox(?).Visible = True
Me.Checkbox(?).Visible = True
.
.
.
GoTo DoneSettingVisibility
End If

If ToolComboBox = Tool2
Me.Checkbox(?).Visible = True
.
.
.
GoTo DoneSettingVisibility
End If

Me.Checkbox(?).Visible = True
.
.
.

DoneSettingVisibility:
Exit Sub

The ResetVisible sub would look something like

Sub ResetVisible()
Me.Checkbox1.Visible = False
Me.Checkbox2.Visible = False
.
.
.
Exit Sub

What is going on here is when you bring up a new record in the form, you have an on current event. That starts your code. The first thing it does is call the ResetVisible sub which is a list of every checkbox on the form and sets them to Visible = False.

Then the process returns to the OnCurrent code where it hits the if statements. Each of the unusual tools has an if statement and a series of lines which, similar to the lines in the ResetVisible sub, sets the appropriate checkboxes to Visible = True. When all the checboxes for the particular tool have been set to Visible = True, the Exit sub line skips all following lines and gets out. If the selected tool is not one of the special tools, you drop down to the last lines which apply to all the rest of the tools and make them visible.

This method (having a bunch of checkboxes, one for each cleaning operation) can cause difficulties later. My original description is a method which will allow for lots of tools/cleaning methods combinations. However the code gets a little involved. This is cheap and dirty but is relatively simple. Trying to query the data might get tricky.

Hope this helps. I do check in on weekends, so if you need any info over the weekend, feel free to ask.

 

[wlong]

Sorry it took me so long to get back. Too many other things going on at work.
Anyway, after a couple little things, I got the visibility working. I had to put a then in the if statement, and change the OnCurrent to Form_Current. That's probably a version thing since I didn't mention my company enjoys living in 1997. I also had to add Call Form_Current to the click sub on the tool select box. I assume that's due to the fact that the control that is setting the visibility is requeried in that sub, because the call had to be after the requery, or it wouldn't work.
But alas, it finally works, and I thank you greatly.