Forms, if statements and multiple fields to hide or show

[merika]

Hi all,

I´m struggling with getting the right VBA code for a form I have in my DB.

I have a form with over 40 fields on it. Depending on the number in one text field (called días) I need to show or hide a lot of other fields.

For instance, if the number in "Días" is 4, 33 fields need to be hidden. If the number is 8, about 25 fields need to be hidden.

I have no problem with a simple IF statement to make sure that one condition is met but as soon as I add another condition, it seems to get stuck (no errors... my form just don´t want to load any of the previous hidden fields).

I know there should be a way to do it, but as being new to VBA I can´t seem to figure out how to rightly use it so that it actually does what I want.

I know that the whole statements need to be on Form_OnCurrent and in the control source for the field that I´m using but still...

Anyone who can get me in the rigt direction?

 

[TheTerminator]

I did this once by putting a numeric value into the Tag property of the Controls. In your case, that numeric value would be either 4 or 8.
Then place a function in that form module.....

Private Function SetControls(n as Integer)
	For Each ctl in Me.Controls
		ctl.Visible = Val(Nz(ctl.Tag)) <> n
	Next
End Function

'When the value of the text box changes (in an appropriate 'event')...
ok = SetControls(Me.diaz.Value)
'or
ok = SetControls(Me.RecordSet![diaz])

Remember that the Tag property is a string value, hence the 'Val' function in the formula to convert it to numeric. Also the 'Nz' function prevents errors in the case of a blank Tag, as it would automatically calculate to zero.

 

[Pat Hartman]

I would suggest that you reexamine your table design. You might be better off with a sub-table for each type of record.

That said, an easy way to accomplish this is with tabs. Put sets of fields on different tabs and have your code set focus to the tab that contains the fields you want to show.

Controls can be placed on the form in which case they show though the tab pages or on an individual tab page in which case they are only visible when that tab page has the focus.

 

[merika]

Thanks Terminator for the suggestion, i´m going to "struggle" with that and think it might be getting me there where I want to be

 

[merika]

Hi Pat... Thanks for the suggestion of creating a sub-table but that will not solve my problem, I think.
I will look into it though... as you neverk now

 

[Pat Hartman]

Did you notice the tab solution I offered? It is not dependent on splitting the table.

 

[merika]

didn´t notice earlier.. thanks for pointing it out once again!