Assign a blank date value

[Guest]

I am validating a line, and have various code to check required fields (we
don't block user from leaving), and in one particular field, the BA Signoff
(meaning the line is complete), I need to blank out the date if one exists.
I
f they are entering a date, and the line is incomplete (I have this piece),
then I need to blank out the date. I have tried various combinations of the
code below.

How do I assign a 'blank' value to a date field, clear it?

My code:
Private Sub BA_Signoff_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strEmpty As String
Dim strMsg As String
Dim blankDate As Variant

blankDate = Null

For Each ctl In Me.Controls

If ctl.Tag = "check" Then
If IsNull(ctl) Then
strEmpty = strEmpty & vbCrLf & ctl.Name
End If
End If
Next ctl

If strEmpty <> "" Then
strMsg = "These fields are missing a value: " & vbCrLf & vbCrLf & _
Mid(strEmpty, 3) & vbCrLf & vbCrLf _
& "All required fields must be filled before BA Line Item
Signoff." & vbCrLf & vbCrLf _
& "Please complete line and signoff again."
MsgBox strMsg, vbCritical + vbOKOnly, "Issue Line Incomplete!"
Me.IssueStatus = "I" 'Incomplete
Cancel = True
Me.BA_Signoff.Text = blankDate

End If

End Sub

 

[Brendan Reynolds]

You need to assign Null to the Value property of the control rather than the
Text property.

 

[Shane S via AccessMonster.com]

Hey Michael,

Have ya tried:

blankDate = ""

HTH,
Shane

 

[Guest]

..Value = Null worked.
But, the form got confused with the code in the BeforeUpdate, and if I do
remove the signature, a Cancel = True, will put it back in again.

I moved the code to the AfterUpdate and it worked and deleted the signoff
date as desired.

Thanks.

 

[Guest]

Objective was to check controls for their tags and if any were missing
values, to set the basignoff date to null, all of which has been done. Code
below. Added a Dirty line to try to save.

But, even though I get the right msgbox and even though the signoff date is
removed, I cannot get out of the record. Code was in AfterUpdate and I tried
it in BeforeUpdate.

Getting error: The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing MS Access from saving
the data in the field.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
ValidateIssueLine
End Sub

Public Sub ValidateIssueLine() '
Dim ctl As Control
Dim strEmpty As String
Dim strMsg As String

For Each ctl In Me.Controls

If ctl.Tag = "check" Then
If IsNull(ctl) Then
strEmpty = strEmpty & vbCrLf & ctl.Name
End If
End If
Next ctl

If strEmpty <> "" Then
strMsg = "These fields are missing a value: " & vbCrLf & vbCrLf & _
Mid(strEmpty, 3) & vbCrLf & vbCrLf _
& "All required fields must be filled before BA Line
Item Signoff." & vbCrLf & vbCrLf _
& "Please complete line and signoff again."
MsgBox strMsg, vbCritical + vbOKOnly, "Issue Line Incomplete!"
Me.IssueStatus = "I" 'Incomplete
Me.BA_Signoff.Value = Null

'? need to save record after setting its values ?
If Me.Dirty = True Then
Me.Dirty = False
End If

Else

Me.IssueStatus = "C" 'Complete

End If

End Sub

 

[David C. Holley]

Here's an alternate function which I just posted for someone else.

In the Forms_BeforeUpdate event use

If validateFields(Me) <> 0 then
Msgbox("You've left out information in the highlighted fields.
You're fired get out of this office and don't let the door hit you on
the way out"
Cancel = True 'I think that this is what you were missing earlier
end if

If you're

Function validateFields(frmFormName As Form)

Dim control As control
Dim fieldCount As Integer

fieldCount = 0

For Each control In frmFormName.Controls
If control.Tag Like "*" & "required" & "*" Then
control.ForeColor = 0
control.BackColor = 16777215
End If
Next

For Each control In frmFormName.Controls
If control.Tag Like "*" & "required" & "*" Then
If IsNull(control.Value) Or control.Value = "" Then
control.ForeColor = 16777215
control.BackColor = 255
fieldCount = fieldCount + 1
End If
End If
Next

validateFields = fieldCount

End Function

 

[Guest]

Thanks. I did get it to work similar to your suggestion.

The Cancel=true wouldn't work for me cuz it everything was ok, and the line
was signed off, and the user removed a required value, and I deleted the
signoff date, the Cancel=true, but it right back in again. Also, when a
record goes out of favor, I also mark my IssueStatus field "I" for
incomplete, which also gets Cancelled.

But, your idea of coloring the fields, I like! But, doesn't that color all
the records' fields in the column? I thought I had tried that a long time
ago.