access 2010 vba pass textbox value to a function and do stuff with it

Hi,

I have a form with 38 textboxes that will contain number values entered by the user. However, what i want is a simple function that when a user leaves any of the fields blank the function will populate that field with a "0".

I have used this on the textbox onLostFocus event but i do wish to do this 38 times throughout the form.

Private Sub TextVal1_LostFocus()
     If Me.TextVal1 = "" Then
        Me.TextVal1 = 0
    End If
End Sub

Can anyone suggest a how i put this into a function but keep this function with the form?

Many thanks
andrewpiconnectAsked:
Who is Participating?
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.

Barry CunneyCommented:
Put a function in your Form module

FunctionZerOutTextBox(thisTextBox as TextBox)

     If thisTextBox.Value = "" Then thisTextBox.Value = 0

End Function


Private Sub TextVal1_LostFocus()

 FunctionZerOutTextBox   Me.TextVal1

End Sub
0
Dale FyeCommented:
Private Function IfBlankZero()

    Dim ctrl As Control
    
    Set ctrl = Screen.PreviousControl
    
    If ctrl.ControlType = acTextBox And ctrl.Value & "" = "" Then
        ctrl.Value = 0
    End If
    
End Function

Open in new window

Then, if you select all of your textboxes in design view, you can simply type:

=IfBlankZero()

In the Lost_Focus event once and it will be applied for each of those textboxes.
0

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
andrewpiconnectAuthor Commented:
nearly there,

i had to change the last bit to:

Private Sub TextVal1_LostFocus()
    Call ZeroOutTextBox(Me.TextVal1)
End Sub
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

andrewpiconnectAuthor Commented:
accepted this one as this really limited the amount of vba on the forms module i.e. by putting =IfBlankZero() on each control in design view

Many thanks
0
andrewpiconnectAuthor Commented:
would i have not got the same result by simply setting the Default Value of each textbox to 0 in design view. FYI all the textboxes are Unbound
0
Dale FyeCommented:
Yes, to some degree that would have done it too, but it would  not have provided any visual indication as to what controls you have intentionally moved through.  

The values would all start out as zero, which may have actually been what you wanted in the first place, but not what you asked.
0
andrewpiconnectAuthor Commented:
Many thanks for the extra advice.
I have set them all to zero as the user will only enter values into some of the fields.
I will keep your code running anyway as an extra precaution as i cant see it being too load bearing and it will no doubt come in hand for other coding further down the line. I  now know how to reference a control value in vba so have picked up some valuable info
0
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.