Enforcing mandatory fields

[Richard John]

I am trying to enforce some form rules in MS access 2003. The approach that works at present is the
"hard-coded" one like this:

......

If (IsNull([Placement date])) Then
lblAlertPlacementDate.Visible = True
alerts = vbTrue
End If

If (IsNull([SiblingGroup])) Then
lblAlertSibgroup.Visible = True
alerts = vbTrue
End If

..... blah..

There are some 20 such rules to enforce. So I am trying an automated way using ADO (or is it DAO ..
could never get that right .. ) where a list of enfordeable fields is read from a table. The
result is this code:

' Called from OnClose event of the 'Reception Intake form'
' Mandatory fields are checked against fields marked as 'mandatory'
' in "tblMandatoryFields"

Set db = CurrentDb
SQL = "select Fieldname,AlertIcon from tblMandatoryFields where flag=" & vbTrue
Set rsMand = db.OpenRecordset(SQL, dbOpenDynaset)

Do Until rsMand.EOF
sAlertIcon = rsMand("AlertIcon")
sFieldname = rsMand("Fieldname")
If IsNull(sFieldname) Or Len(Nz(sFieldname, "")) = 0 Then
rsMand("AlertIcon").Visible = True
alerts = vbTrue
End If
rsMand.MoveNext
Loop

If the field has not been completed, a red alert box (rsMand("AlertIcon")) appears adjacent to the
field.

Can't get this to work. It is not accepting this line of code

*** rsMand("AlertIcon").Visible ***

Any ideas please? This approach is better for my purposes where it gives the manager the ability to
specify which fields to enforce from a form provided for just this purpose.

Thanks

 

[Allan Murphy]

Richard

Have a look at this site http://www.databasedev.co.uk/validate_textbox.html

It uses the Tag Property of the field to ensure that the mandatory fields
are entered.

Allan

 

[bcap]

rsMand("AlertIcon") is a field in a recordset, it's not a control on a form.
It doesn't have any user interface and so you can't make it "visible".

Presumably this is what you intended:

Me.Controls(sAlertIcon).Visible = True

Also, I'm sure this code should be invoked from the BeforeUpdate event of
the form, not the OnClose event. There are many ways of saving a record in
Access, closing the form is only one of them.

 

[bcap]

Oh, and you are all over the place with the fieldname too. You are failing
to make the distinction between fields in the recordset and controls/fields
on the form. Overall, your code should probably look something like this:

Do Until rsMand.EOF
sAlertIcon = rsMand("AlertIcon")
sFieldname = rsMand("Fieldname")
If Nz(Me(sFieldname),"") = "" Then
Me.Controls(sAlertIcon).Visible = True
alerts = vbTrue
End If
rsMand.MoveNext
Loop

rsMand("AlertIcon") is a field in a recordset, it's not a control on a
form. It doesn't have any user interface and so you can't make it
"visible".

Presumably this is what you intended:

Me.Controls(sAlertIcon).Visible = True

Also, I'm sure this code should be invoked from the BeforeUpdate event of
the form, not the OnClose event. There are many ways of saving a record
in Access, closing the form is only one of them.

I am trying to enforce some form rules in MS access 2003. The approach
that works at present is the "hard-coded" one like this:

.....

If (IsNull([Placement date])) Then
lblAlertPlacementDate.Visible = True
alerts = vbTrue
End If

If (IsNull([SiblingGroup])) Then
lblAlertSibgroup.Visible = True
alerts = vbTrue
End If

.... blah..

There are some 20 such rules to enforce. So I am trying an automated way
using ADO (or is it DAO .. could never get that right .. ) where a
list of enfordeable fields is read from a table. The result is this code:

' Called from OnClose event of the 'Reception Intake form'
' Mandatory fields are checked against fields marked as 'mandatory'
' in "tblMandatoryFields"

Set db = CurrentDb
SQL = "select Fieldname,AlertIcon from tblMandatoryFields where
flag=" & vbTrue
Set rsMand = db.OpenRecordset(SQL, dbOpenDynaset)

Do Until rsMand.EOF
sAlertIcon = rsMand("AlertIcon")
sFieldname = rsMand("Fieldname")
If IsNull(sFieldname) Or Len(Nz(sFieldname, "")) = 0 Then
rsMand("AlertIcon").Visible = True
alerts = vbTrue
End If
rsMand.MoveNext
Loop

If the field has not been completed, a red alert box
(rsMand("AlertIcon")) appears adjacent to the field.

Can't get this to work. It is not accepting this line of code

*** rsMand("AlertIcon").Visible ***

Any ideas please? This approach is better for my purposes where it gives
the manager the ability to specify which fields to enforce from a form
provided for just this purpose.

Thanks

 

[Richard John]

Thanks for this solution. IT WORKED !!!!

Oh, and you are all over the place with the fieldname too. You are failing
to make the distinction between fields in the recordset and controls/fields
on the form.

I actually was fully aware of that, but just went up against a wall, looking for a work around,
which is why I asked. Thanks again..

RJ


Overall, your code should probably look something like this:

Do Until rsMand.EOF
sAlertIcon = rsMand("AlertIcon")
sFieldname = rsMand("Fieldname")
If Nz(Me(sFieldname),"") = "" Then
Me.Controls(sAlertIcon).Visible = True
alerts = vbTrue
End If
rsMand.MoveNext
Loop

rsMand("AlertIcon") is a field in a recordset, it's not a control on a
form. It doesn't have any user interface and so you can't make it
"visible".

Presumably this is what you intended:

Me.Controls(sAlertIcon).Visible = True

Also, I'm sure this code should be invoked from the BeforeUpdate event of
the form, not the OnClose event. There are many ways of saving a record
in Access, closing the form is only one of them.

I am trying to enforce some form rules in MS access 2003. The approach
that works at present is the "hard-coded" one like this:

.....

If (IsNull([Placement date])) Then
lblAlertPlacementDate.Visible = True
alerts = vbTrue
End If

If (IsNull([SiblingGroup])) Then
lblAlertSibgroup.Visible = True
alerts = vbTrue
End If

.... blah..

There are some 20 such rules to enforce. So I am trying an automated way
using ADO (or is it DAO .. could never get that right .. ) where a
list of enfordeable fields is read from a table. The result is this code:

' Called from OnClose event of the 'Reception Intake form'
' Mandatory fields are checked against fields marked as 'mandatory'
' in "tblMandatoryFields"

Set db = CurrentDb
SQL = "select Fieldname,AlertIcon from tblMandatoryFields where
flag=" & vbTrue
Set rsMand = db.OpenRecordset(SQL, dbOpenDynaset)

Do Until rsMand.EOF
sAlertIcon = rsMand("AlertIcon")
sFieldname = rsMand("Fieldname")
If IsNull(sFieldname) Or Len(Nz(sFieldname, "")) = 0 Then
rsMand("AlertIcon").Visible = True
alerts = vbTrue
End If
rsMand.MoveNext
Loop

If the field has not been completed, a red alert box
(rsMand("AlertIcon")) appears adjacent to the field.

Can't get this to work. It is not accepting this line of code

*** rsMand("AlertIcon").Visible ***

Any ideas please? This approach is better for my purposes where it gives
the manager the ability to specify which fields to enforce from a form
provided for just this purpose.

Thanks