Base Control Visibility on Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that lists sales goals for multiple employees, and it's called
"Goals". Some employees therein are commercial bankers, some are retail
managers, and others are assistant retail managers. I'd like to build a form
for data entry of the sales goals. I want a list box on that form for the
employee types (commercial banker, retail manager, assistant retail manager).
When a user selects an employee type, I want to show/hide fields on the form
based on the values in a second table, "jobTitles". The jobTitles table lists
only the employee types, and has fields identical to the fields in the table
Goals. However, the values in these fields are all true/false, yes/no, or
0/1. These values will indicate whether a field should be visible on the data
entry form.

Am I making sense, and can this be done? If so, how? Pseudocode would be
appreciated.

Many thanks,
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor
 
Gwen,

One simple way is to use DLookup to lookup the boolean values. In the
AfterUpdate event of the list or combo box:

Me![MyControl].Visible =
DLookup("[ControlBooleanValue]", "JobTitles", "[JobType] = " & _
Me![YourListBox]")

Since you will want the proper controls to show for existing records, the
code should be in the On Current event as well. Rather than have the same
redundant code in two places, write a generic procedure, say, ShowControls,
and then call it in both events:

Call ShowControls

If there is a fairly small number of employee types (less than 26), however,
I'd personally prefer another approach, that avoids multiple calls to Dlookup
and is likely faster. If there are less than 26 types, you can use a letter
for each type. Let's say you have the following codes:

A Commercial Banker
B Retail Manager
C Assistant Retail Manager

Let's say a given control should be visible for commercial bankers and
assistant retail managers. You could then enter a Tag property of AC (and
the letters of any other jobtypes that need the control visible).

The code would then read as follows. I've assumed the controls to be all
textboxes and that the *value* of your listbox (the bound column) is the code
of interest.

Dim ctl as Control
For Each ctl in Me.Controls
If ctl.ControlType = acTextbox Then
If Nz(Instr(ctl.Tag, Me![YourListBox])) <> 0 Then ' found code in
Tag string
ctl.Visible = True
Else
ctl.Visible = False
End If
End If

The first method is more "visible" to the programmer because it puts the key
values in a table, however, I believe the latter would be much faster. If
the speed of the former meets your needs, you've the best of both.

Hope that helps.
Sprinks
 
Back
Top