Did not see anything on the forum, will be happy to take existing suggestions if there are any.
A Form based on a single table (with associated look up tables for the list boxes).
A List on top shows a single Site Location - with its Associated Permits classified as Federal, State, County, Misc i.e TYPE.
Clicking on the record for displays multiple controls below associated with FED, State, County, and Misc fields.
On the top Site Location list box
- if a Federal type is chosen, only the controls associated with Federal should be visable.
- if a State type is chosen, only the controls associated with State should be visable.
- if a County type is chosen, only the controls associated with State shoudl be visable. (hide the rest of the controls)
General Question:
The Type can be harvested from the form's recordset or from a control displaying the Type below. It needs to change when a TYPE changes.
What is the best spot for the code to call a Subroutine to show and hide the groups of controls associated with each type?
- Top Location List box change event
- Control below that displays the TYPE on its change event
- Other?
My intent is to create a Subroutine in the Form, pass in the Type - and use a case statement to show/hide each control.
So, where would be the best place to call this Subroutine?
From the sound of it, the listbox's after update event. The events for the textbox won't fire if the value is changing because of a change to the listbox. It doesn't sound like you're there yet, but I'd likely use a loop of the controls on the form and use the tag property of each to determine whether to display it or not.
B.T.W. Inherited the Access 2007 DB - trying to update feature quickly
Agreed. That will work well. Like the idea of tags as well. There are really 10 permit types, and differen controls share differnt sets.
Now, I have a "new record" button. So, I though that if the data changed, I might find a more form level event. Non of the form events seem to recgonize the change. So, it may be necessary to keep it in the top level List box AfterUpdate event.
And then add code for the New Record - in the TYPE list box (the first thing the user must choose) change event.
Thanks for the input. I may post some code for the benefit of others later next week.
Here is a quick and dirty example used to show to the customer as a demo. With this demo, they have more ideas on what to do.
Basically, in the Lists box described above, the TYPE and State are passed into a Sub to show / hide controls on the form. E.G. if it is FED, then hide the State and County controls. For the final, I will come up with using tags and perhaps a For Each Control. However, this code might be useful for other people that don't use collections of objects code .. just yet.
For a small set, this is fairly easy to do, and copy and past (then just change the correct True to False for the desired results. Note: I had one state with a special exception.
From Listcontrol after update event - change records.
Each record in list control includes a RegulatoryType and a StateLocation.
The idea is to show / hide controls every time a new record is selected on the list box. Many controls are in common (not shown) so they are never hidden. Others (shown below) show or hide depending on the Fed, St, Co regulationtype.
All records go into one table. The user can never fill in the wrong control because it is hidden for that type.
Code:
Private Sub ActivateControlsByRegulatoryType(RegulatoryTypeCode As String, StateLocation As String)
' Every time the list box is called, read the Regulatory type and show/ hide fields on the form
On Error Resume Next
100 If Len(RegulatoryTypeCode) >= 2 Then ' check for empty string
Select Case RegulatoryTypeCode
Case "Fed" ' Show / hide text boxes for Federal permits
' Federal Controls
lblDateNOSSubmitted.Visible = True
Dt_NOS_Sub.Visible = True
lblElectronicSubmissionNoFED.Visible = True
NOS_eSubNo.Visible = True
lblRoadDesignSubmitted.Visible = True
Dt_DsgnSub.Visible = True
lblRoadDesignSubmitted.Visible = True
Dt_DsgnSub.Visible = True
lblRoadDesignApproved.Visible = True
Dt_DsgnApv.Visible = True
' State Controls
lblDirHorizRequestSubmitted.Visible = False
Dt_DirHorz_Sub.Visible = False
lblLocExceptionSubmitted.Visible = False
Dt_LocXcpt_Sub.Visible = False
lblDirHorizRequestApproved.Visible = False
Dt_DirHorz_Apv.Visible = False
lblLocExceptionApproved.Visible = False
Dt_LocXcpt_Apv.Visible = False
' County Controls
lblCountyOfPermit_Label.Visible = False
cbointCountyPermit.Visible = False
LblWellPhysicalLocation.Visible = False
lblWellStCounty.Visible = False
Case "St" ' show / hide text bosex for State permits
lblDateNOSSubmitted.Visible = False
Dt_NOS_Sub.Visible = False
lblElectronicSubmissionNoFED.Visible = False
NOS_eSubNo.Visible = False
lblRoadDesignSubmitted.Visible = False
Dt_DsgnSub.Visible = False
lblRoadDesignSubmitted.Visible = False
Dt_DsgnSub.Visible = False
lblRoadDesignApproved.Visible = False
Dt_DsgnApv.Visible = False
' State Controls -------------------State -------
lblDirHorizRequestSubmitted.Visible = True
Dt_DirHorz_Sub.Visible = True
lblDirHorizRequestApproved.Visible = True
Dt_DirHorz_Apv.Visible = True
If StateLocation = "WY" Then
lblLocExceptionApproved.Visible = True
Dt_LocXcpt_Apv.Visible = True
lblLocExceptionSubmitted.Visible = True
Dt_LocXcpt_Sub.Visible = True
Else
lblLocExceptionApproved.Visible = True
Dt_LocXcpt_Apv.Visible = True
lblLocExceptionSubmitted.Visible = True
Dt_LocXcpt_Sub.Visible = True
End If
' County Controls -----------------county -------
lblCountyOfPermit_Label.Visible = False
cbointCountyPermit.Visible = False
LblWellPhysicalLocation.Visible = False
lblWellStCounty.Visible = False
Case "Co" ' Show / hide text boxes for County permits
lblDateNOSSubmitted.Visible = False
Dt_NOS_Sub.Visible = False
lblElectronicSubmissionNoFED.Visible = False
NOS_eSubNo.Visible = False
lblRoadDesignSubmitted.Visible = False
Dt_DsgnSub.Visible = False
lblRoadDesignSubmitted.Visible = False
Dt_DsgnSub.Visible = False
lblRoadDesignApproved.Visible = False
Dt_DsgnApv.Visible = False
' State Controls
lblDirHorizRequestSubmitted.Visible = False
Dt_DirHorz_Sub.Visible = True
lblLocExceptionSubmitted.Visible = False
Dt_LocXcpt_Sub.Visible = True
lblDirHorizRequestApproved.Visible = False
Dt_DirHorz_Apv.Visible = False
lblLocExceptionApproved.Visible = False
Dt_LocXcpt_Apv.Visible = False
' County Controls
lblCountyOfPermit_Label.Visible = True
cbointCountyPermit.Visible = True
LblWellPhysicalLocation.Visible = True
lblWellStCounty.Visible = True
Case "Mi" ' show / hide text boxes for Misculanous permits
lblDateNOSSubmitted.Visible = False
Dt_NOS_Sub.Visible = False
lblElectronicSubmissionNoFED.Visible = False
NOS_eSubNo.Visible = False
lblRoadDesignSubmitted.Visible = False
Dt_DsgnSub.Visible = False
lblRoadDesignSubmitted.Visible = False
Dt_DsgnSub.Visible = False
lblRoadDesignApproved.Visible = False
Dt_DsgnApv.Visible = False
' State Controls
lblDirHorizRequestSubmitted.Visible = False
Dt_DirHorz_Sub.Visible = True
lblLocExceptionSubmitted.Visible = False
Dt_LocXcpt_Sub.Visible = True
lblDirHorizRequestApproved.Visible = False
Dt_DirHorz_Apv.Visible = False
lblLocExceptionApproved.Visible = False
Dt_LocXcpt_Apv.Visible = False
' County Controls
lblCountyOfPermit_Label.Visible = True
cbointCountyPermit.Visible = True
LblWellPhysicalLocation.Visible = True
lblWellStCounty.Visible = True
Case "Tr" ' show / hide text boxes for (next set of rules)
' Note underconsidreation - awaiting input
Case Else
MsgBox "Temp Debug notice, the permit type did not match Fed, St, Co, Mi, Tr", vbOKCancel, "Short term Debug notice for development"
End Select
900 Else
910 MsgBox "A new Regulatory type has been added and needs to be addressed in Sub Activate ControlsByRegulatoryType", vbOKCancel, "Temp Debug notice"
1000 End If
End Sub
Denver - The non-METRIC city - After all - who ever heard of the 1.2 Km High Stadium! It is the "Mile High" city and Mile High Stadium. Likewise, when other athletes go to purchase an Athletic Cup, we prefer the full Gallon!
I would have gone with the loop and tag property. Something along the lines of this air-code:
Code:
Dim ctl As Control
For Each ctl In Me.Controls
If InStr(1, ctl.Tag, RegulatoryTypeCode) > 0 OR ctl.Tag = "All" Then
ctl.Visible = True
Else
ctl.Visible = False
End If
Next ctl
With the Tag property containing one or more codes, or All. I would also wonder whether your structure should include related tables instead of fields. I don't know your overall situation well enough to make the call, so I just throw that out there for consideration.
Thanks! I was looking for this code in my archives, but it was Friday afternoon and I needed to demo this for the customer.
For anyone considering this, the object code above is much more efficient.
But mainly, it is much easier for ongoing code maintenance!
Excellent Point! The related tables and fields are an important consideration.
In my case, I had just combined a dozen tables and normalized them into a few tables. Calling on a view in the case of SQL Server (or query in the case of Access).
In addition, a plan to wrap the entire process using variables and then use a Begin Transaction into an all-or-nothing - with some error trapping code for a "graceful exit". That will help prevent data collisions for multiple users.
Denver - The non-METRIC city - After all - who ever heard of the 1.2 Km High Stadium! 
It is the "Mile High" city and Mile High Stadium. Likewise, when other athletes go to purchase an Athletic Cup, we prefer the full Gallon!