Hide controls based on field entry

[jiblankman]

I have a form which includes a field for a program description. When a program is chosen, I want to have certain controls available for entry. Programs have overlapping fields.

I have a table in my database that resembles the following (the fields a-d are check boxes):

Program # Field A Field B Field C Field D
P A B C D
1 X X X O
2 X X O X
3 O X X X
4 X X X X

When program 1 is selected, I want to have controls for A, B and C only visible to the user. D would remain null in the associated table.

Is there an easy way to implement this. In the actual database there are currently 11 programs and 12 fields. New programs/fields are added periodically, so I would like to make this as simple as possible so that I am not constantly rewriting code.

Thanks for any help.

 

[vbaInet]

On the On Current event of the form:

1. Check each field's value using an IF statement.
2. Set focus to a control that will ALWAYS be Enabled.
3. IF the value of the field linked to the checkbox is -1 or True then disable.
4. ELSE Enable.

 

[Mr. B]

If you have included in you database design a table to define the check boxes that allows you to define the check boxes that must be available for each program then you can simple query that table for the values related to each program and then write your code to only make the proper check boxes be visible.

You must have some type of information stored for each program that will indicate which controls should be displayed before you will have any way to do what you want with out having to modify your code each time you add a new program.

 

[jiblankman]

Thank you both for the quick responses. I am not sure I know how to do either of the things suggested.

The database is being written from scratch to take the place of older (read that as poorly written) databases, but I am learning as I go.

I have written a number of procedures in VBA that will hide or show the controls depending on the program selected (using the forms On Current event and the fields On Update event). The programs work correctly when changing the program code the first time, but now if I make subsequent changes. (I have tried calling the subroutine in different field events like lost focus, etc.) This still leaves me needing to write additional code for each new program or new category within the programs.

Would it help if I posted a sample db?

 

[Mr. B]

Did you understand what I was talking about storing the data needed to determine the controls that should or should not be displayed for one program?

If so, try to create your table and then you would simply need VBA code that would open a record set for the current program and read the requirements and then set the visible property of each control as defined in the table.

If you need to post your database, then that is fine, but this is the type of thing that you may need to start and just work through one step at a time.

 

[jiblankman]

Thanks, I will try using the recordset approach.