Determine if control is Required (2000)

Topic

New Reply

I’m trying to find out how to programatically determine which fields on a form are Required. In a search of this forum I found a post http://www.wopr.com/cgi-bin/w3t/showflat.p…ew=&sb=&o=&vc=1 in which JAYDEN says:

“I like to use the ‘tag’ property of controls to indicate those controls that have ‘required’ data. I usually set the tag property for controls to 1 for required and 0 for optional (or sometimes leave it blank). During the beforeupdate event of the form, I just run code that loops through all controls, checks to see if the first character of the tag control is 1, if it is, it then checks to make sure that the field has a value. ”
________________________________________________________________

I would prefer not to have to remember to tag required fields that may be added to the form in the future if possible. Is there a way to loop through the fields, like:

For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
‘TEST FOR REQUIRED PROPERTY
End If
Next ctl
________________________________________________________________

Thanks,

Randy

Reply | Quote

Replies

Required is a property of a field in a table.

So you might open the Record Source of the form as a recordset and check if the Control Source of the control is required.

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
If rst.Fields(ctl.ControlSource).Required = True Then
‘ …
End If
End If
Next ctl

Reply | Quote

Thanks Hans – now I want to display the name of the control in a MsbBox. How would I access the name? Also, what would be a good source to learn all of the properties that I can access and use? For instance, I don’t see TypeOf anywhere in Help.

Thanks again!

Reply | Quote

I’d suggest getting familiar with the Object Browser. You can “drill down” to the object(s) you’re interested in and see the properties & methods for that object. Once there you can access the context-sensitive help to get the details on the syntax. The object browser also has a search function (handy if you know what you’re looking for).

Hope this helps.

Reply | Quote

(1) The name of a control is – surprise surprise – ctl.Name
(2) Tom already pointed you to the Object Browser as a great way to learn about the properties and methods of objects
(3) There should be something about TypeOf in the help on If…Then…Else. BTW, TypeOf is generic VBA, it’s not Access specific.
(4) An alternative to TypeOf is the ControlType property of a control. Its values are acTextBox, acLabel, acComboBox etc.

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
If rst.Fields(ctl.ControlSource).Required = True Then
MsgBox ctl.Name
End If
End Select
Next ctl

Reply | Quote