How to indicate a field is required

[PhilOfWalton]

I have a continuous subform, listing the fields in a table. I want to indicate whether the field is required or not.

So I need a visual indication on the subform itself without actually trying to enter data. Here is a mock-up

Stuck on this one. Have messed around with the IsRequired property, but no joy

Thanks,

Phil

[twinnyfo]

Hmmmmmmmm.....

I was just thinking to myself, "Self (I always call myself, 'Self'), this oughta be really simple!"

But, then I got to thinking, Phil wants to change a property of a Control based upon a property of a Field in a Table--for each Record in that Table.

I think the rub is that Field properties are not available at the Control level. If it was a Single Form, you could do it with VBA, but that doesn't work so nicely with Continuous Forms.

Let me chew on this one a bit longer....

[twinnyfo]

By the way, how do you determine if a field is required or not? That might be a better starting place. If I am thinking Table level, if a field is required, the field is required in the table--but I know that is not what you are asking.

[PhilOfWalton]

Ah, Twinnyfo, if it was simple, I hope I wouldn't be asking. I am just trying to keep the little grey cells working.

No I don't want to change any properties of the control, just the format.

Yes, the Field is determined in the table design along with "Allow Zero length" & "Indexed" etc.

I have tried

Private Sub CboTables_AfterUpdate()
 
    Dim objFC As FormatCondition
 
    Me!PFrmSubFields.Form.InputField.FormatConditions.Delete
    Set objFC = Me!PFrmSubFields.Form.InputField.FormatConditions.Add(acExpression, , _
        "Currentdb.TableDefs(CurrentDb.TableDefs(Me!PFrmSubFields.Form![TableName]).Name).Fields(Me!PFrmSubFields.Form!InputField).Properties(15) = True")
    Me!PFrmSubFields.Form.InputField.FormatConditions(0).ForeColor = vbRed
 
End Sub

which runs without error, but doesn't change the colour of the field.

It may help to refer to https://bytes.com/topic/access/answe...lised-database

To explain the code, this runs when I update "CboTables" next to "Please Select Tables at Level: 3:"

That selects the "TableName"

The subform is called "PFrmSubFields"

The Control under the pale blue Field Name column is "InputField"

Phil

[twinnyfo]

Phil,

Could it be this:

Me!PFrmSubFields.Form.InputField

might should be:

Me!PFrmSubFields.Form.txtInputField

The former is ambiguously referring to either the underlying Field OR a Text Box Control; the latter is explicitly referring to a Text Box Control.

[NeoPa]

I guess you only have the property of the Field to work from in your explanation of the scenario. I'm not aware of any way of handling this using Conditional Formatting without setting up a function procedure to return the value.

On the other hand, is this something that could be handled at the time of loading or opening? Code in one of those event procedures could determine the field and its properties from the control.

It would help to have a clear idea of exactly what it is you're after. Bear in mind that items on a form, though they may be associated with fields, are controls. It is these that somehow need to be set as required. The fields already are, or not.

[PhilOfWalton]

@Twinnyfo

Won't even compile so no luck there I'm afraid

Neopa

The mock up in post no 1 is exactly what I a after.

Here is the table structure:=-

This is an image of the code running:-

When I halt the code on the line beginning "Set", in the Debug Window, I correctly get 0 (Black ForeColor)

Move on to the end and I get 255 Red which is what is wanted.

Trouble is the Field Names don't change colour

Phil

[NeoPa]

So, other than the result you don't care about any other details. Got it. In that case both suggestions I included in my previous post should work for you. Have you considered which, if any, seems the more appropriate? I'd be looking at setting up the Controls within the Load or Open event procedures personally.

I would just point out that colouring the text of Controls, which I expect will start off empty, may not be a very reliable way to indicate that a value's required. Typically I'd expect that the associated Label field would make more sense to use. That's just an idea of course.

[PhilOfWalton]

Yes the label option is a good one, I'll go with that, but bearing in mind that we are talking continuous forms, we will still need Conditional Formatting?????

Phil

[PhilOfWalton]

Cracked it

Created a Function

Public Function RequiredField(TableName As String, FieldName As String) As Boolean
 
    Dim MyDb As Database
    Set MyDb = CurrentDb
 
    On Error Resume Next
 
    RequiredField = MyDb.TableDefs(TableName).Fields(FieldName).Properties("Required")
 
End Function

The RecordSource of the subform now has an additional field "IsRequired" set to the above function.

I then do standard conditional formatting depending on whether "IsRequired" is true or false.

Phil

[NeoPa]

Hi Phil.

Does it make sense to you to use ConditionalFormatting?

Surely the Controls that are for required Fields don't change between records. Hence the suggestion to handle it more simply, and once only, when the Form is opened or loaded. That seems a far simpler and more straightforward approach to me.

[PhilOfWalton]

I see what you are getting at and have tried

Private Sub Form_Load()
 
    Dim MyDb As Database
    Set MyDb = CurrentDb
 
    On Error Resume Next
 
    If MyDb.TableDefs(TableName).Fields(FieldName).Properties("Required") = True Then
        InputField.BackColor = vbRed
    Else
        InputField.BackColor = vbYellow
    End If
 
End Sub

All the fields are yellow.

The routine just seems to "Look" at the first control on the subform

Thanks

Phil

[NeoPa]

Phil of Walton:
The routine just seems to "Look" at the first control on the subform

That sort of makes sense really. I see the term FieldName in your code has no visible definition. I'm guessing that you don't have Require Variable Declaration set and that FieldName is therefore automagically declared for you as a Variant with nothing in it. That roughly translates the Fields(FieldName) reference to Fields(0) which would indeed be the first field.

What you need is to loop through all the controls while ignoring any that are not of the types you're using. That would certainly include TextBoxes, and probably ComboBoxes and CheckBoxes, but not sure what else. This would further be restricted to exclude any such control without an associated Label.

Something like :

Private Sub Form_Load()
    Dim lngVar As Long
    Dim strField As String
    Dim ctlLbl As Control
 
    For Each ctlLbl In Me.Detail.Controls
        With ctlLbl
            If .ControlType = acLabel Then
                lngVar = 0
                On Error Resume Next
                With .Parent
                    lngVar = .ControlType
                    strField = .ControlSource
                End With
                On Error GoTo 0
                If strField > "" _
                And (lngType = acCheckBox _
                Or lngType = acTextBox _
                Or lngType = acComboBox) Then
                    'We have a valid Control pair.
                    lngColour = IIf(Me.Recordset.Fields(strField).Required _
                                  , vbRed, vbYellow)
                    .BackColor = lngColour
                    .Parent.BackColor = lngColour
                End If
            End If
        End With
    Next ctlLbl
End Sub

In the object structure any associated Label has the associated Control as its .Parent. The associated Control has a .Controls() collection that contains the Label as its only entry.