Clean up a form

[Guest]

Is there a method to initialize all controls in a form? That means move blanks to text control or zero to numeric control.

 

[Brian Kastel]

Perhaps this might help?

I wrote this code for an application at my last job. It is suitable for a
global module, so I called it when a form loaded. My routine examines the
bound controls on a form, setting the color of the attached labels to one
color for required fields, and another color for non-required fields.
Something similar to this is what you will probably have to do to
differentiate between text and numeric fields, but I could be wrong.

=== START OF CODE ==================

'---------------------------------------------------------------------------
--
' Sets the label color of a required bound control's label.
'---------------------------------------------------------------------------
--
Public Sub InitBoundControls(ByRef frm As Object)

Dim i As Long
Dim ctl As Control
Dim rs As Recordset
Dim ctlParent As Object

If Not IsNull(frm.RecordSource) Then

'Examine the underlying record source.
Set rs = CurrentDb.OpenRecordset(frm.RecordSource, dbOpenDynaset)

'Enumerate through the form's controls.
For Each ctl In frm.Controls
'Examine the form's label controls.
If ctl.ControlType = acLabel Then
'Determine if the parent's bound field is required.
If ctl.Parent.Name <> frm.Name Then
Set ctlParent = frm.Controls(ctl.Parent.Name)
Select Case ctlParent.ControlType
Case acTextBox, acComboBox, acListBox
If Not IsNull(ctlParent.ControlSource) Then
If Left$(ctlParent.ControlSource, 1) <> "=" Then
If
rs.Fields(ctlParent.ControlSource).Required Then
ctl.ForeColor = 128
Else
ctl.ForeColor = 8388608
End If
End If
End If
End Select
End If
End If
Next ctl


'Destroy the object variable.
Set rs = Nothing

End If

End Sub

=== END OF CODE ==================

Is there a method to initialize all controls in a form? That means move

blanks to text control or zero to numeric control.

 

[Guest]

Thansk Brian. When I went through your code, I found that the form is bounded to a recordset. But how about if my form does not have recordsets. I have a blank form just for input. after finished input, my prgram will perform validation and update. After update the recordsets will be closed. In order for the form be available for next input, I want to clear (blank out) all controls in ther form

Thanks for your code. It is beautiful. Actually you have given me some idea to use color for highlight if that control does not pass validation

 

[Brian Kastel]

Thanks for the compliment, Jerry.

If I understood your original question, you want to initialize some controls
to empty strings and others to numeric zeroes, depending upon their intended
future binding. If this is the case, then perhaps you could modify the code
to examine the *intended* record source first to determine the data types.
Then, if your form's text control names are identical to the recordset's
field names, you could enumerate through them that way.

The code would no longer be suitable for a global module, I don't think, but
you could just place the code into the form's module and it wouldn't be an
issue.

I just worked on this, and it seems to work fine:

=== START OF CODE =================

Private Sub InitControls()

Dim ctl As Control
Dim rs As Recordset
Dim fld As Field
Dim DefVal As Variant

'Examine the intended record source.
Set rs = CurrentDb.OpenRecordset("tblTest", dbOpenDynaset)

'Enumerate through the fields.
For Each fld In rs.Fields
'Use defaults specified in the table def.
If Not fld.DefaultValue = "" Then
DefVal = fld.DefaultValue
Else
'Set the default by the field's data type.
Select Case fld.Type
Case dbBoolean
DefVal = False
Case dbByte, dbCurrency, dbDecimal, dbDouble, _
dbFloat, dbInteger, dbLong, dbNumeric, _
dbSingle
DefVal = 0
Case dbDate, dbTime
DefVal = Now
Case dbMemo, dbText
'Deal with the zero-length attribute.
If fld.AllowZeroLength Then
DefVal = ""
Else
DefVal = Null
End If
End Select
End If
'Set the related control's value.
For Each ctl In Me.Controls
If ctl.Name = fld.Name Then
'Do not attempt to set autonumber field.
If (fld.Attributes And dbAutoIncrField) = 0 Then
ctl = DefVal
Exit For
End If
End If
Next ctl
Next fld

'Close the recordset.
rs.Close

'Destroy the object variable.
Set rs = Nothing

End Sub

=== END OF CODE =================

Thansk Brian. When I went through your code, I found that the form is

bounded to a recordset. But how about if my form does not have recordsets.
I have a blank form just for input. after finished input, my prgram will
perform validation and update. After update the recordsets will be closed.
In order for the form be available for next input, I want to clear (blank
out) all controls in ther form.

Thanks for your code. It is beautiful. Actually you have given me some

idea to use color for highlight if that control does not pass validation.

 

[Brian Kastel]

Correction:

The Exit For should come after the End If that follows it.

'Set the related control's value.
For Each ctl In Me.Controls
If ctl.Name = fld.Name Then
'Do not attempt to set autonumber field.
If (fld.Attributes And dbAutoIncrField) = 0 Then
ctl = DefVal
Exit For
End If
End If
Next ctl

as shown:

'Set the related control's value.
For Each ctl In Me.Controls
If ctl.Name = fld.Name Then
'Do not attempt to set autonumber field.
If (fld.Attributes And dbAutoIncrField) = 0 Then ctl =
DefVal
Exit For
End If
Next ctl