Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

If user deleted numeric value mafe the field a "0"

I have a sub-form with a tab that has several numeric fields.  If the user deletes the value in any of them I want the result to be a zero, not blank.

Is there a way to do this with some kind of module?
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

I guess a way is to use the LostFocus event so when the user "leaves" the textbox it should check
 if len(textbox) =0 then textbox = 0

Open in new window

Another way is preferably if you use custom buttons for navigation to check when you move around records to check the value of your controls and revert them to 0
It would be something like this :
Dim ctl as control
For each ctl in Me.controls
If ctl.ControlType = acTextBox then
if len(ctl) =0 then ctl = 0
end if
end if
Next
Avatar of SteveL13

ASKER

But I have about 30 of these fields on the form.  Do I have to do this with each of them or is there a way to do it "globally"?
By mistake i pushed submit so here is the global code :
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If Len(ctl) = 0 Then ctl = 0
    End If
Next

Open in new window

Also there is another way the "advanced" one ...this is called event sinking...with this you can assign a default behavior for events for controls you select to follow this behavior
Check my post here
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I guess I don't know how to use this code.  Where do I put it?
Did you try the no code solution that I suggested?  Whenever you elect to implement RI using code you have to be completely in control of the application and no other application can be sharing your data.  Every place that a field might possibly be changed requires implementing the RI code.  

The code tsgiannis suggested applies to ALL text boxes on the form, NOT just the ones you are talking about.  If you want to use code like this (which I don't recommend), use the tag property of the control to essentially create a control group and then the code will only impact the controls with the tag property.  Of course, you will need to remember that you did this and if you add an additional control that you want to include, you will need to set its Tag property also.  Code of this type would be placed in the FORM's BeforeUpdate event.
Typically having a Blank in a number field is not an issue...
In your situation, what is the concern if the field value is deleted?

Like Pat, I really hate resorting to code for things that should be automatically done in the UI.
Make the field required, and inform the user that they cannot simply delete the value, ...they must enter a zero.

A less intensive alternative might be to create a "Show Zero If Empty" field in a query.
The user could still delete the value in the specified field, ...but if deleted, the "show Zero" field would display a zero.

Something like this in the query grid:
ShowZero: IIf(IsNull([YourField]),0,[YourField])

Or if using SQL:
SELECT Field1, Field2, YourField, IIf(IsNull([YourField]),0,[YourField]) AS ShowZero

Then, For example, on your reports, you could hide the main field and display the ShowZero field.

JeffCoachman
But please reply to Pat's post first to avoid confusion...
I have decided to use Pat's suggestion.  (Required field, 0 as the default)