How to detect all required fields of a form?

>I want to indicate requiredness by setting the background color of the
>control. Ideally I would call a sub in Form_Load to just do its thing.
>
>Below is what I have so far, but I realize this is of limited use
>because both Autonumber fields as well as Primary Key fields not
>necessarily have their Required property set, while they are required
>for the purpose of the Insert action.
>
>It seems to find out if a field is an Autonumber or PK field can be
>complicated.
>Does anyone have a brilliant solution?
>
>-Tom.
>Code follows:
>
>In the Form_Load event of a form:
>SetRequiredFields Me
>
>
>Public Sub SetRequiredFields(frm As Form)
Dim ctl As Control
Dim rs As DAO.Recordset
>
Set rs = frm.RecordsetClone
>
For Each ctl In frm.Controls
If HasProperty(ctl, "ControlSource") Then
if rs.Fields(ctl.ControlSource).Required then
>ctl.BackColor = vbRed
End If
Next ctl
Set ctl = Nothing
Set rs = Nothing
>End Sub
>
>
>Private Function HasProperty(ctl As Control, ByVal strPropName As
>String)
Dim prop As Property
>
On Error Resume Next
Set prop = ctl.Properties(strPropName)
HasProperty = (Err.Number = 0)
Set prop = Nothing
>End Function
>

>Hi Tom,
>
>I never thought of doing it that way.
>
>The method I use is a little different, but does address the 'not
>required pk' (Autonumbers don't count-they fill themselves in) as well
>as enforcing other form controls to be supplied whether or not the
>table requires them. I use the tag property of the control to contain
>specifically formated string to store any info I need about the
>control. The format looks like
>VARIABLE=VALUE;VARIABLE=VALUE;VARIABLE=VALUE (caps not required). I've
>created one function that finds a variable and returns the value, and
>another that modifies the value or adds if not found. Set one of the
>variables to REQUIRED=TRUE and before updating the record(s) cycle
>through all controls and see if the ones marked required are filled
>in. Any required control not containing a value is added to a string
>and the user is prompted for all the ones they missed.
>
>Yours warns them before hand which is nice, but I found this way to be
>a little more flexible.

I want to indicate requiredness by setting the background color of the
control. Ideally I would call a sub in Form_Load to just do its thing.
>
Below is what I have so far, but I realize this is of limited use
because both Autonumber fields as well as Primary Key fields not
necessarily have their Required property set, while they are required
for the purpose of the Insert action.
>
It seems to find out if a field is an Autonumber or PK field can be
complicated.
Does anyone have a brilliant solution?

>Tom van Stiphout <no.spam.tom7744@cox.netwrote in
>news:rgduv2pmi2mk2rfihp8j58mkn5r33emasm@4ax.com :
>>
>If you are using Access >=2000 and if you are talking single column
>primary keys of the underlying tables these might help:
>
>Public Function PrimaryKey$(ByVal TableName$)
>PrimaryKey = CurrentProject.Connection.OpenSchema( _
>adSchemaPrimaryKeys, Array(Empty, Empty, TableName)) _
>.Collect("COLUMN_NAME")
>End Function
>
>Public Function IsPrimaryKey(ByVal ColumnName$, ByVal TableName$) As
>Boolean
>IsPrimaryKey = CurrentProject.Connection.OpenSchema( _
>adSchemaPrimaryKeys, Array(Empty, Empty, TableName)) _
>.Collect("COLUMN_NAME") = ColumnName
>End Function
>
>Public Sub Test()
>'northwinds
>Debug.Print PrimaryKey("Customers") 'CustomerID
>Debug.Print IsPrimaryKey("CustomerID", "Customers") 'True
>Debug.Print IsPrimaryKey("CustomerName", "Customers") 'False
>End Sub
>
>Then again I do so little in Access these days that I may be too out of
>touch to understand your question.
>
>I guess we could modify these for miltiple column primary keys if needed.

On Tue, 20 Mar 2007 02:04:07 GMT, lyle fairfield <lylef...@yahoo.ca>
wrote:
>
Hi Lyle,
Thanks for responding.
I considered such code (perhaps I would use the Indexes collection
rather than OpenSchema), but it assumes that I know the tablename a
control is bound to. What if this is a form with a RecordSource that
is a complex query, and possbly several queries/tables in this query
have a CustomerID field?

>On Mar 19, 9:12 pm, Tom van Stiphout <no.spam.tom7...@cox.netwrote:>
>
>The field in the recordset you are using contains a SourceTable and
>Source Field property. Probably could use those to look through
>indexes.