Not adding a blank textbox

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

I have the following code in the On Enter of a textbox:

Private Sub TotalMonthlyExpense_Enter()
TotalMonthlyExpense = [HousingExpenses-Rent/Board] +
[HousingExpenses-Heat/Oil] + [HousingExpenses-Hydro] +
[HousingExpenses-Cable] + [HousingExpenses-Telephone] +
[HousingExpenses-Other] + [FixedExpenses-ChildCare] +
[FixedExpenses-ChildSupport] + [FixedExpenses-CreditCards] +
[FixedExpenses-Medical] + [FixedExpenses-Other] +
[FixedExpenses-PersonalLoans] + [FixedExpenses-Transportation]
End Sub

However it requires that each and every textbox included in the formula be
not null. How can I make it still add all of the fields even if some are
blank?
 
Hi Bruce,

That works perfectly! I realize now that my naming convention is off, things
they never teach you in a book. Thanks for your help!

BruceM said:
Why the Enter event? That requires the user to click in or tab to
TotalMonthlyExpense before they can see the total.

The short answer is you could use the Nz function:

Me.TotalMonthlyExpense = Nz(Me.[HousingExpenses-Rent/Board],0) + _
Nz(Me.[HousingExpenses-Heat/Oil],0)
etc.

See Help for more information about Nz. Note that the underscore is a line
continuation character. It is not part of the code as such.

You really need to stay away from special characters other than underscores
in your field names. Under some circumstances Access could try to perform
math with the slashes and hyphens, which it could view as division and
subtraction. For your own benefit, shorter field names are easier to work
with. For instance: HE_RB (room and board), HE_Heat, etc.

The larger point is that the expenses probably should be in a related table.
I don't know the situation behind the database, or the rest of the
information stored in the form's record source table, so I can't suggest to
what table the expenses should be related, but I suspect you are using
Access to make a spreadsheet.

You would do well do review some database fundamentals. A good place to
start is here:
http://allenbrowne.com/casu-22.html

Emma said:
I have the following code in the On Enter of a textbox:

Private Sub TotalMonthlyExpense_Enter()
TotalMonthlyExpense = [HousingExpenses-Rent/Board] +
[HousingExpenses-Heat/Oil] + [HousingExpenses-Hydro] +
[HousingExpenses-Cable] + [HousingExpenses-Telephone] +
[HousingExpenses-Other] + [FixedExpenses-ChildCare] +
[FixedExpenses-ChildSupport] + [FixedExpenses-CreditCards] +
[FixedExpenses-Medical] + [FixedExpenses-Other] +
[FixedExpenses-PersonalLoans] + [FixedExpenses-Transportation]
End Sub

However it requires that each and every textbox included in the formula be
not null. How can I make it still add all of the fields even if some are
blank?
Click to expand...
Click to expand...
 
Back
Top