Recursive function to look for not updated controls on any form 1

[Aivars]

Hi, everybody!

Many times Tep-Tips forum's members ask how to be satisfied that all controls on form are updated. Here's function which checks for any control with Null value on anyone opened form including all subforms. Function returns True if such control was found otherwise False.

Simple copy following code into any module and call function when it's needed.

'*********************************
Public Function IsNotUpdatedControl(Optional frm As Form, _
Optional OnlyRecUpdCtls As Boolean = False) As Boolean
'-------------------------------
'Designed by Aivars Laganovskis
'August 20,2001
'-------------------------------
'Function seeks any control with Null value on given (Me if frm is omitted) form
'including all subforms recursively.
'If found, it returns True otherwise False.
'
'Optional parameter "OnlyRecUpdCtls" >>> (Only Record[fields] Updated controls):
' If it's True then only controls with ControlSource
' to recordset field are to be verified.
' Else all form's controls are verified.
'
On Error GoTo Err_IsNotUpdatedControl
Dim ctl As Control
Dim bln As Boolean
Dim rst As Recordset
Dim fld As Field
Dim prp As Property

If frm Is Nothing Then
'Function was called without first parameter
Set frm = Screen.ActiveForm
End If

If OnlyRecUpdCtls Then
'Must be verified only for controls with
'associated recordset fields
If frm.RecordSource = "" Then
'Form have not RecordSet
GoTo Exit_IsNotUpdatedControl
End If
Set rst = frm.RecordsetClone
If rst.EOF Then
'Empty recordset
GoTo Exit_IsNotUpdatedControl
End If
End If

