Dynamic Field Visibility Control

  • Thread starter Thread starter Damien McBain
  • Start date Start date
D

Damien McBain

I am designing a "data entry" form (new records only) where the user will
first select a "type" from a combo box, then certain controls (mostly text
boxes) will become visible as a result of the AfterUpdate event of the combo
box.
The values in the combo box come from a table (tblTypes) and may change at
some time or other. I therefore don't want to hard code the various "types"
into the code which sets the controls' visible property ie:
If combo1.text = "blah1" then
Me!TextBox1.Visible = True
Me!TextBox2.Visible = True
If combo1.text = "blah2" then
Me!TextBox3.Visible = True
Me!TextBox4.Visible = True
etc
....because I might end up with a blah3 and blah4 at some stage.

Is there a way I can make this dynamic? Maybe I could create a yes/no field
in the "tblTypes" table for each control available on the data entry form
and set the value to yes if I want a certain control to be visible for that
"type", but then how would I use it to set the required properties?
 
You could have a table that contains the combination of "types" and fields
names, and shows whether or not the given field should be visible for that
specific type:

type ControlNm VisibilityFg
blah1 TextBox1 True
blah1 TextBox2 True
blah1 TextBox3 False
blah1 TextBox4 False
blah2 TextBox1 False
blah2 TextBox2 False
blah2 TextBox3 True
blah2 TextBox4 True

Then, once you know the specific type, return a recordset containing the
field names and visibility property for that type.

Do While rsCurr.EOF = False
Me.Controls(rsCurr!ControlNm).Visible = rsCurr!VisibilityFg
rsCurr.MoveNext
Loop
 
Damien,

You could use either DLookups or a recordset operation. The latter results
in neater code, in that you can "read" fieds in a loop and also use their
names to refer to the controls on the form, the only requirement being that
the fields are consecutive in the table (which should be no problem since
they do not already exist). Example code, assuming 5 controls and five
fields starting in the 4th field position in the table (so field index 3,
the first one being 0):

Dim db As DAO.Database
Dim rst As DAO Recordset
Dim strSQL As String, ctlname As String

Set db = CurrentDb()
strSQL = "SELECT * FROM tblTypes WHERE Type = '" & Me!Combo1 & "'"
'assuming your combo values are text
St rst = db.OpenRecordSet(strSQL)
For i = 3 to 7 'field indexes as above
ctlname = rst.Fields(i).Name
Me!Controls(ctlname).Visible = rst.Fileds(i)
Next
rst.Close
Set rst = Nothing
Set db = Nothing

Note: you will need to add a DAO reference if you don't alreay have one (DAO
3.51 for A97, DAO 3.6 for A2K or later).

HTH,
Nikos
 
Nikos said:
Damien,

You could use either DLookups or a recordset operation. The latter
results in neater code, in that you can "read" fieds in a loop and
also use their names to refer to the controls on the form, the only
requirement being that the fields are consecutive in the table (which
should be no problem since they do not already exist). Example code,
assuming 5 controls and five fields starting in the 4th field
position in the table (so field index 3, the first one being 0):

Dim db As DAO.Database
Dim rst As DAO Recordset
Dim strSQL As String, ctlname As String

Set db = CurrentDb()
strSQL = "SELECT * FROM tblTypes WHERE Type = '" & Me!Combo1 & "'"
'assuming your combo values are text
St rst = db.OpenRecordSet(strSQL)
For i = 3 to 7 'field indexes as above
ctlname = rst.Fields(i).Name
Me!Controls(ctlname).Visible = rst.Fileds(i)
Next
rst.Close
Set rst = Nothing
Set db = Nothing

Note: you will need to add a DAO reference if you don't alreay have
one (DAO
3.51 for A97, DAO 3.6 for A2K or later).

HTH,
Nikos
Click to expand...

Thanks guys I'll have a go. This is uncharted territory for me.

Damo
 
Back
Top