Requiring at least one field have a number

[Joe Champagne]

I have users entering time and multiple fields with counts. I need to
require at least one of those 5 fields have an entry. I added a field on the
form that sums the counts and set its validation rule. However, from reading
threads about validation I see it doesn't get triggered without a user
entering date in that field, so that isn't working. Is there a way to
require at least one of my 5 fields have data before committing the record?

Thanks.

 

[George Nicholson]

In the Form_BeforeUpdate event.

intCombined = nz([Field1],0) + nz([Field2],0) + ... + nz([Field5],0)

if intCombined = 0 then
'All fields are Null or zero. Prompt user and Cancel Update
msgbox "Please enter a value in fields 1 to 5"
Cancel = True
End If

Note: the Form_BeforeUpdate event will only fire if *some* change has been
made to the data on the form (i.e., Dirty = True). All you are doing here is
validating that one of those 5 fields has a non-zero value.

 

[Arvin Meyer [MVP]]

In the BeforeUpdate event of the form you can write something like (air
code):

Sub Form_BeforeUpdate (Cancel As Integer)
If Len(Txbox1 & Txbox2 & Txbox3 & Txbox4 & Txbox5) <>0 Then
MsgBox "You need to fill in at least 1 field", vbOKOnly, "Data
Error"
Cancel = True
End If
End Sub

 

[Joe Champagne]

Excellent, thank you much. Worked like a champ!

In the Form_BeforeUpdate event.

intCombined = nz([Field1],0) + nz([Field2],0) + ... + nz([Field5],0)

if intCombined = 0 then
'All fields are Null or zero. Prompt user and Cancel Update
msgbox "Please enter a value in fields 1 to 5"
Cancel = True
End If

Note: the Form_BeforeUpdate event will only fire if *some* change has been
made to the data on the form (i.e., Dirty = True). All you are doing here is
validating that one of those 5 fields has a non-zero value.

--
HTH,
George

I have users entering time and multiple fields with counts. I need to
require at least one of those 5 fields have an entry. I added a field on
the
form that sums the counts and set its validation rule. However, from
reading
threads about validation I see it doesn't get triggered without a user
entering date in that field, so that isn't working. Is there a way to
require at least one of my 5 fields have data before committing the
record?

Thanks.