Trying to set a field as required....

[1BadBird]

ok guys I've been reading the boards here for a decent amount of time and have gotten a lot of good info. Let's see if you guys can help me out. I have a field set to be visible only if another field (combo box) is one of two values. Now I can get this to work, but I can't figure out the code that I need to use to make that field a required field only when it is visible. Is this possible or am I trying in vain?????????

TIA

 

[Lightning]

Setting the field's Visible setting to NO means that it cannot get the focus, and therefore does not need to have data entered. No Problem.

When the control is set to Visible = Yes (presumably by the After Update event of the Combo Box?) also set the focus to the control. Then in the control's Before Update event, test to see if it contains any data. If Yes, do nothing and proceed to the next control (or whatever you need to do). If No, pop up a message box to advise that the user must enter data in this field, and cancel the move to the next control (Cancel = true). This way the users won't be able to do anything until they enter data in the field.

Something like:

' Calls the MANDATORYFIELD function.
' Shows a messagebox advising that this field must contain data if
' the control field is set to YES.
If IsNull(AHDetails) Then
Call MandatoryField(Me.ActiveControl.Controls(0))
Cancel = True
AHDetails.SetFocus
End If

Public Function MandatoryField(ctl As Control)
Dim strField As Variant

strField = MsgBox(ctl.Caption & "@This field MUST have information entered into it.@" & _
"Enter the required information or press the [ESCAPE] key Twice to delete " & _
"all data on this screen and start again.", vbOKOnly + vbInformation, _
"Required Information Missing!!&quot
End Function


HTH
Lightning

 

[1BadBird]

this is what I have I am trying to make it so that once the licensing field is visible it can not be blank. I have it set so that when the value of a the combo box is Monitor then the monitor size combo box pops up. and when the value is Laptop or Computer then the licensing info box pops up. I want monitor size and licensing info to be required only when they are visible. If I am understanding your post (Lightning) correctly (which I am probably not) then the coding that you have would go at the end of mine??

If Me.TypeOfEquip1.Value = "Monitor" Then
Me.MonitorSize.Visible = True

Else
If Me.TypeOfEquip1.Value <> &quot;Monitor&quot; Then
Me.MonitorSize.Visible = False

End If
End If
If Me.TypeOfEquip1.Value = &quot;Computer&quot; _
Or Me.TypeOfEquip1.Value = &quot;Laptop&quot; _
Then
Me.LicensingInfo.Visible = True

Else
If Me.TypeOfEquip1.Value <> &quot;Computer&quot; _
Or Me.TypeOfEquip1.Value <> &quot;Laptop&quot; _
Then
Me.LicensingInfo.Visible = False


End If
End If

 

[Lightning]

Not Quite.

The MandatoryField Function Code should go into a separate module in your application. As it is a public function, it can then be called from any form that needs it.

The code that calls the MandatoryField function:
If IsNull(AHDetails) Then
Call MandatoryField(Me.ActiveControl.Controls(0))
Cancel = True
AHDetails.SetFocus
End If

should go into the On Exit event for the &quot;MonitorSize&quot; and &quot;LicencingInfo&quot; fields. When the user tries to exit from the field, the code checks whether the field is Null or not. If it is Null, it then calls the MandatoryField function and cancels the Exit event.

Also, have you considered a Select Case statement for your conditions? Try this code:

Select Case [TypeOfEquip]
    Case Is = "Monitor"
        Me.MonitorSize.Visible = True
        Me.LicencingInfo.Visible = False
    Case Is = "Laptop", "Computer"
        Me.MonitorSize.Visible = False
        Me.LicencingInfo.Visible = True
    Case Else
        Me.MonitorSize.Visible = False
        Me.LicencingInfo.Visible = False
End Select

instead of the multiple IF...Then statements

HTH
Lightning

 

[VicM]

TIA:

One other thought: If the field definition in the underlying table for your form's control is set to Required, or the Allow Zero Length is set to No, then it doesn't matter whether the control is visible or not. Access will demand data for it.

As taken directly from Access' help for 'Allow Zero Length':

Allow
Zero User's Value
Length Required action stored
No No Presses ENTER Null
Presses SPACEBAR Null
Enters a zero-
length string (not allowed)

Yes No Presses ENTER Null
Presses SPACEBAR Null
Enters a zero-
length string Zero-length string

No Yes Presses ENTER (not allowed)
Presses SPACEBAR (not allowed)
Enters a zero-
length string (not allowed)

Yes Yes Presses ENTER (not allowed)
Presses SPACEBAR Zero-length string
Enters a zero-
length string Zero-length string

You might want to take a look at the combinations.

Hope this helps,

Vic

 

[1BadBird]

Thanks a lot guys!!
I will try this today and see if it works. I like that select case option I was not aware of that.

Charles