Unhide fields and make it "required" on basis on check box update

[sunilkeswani]

Hi

I got this code to work on basis of a text box afterupdate, but how do
I do a similar action for a check box, if checked, makes a particular
field visible and mandatory to fill?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.txt) Then
Select Case Me.txt2.Value
Case 115, 158, 160
Cancel = True
MsgBox "txt is required."
End Select
End If
End Sub


To make it visible, use the AfterUpdate of txt2. You also need to do
this in
the Current event of the form, so it is visible/hidden when the form
moves
record:
Private Sub txt2_AfterUpdate
Dim bShow As Boolean
Select Case Me.txt2.Value
Case 115, 158, 160
bShow = True
End Select
With Me.txt
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End Sub
Private Sub Form_Current()
Call txt2_AfterUpdate
End Sub


Above code was courtesy Allen, now I need some more help.

Cheers
Sunny

 

[BruceM]

In the form's Current event:
Me.txtHiddenBox.Visible = False

In the check box After Update event:
If Me.chkCheckbox = True Then
Me.txtHiddenBox.Visible = True
Me.txtHiddenBox.SetFocus
End If

Then you could use the Exit event of txtHiddenBox to make sure something was
entered.

 

[sunilkeswani]

Thanks Bruce, another one, how do i make any one of 4 check boxes
mandatory, only if the "comboname" combo has entries for a particular
list of names?

i.e I want speicific users to mandatorily select one of the 4 check
boxes, but not all.

Please advise

 

[BruceM]

If your combo box row source is based on a table, one possibility is to add
to the row source query a field that contains a value that is unique to
selected names. Change the combo box column count to reflect the added
column. If your combo box column count was 2, now it will be 3. In your
code you could check for the value in the hidden column:
If Me.cboComboBox.Column(2) = "TestValue" Then
'Add code as needed
End If

Note that the columns are numbered starting at 0, so 2 is the third column.

I'm not sure that is the best way, but it is what occurs to me if there are
more than two or three names. With a limited number of names you could do
something like:
If Me.cboComboBox = "Person One" Or Me.cboComboBox = "Person Two" Then
' Rest of code
End If

I'm not sure what to say about the check boxes. For one thing I am sure
there are better methods than what occurs to me. For another I am not quite
clear on what is supposed to happen. Are the check boxes visible at all
when the user is somebody other than one of the selected ones?

I would probably look into using an option group. If you prefer the look of
check boxes, I believe I have seen ways of simulating an option group with
check boxes, but I don't know how to do that. A Google groups search for
Access "check box" "option group" may yield some results, or you could try
posting a new thread here. In any case, I'm sure there are better ways than
what I can think of without doing further research.

 

[sunilkeswani]

Thanks, but I need the complete code, because i am not getting it

If Me.cboComboBox = "Person One" Or Me.cboComboBox = "Person Two" Then

' Rest of code (I want the code for Atleast 1 among check boxes
chck1,chck2,chck3,chck4 becomes mandatory)

End If


yes, for the other users, the boxes are hidden and for a few, they
unhide and atleast one will be required.

 

[BruceM]

I abbreviated because I wasn't sure what you needed to do.

I would probably place in front of the check boxes a rectangle (boxHide) the
same color as the background.
If Me.cboComboBox = "Person One" Or Me.cboComboBox = "Person Two" Then
Me.boxHide.Visible = False
Else
Me.boxHide.Visible = True
End If

This code would be in both the combo box After Update event and the form's
Current event. You could also put the code into a general procedure, and
call it from other events.

Then, in the form's Before Update event, you could have some validation
code:

If Me.chkCheckOne = False And Me.chkCheckTwo = False And _
Me.chkCheckThree = False And Me.chkCheckFour = False Then
msgbox "You must select at least one check box"
Cancel=True
End If

The underscore is for line wrapping in the code window. It is not required.
I have not tested the code.

Again, the combo box code could get clumsy if there are more than a few
names, which is why I suggested in an earlier post the approach that uses a
hidden column. Also, I think there are ways of testing a group of controls
such as check boxes by means other than naming each one in the code. For
four check boxes it works well enough to name all of the check boxes, but
that too could become clumsy in some cases.

 

[sunilkeswani]

This works great !!! Thanks for your hlp Bruce and sorry for
cross-posting

 

[BruceM]

Glad it helped. Good luck with the project.