For Each ctl In frm.Controls
If ctl.Properties("ControlType&quot = acSubform Then
'Recursive call: finds null values for controls on subform
bln = IsNotUpdatedControl(ctl.Form, OnlyRecUpdCtls)
Else
For Each prp In ctl.Properties
'Check for property
'Only updatable controls are verified
'ie. controls with property "ControlSource"
If prp.Name = "ControlSource" Then
If OnlyRecUpdCtls Then
'Compare field names of form's recordset
'with control's ControlSource
For Each fld In rst.Fields
If fld.Name = ctl.ControlSource Then
'Only if form's recordset field is updated
'via control, its value is verified
bln = IsNull(ctl)
Exit For
End If
Next fld
Else
bln = IsNull(ctl)
End If
Exit For
End If
Next prp
End If
If bln Then 'Control has Null value
Exit For
End If
Next ctl
IsNotUpdatedControl = bln

Exit_IsNotUpdatedControl:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Exit Function

Err_IsNotUpdatedControl:
MsgBox "Error No " & Err.Number & vbLf & Err.Description, , "Public Function IsNotUpdatedControl"
Resume Exit_IsNotUpdatedControl

End Function
'*********************************

Examples:
1. Verified for all updatables active form's controls
Private Sub Form_UnLoad(Cancel As Integer)
If IsNotUpdatedControl(Me) = True then
Msgbox "You may update all controls on form!"
Cancel = True
End If
End sub

2. Verified only for controls with associated recordset fields
Private Sub Form_UnLoad(Cancel As Integer)
If IsNotUpdatedControl(Me, True) = True then
Msgbox "You may update all controls on form!"
Cancel = True
End If
End Sub

3. On active form:
MyValue=IsNotUpdatedControl()
MyValue=IsNotUpdatedControl(, True)
MyValue=IsNotUpdatedControl(Me)
MyValue=IsNotUpdatedControl(Me, True)

4. On other form:
MyValue=IsNotUpdatedControl(Forms("MyForm&quot)
MyValue=IsNotUpdatedControl(Forms!MyForm, True)

Set frm = Forms!MyForm
MyValue=IsNotUpdatedControl(frm)
MyValue=IsNotUpdatedControl(frm, True)

Good luck!
Aivars |-0
Riga,
Latvia
alaganovskis@hotmail.com

 

[MichaelRed]

Yes, but I want the stars and the moon thrown in at no additional cost!

It may be "Nice" to know that SOMETHING returns "Null", but I would want to know WHAT (e.g. the ControlName and probably the Top and Left, and certainly the (if any) subform name.

Now, while you're at this, please also check for controls which have an empty string (as opposed to NULL).

This pretty well cover the Moon part, I'll get back to you on the Stars in a bit.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Aivars]

Hi, Michael!

I'm not sure that is necessary the found control names to show because this function was planned for checking how to user update form to inform his if he forgot anything.

Control names and form names don't useful for user because its are different controls labels.

There you are function's version with possibility to show control names list:

'***************************
Public Function IsNotUpdatedControl(Optional frm As Form, _
Optional OnlyRecUpdCtls As Boolean = False, _
Optional WithMessage As Boolean = False, _
Optional strMSG As String = "&quot As Boolean
'-------------------------------
'Designed by Aivars Laganovskis
'August 20,2001
'-------------------------------
'Function find any control with Null value on form
'include all form's subforms and all subforms of subforms.
'If found return True in opposite return False.
'
'Optional parameter "OnlyRecUpdCtls" >>> (Only Record[fields] Updated controls):
' If it's True then only controls with ControlSource
' to recordset field are verified.
' Else all form's controls are verified.
'
'If WithMessage = True, message box will appear
'
'strMSG >>>> message text string
'
On Error GoTo Err_IsNotUpdatedControl
Dim ctl As Control
Dim bln As Boolean
Dim rst As Recordset
Dim fld As Field
Dim prp As Property

If frm Is Nothing Then
'Function was called without first parameter
Set frm = Screen.ActiveForm
End If

If OnlyRecUpdCtls Then
'Must be verified only controls which
'update recordset fields
If frm.RecordSource = "" Then
'Form have not RecordSet
GoTo Exit_IsNotUpdatedControl
End If
Set rst = frm.RecordsetClone
If rst.EOF Then
'Empty recordset
GoTo Exit_IsNotUpdatedControl
End If
End If

For Each ctl In frm.Controls
If ctl.Properties("ControlType&quot = acSubform Then
'Recursive call: find null values controls on subform
bln = IsNotUpdatedControl(ctl.Form, OnlyRecUpdCtls, , strMSG)
Else
For Each prp In ctl.Properties
'Check for property
'Only updatable controls are verified
'ie. controls with property "ControlSource"
If prp.Name = "ControlSource" Then
If OnlyRecUpdCtls Then
'Compare field names of form's recordset
'with control's ControlSource
For Each fld In rst.Fields
If fld.Name = ctl.ControlSource Then
'Only if form's recordset field is updated
'via control, it value is verified
bln = IIf(Not bln, IsNull(ctl), bln)
If IsNull(ctl) Then
'Adding control name and form name to message text
If strMSG <> &quot;&quot; Then
strMSG = strMSG & vbLf
End If
strMSG = strMSG & ctl.Name & &quot; (&quot; & frm.Name & &quot&quot;
End If
Exit For
End If
Next fld
Else
bln = IIf(Not bln, IsNull(ctl), bln)
If IsNull(ctl) Then
'Adding control name and form name to message text
If strMSG <> &quot;&quot; Then
strMSG = strMSG & vbLf
End If
strMSG = strMSG & ctl.Name & &quot; (&quot; & frm.Name & &quot&quot;
End If
End If
Exit For
End If
Next prp
End If
Next ctl
IsNotUpdatedControl = bln
'If message is required
'and non update controls was found
'display message box with list
If WithMessage And bln Then
MsgBox &quot;You was not update following controls:&quot; & vbLf & vbLf & strMSG
End If

Exit_IsNotUpdatedControl:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Exit Function

Err_IsNotUpdatedControl:
MsgBox &quot;Error No &quot; & Err.Number & vbLf & Err.Description, , &quot;Public Function IsNotUpdatedControl&quot;
Resume Exit_IsNotUpdatedControl

End Function
'***************************

With message:
MyValue=IsNotUpdatedControl(me, , True)

Best regards!
Aivars

 

[MichaelRed]

Aviars,

It CAN be user useful, Of course it would need to be able to provide some feed back (like a messagebox) to inform them of the omission. I would PROBABLY follow up with a SET Focus to the offending Control(s), so perhaps the postions info is NOT really necessary. This could be a good tool for guiding user to the problem area (Control?) It could be extended to reference a record source to see if there is a Caption for the field for use in telling 'User' what he has not filled in. Also, when setting the focus, the control couls be set up to have the field &quot;selected&quot; nad it would show up with the highlight color. Then, MAKE it do the BLINKY Dance. Bot even the real DUNCES could miss that?

Really, I like the routine. I will probably borrow it and do some building on the foundation. In a LARGE app, you could even set up a seperate Table to denote which fields were REALLY required (even on a form-by-form basis) and only SELECTIVELY flag the omissions.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Aivars]

Michael, thank you for advice!

Do you know how to extract saved (embedded) pictures from Acc DB to disk image files (JPEG). Its was inserted by using Access dialog for inserting OLE object. I have solution how to do it by <SendKeys>. Maybe you know other way? GetChunk extracts long bin data... Its are unusable...

Aivars

 

[MichaelRed]

Sorry, Haven't done that trick. Can't look into it right now, as I need to do some other things (LOOK for &quot;GAINFUl&quot; employment?)

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over