PREVENT Default Value of "0" fields

[Tango524]

I have a database that keeps track of the date, employeeID, taskID, typeID and the hours and minutes spent on the task and type of work. All information is manually keyed into this DB by a user. I would like to prevent this user from accidently posting 0 hours and minutes for a task or type. If she only types in a new employeeID, etc, and enters through the other fields, that employee will have 0 hours and minutes for that task and it will affect my later calculations. I am using this code in a beforeupdate event procedure.

It seems like it isn't picking up the default 0 (entering through the fields), only if I manually type 0 in the field, I will get my error.

Dim control1, control2, control3, control4 As Integer
If Not (IsNumeric(HoursIn)) Then
control1 = 0
Else
control1 = HoursIn
End If
If Not (IsNumeric(MinutesIn)) Then
control2 = 0
Else
control2 = MinutesIn
End If
If Not (IsNumeric(HoursOut)) Then
control3 = 0
Else
control3 = HoursOut
End If
If Not (IsNumeric(MinutesOut)) Then
control4 = 0
Else
control4 = MinutesOut
End If

If ((control1 = 0 Or Me!HoursIn = 0) And (control2 = 0 Or Me!MinutesIn = 0) And (control3 = 0 Or Me!HoursOut = 0) And (control4 = 0 Or Me!MinutesOut = 0)) Then

Do
MsgBox ("Data Entry Error. You must not have zero or null values. Please fix this error before proceding.&quot
End
Loop Until ((control1 <> 0) Or (control2 <> 0) Or (control3 <> 0) Or (control4 <> 0))

End IF

I have tried everything: isblank, isempty, isnull, = &quot;&quot;, =0. I have even used field.defaultvalue, I have set defaultvalue in properties to 0 or blank. Nothing seems to work! Please help. Thank you!

 

[danvlas]

BeforeUpdate is triggered if you make a change in the field.
Use the Exit event instead of BeforeUpdate. It also has the Cancel argument that you can set to True...

Private TextBoxName_Exit(Cancel As Integer)
If Nz(TextBoxName,0)=0 Then
Cancel=True
MsgBox &quot;Dude, your data is invalid!&quot;
End If
End Sub


Use the BeforeUpdate event of the form to double check that everything is fine.
Set the Required property of the table field to True and delete everything in the Default Value.

Good luck



Daniel Vlas
Systems Consultant

 

[Tango524]

I set the required property to true in my table- thanks!