Tag and Select Case for Sprinks

[Guest]

Hi Sprinks
I have main form"MICN" and subform"Requir" with several check boxes.
I tagged all check boxes in the subform. what I want to do if the user
choose the new record in the main form, then check boxes: CE30, Fee30,
Application .... in the subform "Requir" be visible. This is the code I used.
but it execute the first If statement only. IT doesn’t execute the select
case statement

Private Sub CertStatus_AfterUpdate()
Dim intCaseNumber As Integer
Dim ctl As Control
If Me.NewRecord Then
If Left(ctl.Tag, 1) = "V" Then
ctl.Vislible = True
Else
ctl.Visible = False
End If
End If
Select Case CertStatus
Case Is < 182 ' Case 1
intCaseNumber = 1
Case Is < 365 ' Case 2
intCaseNumber = 2
Case Is < 730 ' Case 3
intCaseNumber = 3
Case Is > 730 ' Case 4
intCaseNumber = 4
End Select
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If Mid(ctl.Tag, intCaseNumber, 2) = "V" Then
ctl.Visible = True
Else
ctl.Visible = False
End If
End If
Next ctl

Any help
Thanks
Amin

 

[Guest]

Hi, Amin.

Be sure to continue discussion on the original thread, otherwise, you stand
a good chance that your message will be missed. Fortunately, I signed on
just after you posted this one, and saw my nickname.

I think you would find if you inserted various MsgBox or Debug.Print
statements that the Select Case code is executing, however, it is not doing
anything to the checkbox controls because they are on a subform--the code
should be looping through the controls on the main form. To refer to the
controls collection of the subform, change:

For Each ctl In Me.Controls
to
For Each ctl In Me!Requir.Form.Controls

This creates an issue if your first control on the subform (in tab order) is
one of the checkboxes, because Access will not make invisible a control that
has the focus, which the first one does by default. So, set the focus to one
of the other non-checkbox controls on the subform right after the Select Case
statement:

Me!Requir.Form!YourNonCheckBoxControl.SetFocus

Also, you have a typo in your If clause. "Vislible" should be "Visible".

Hope that helps.

Sprinks

 

[Guest]

Thanks a lot Sprinks

Hi, Amin.

Be sure to continue discussion on the original thread, otherwise, you stand
a good chance that your message will be missed. Fortunately, I signed on
just after you posted this one, and saw my nickname.

I think you would find if you inserted various MsgBox or Debug.Print
statements that the Select Case code is executing, however, it is not doing
anything to the checkbox controls because they are on a subform--the code
should be looping through the controls on the main form. To refer to the
controls collection of the subform, change:

For Each ctl In Me.Controls
to
For Each ctl In Me!Requir.Form.Controls

This creates an issue if your first control on the subform (in tab order) is
one of the checkboxes, because Access will not make invisible a control that
has the focus, which the first one does by default. So, set the focus to one
of the other non-checkbox controls on the subform right after the Select Case
statement:

Me!Requir.Form!YourNonCheckBoxControl.SetFocus

Also, you have a typo in your If clause. "Vislible" should be "Visible".

Hope that helps.

Sprinks