How to detect all required fields of a form?

[Tom van Stiphout]

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

[storrboy]

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.

[Tom van Stiphout]

On Mon, 19 Mar 2007 18:26:32 -0700, Tom van Stiphout
<no*************@cox.netwrote:

I just realized the Autonumber portion of my question is no big deal:

blnRequiredField = rs.Fields(ctl.ControlSource).Required Or
(rs.Fields(ctl.ControlSource).Attributes And dbAutoIncrField)

Still struggling with the PK fields though.

-Tom.

>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

[Tom van Stiphout]

On 19 Mar 2007 18:50:25 -0700, "storrboy" <st******@sympatico.ca>
wrote:

I have considered such methods, but they require the developer to be
dilligent about fixing up the code (tags) if the database design
changes. My code avoids the possible discrepancies altogether.

Btw, rather than your format, I use the querystring format:
VARIABLE=VALUE&VARIABLE=VALUE&VARIABLE=VALUE
just because it's a commonly used format.

-Tom.

>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.

[lyle fairfield]

Tom van Stiphout <no*************@cox.netwrote in
news:rg********************************@4ax.com:

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?

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.

[Tom van Stiphout]

On Tue, 20 Mar 2007 02:04:07 GMT, lyle fairfield <ly******@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?

-Tom.

>Tom van Stiphout <no*************@cox.netwrote in
news:rg********************************@4ax.com :

>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?

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.

[storrboy]

On Mar 19, 9:12 pm, Tom van Stiphout <[e-mail]:

On Tue, 20 Mar 2007 02:04:07 GMT, lyle fairfield <[e-mail]>
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?

The field in the recordset you are using contains a SourceTable and
Source Field property. Probably could use those to look through
indexes.

[Tom van Stiphout]

On 19 Mar 2007 19:25:26 -0700, "storrboy" <st******@sympatico.ca>
wrote:

Sweet. That seems to do it!
Thanks,
-Tom.

>On Mar 19, 9:12 pm, Tom van Stiphout <[e-mail]:

>On Tue, 20 Mar 2007 02:04:07 GMT, lyle fairfield <[e-mail]>
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?

The field in the recordset you are using contains a SourceTable and
Source Field property. Probably could use those to look through
indexes.