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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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
SteveL13Author Commented:
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"?
John TsioumprisSoftware & Systems EngineerCommented:
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

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

John TsioumprisSoftware & Systems EngineerCommented:
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
If you don't want a field to be null, then in the table, set its required property to Yes and set a default of 0 if that makes sense.    You should not use code to do what you can have the database engine do with declarative RI.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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.

Jeffrey CoachmanMIS LiasonCommented:
But please reply to Pat's post first to avoid confusion...
SteveL13Author Commented:
I have decided to use Pat's suggestion.  (Required field, 0 as the default)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.