Code for a total text box.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that has four text boxes that should = 10. There are occasions
when there could be less than or more than 10.
What I would like to know is how to prompt a message box to come up after
the fourth one looses focus to indicate the total that it equals. Then I want
to be able to accept the total that is given and make the Total text box =
the total that is not equal to 10. If the total equals 10 then there is no
prompt.
Could I get someone to help me on the code.
 
Curt,
You only have to capture the 4 values... not the Total. The Total for
all values can always be recalculated "on the fly" as needed in any
subsequent form, report, query, etc...
Just place an unbound text control (call it ex. [MyTotal]) on your form
with a ControlSource of
= Nz(Value1) + NZ(Value2) + NZ(Value3) + NZ(Value4)
and that will always display the total of all 4 values.

Now, on the AfterupDate event on Value4...
If (Nz(Value1) + NZ(Value2) + NZ(Value3) + NZ(Value4))=10 Then
Exit Sub
Elseif (Nz(Value1) + NZ(Value2) + NZ(Value3) + NZ(Value4))<>10 Then
'Place your message box here with an OK and Cancel button
'If OK then Exit Sub
'If Cancel then Value4 = 0 (or whatever you want to do to cancel those
values)
End If

hth
Al Camp
 
In the Lost Focus event of txtFour:

lngTot = Me.txtOne + Me.txtTwo + Me.txtThree + Me.txtFour
If lngTot <> 10 then
If msgBox("Total Is " & Cstr(lngTot),vbOkCancel) = vbCancel Then
Do what happens if you don't want to accept it.
Else
Do what happens if you want to accept it
End If
Else
Do what happens when it is = 10
End If
 
Hi. Curt.
Could I get someone to help me on the code.
Click to expand...

Sure. If the names of the text boxes were txtQtr followed by the relevant
digit, txtTotal was the name of the text box to display the total, and
txtQtr4 was the last text box that you are interested in making sure that the
calculation is made, then you could try the following code:

' * * * * Start Code * * * *

Private Sub txtQtr4_Exit(Cancel As Integer)

On Error GoTo ErrHandler

Dim nTotal As Integer
Dim nAns As Integer

nTotal = (CInt(Nz(Me!txtQtr1.Value, 0))) + (CInt(Nz(Me!txtQtr2.Value,
0))) + _
(CInt(Nz(Me!txtQtr3.Value, 0))) + (CInt(Nz(Me!txtQtr4.Value, 0)))

If (nTotal <> 10) Then

nAns = MsgBox("The total doesn't equal 10. Do you" & vbCrLf & _
"wish to use this value anyway?", vbYesNo, "Total Is Not 10")

If (nAns = vbYes) Then
Me!txtTotal.Value = nTotal
Else
' Do whatever alternative that you want. For example:
Cancel = True ' Focus stays in the current text box.
End If
End If

Exit Sub

ErrHandler:

MsgBox "Error in txtQtr4_Exit( ) in " & vbCrLf & Me.Name & " form." & _
vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * Start Code * * * *

Using the Exit event rather than the Lost Focus event allows the event to be
cancelled.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Thanks for the help.
I did have a total box that the value went to so I used what Klatuu sent me
as follows:
IngTot = Me.QtySRej + Me.QtySLgMed + Me.QtySJumbo + Me.QtySColossal
If IngTot <> 10 Then
If MsgBox("Total is not equal to 10. " & "The total equals " &
CStr(IngTot) & ".", vbOKCancel) = vbCancel Then
MsgBox ("Check your entries and correct.")
Else
Me.TotalBulbs = IngTot
End If
Else
Me.TotalBulbs = QtySRej + QtySLgMed + QtySJumbo + QtySColossal
End If
This works great.
I would like to set the focus to Me.QtySRej after I click OK on the MsgBox
("Check your entries and correct.").
How do I set the focus?
 
Hi, Curt.
This works great.
Click to expand...

For the current record, yes. It will bomb with Error #94 in the near future.
How do I set the focus?
Click to expand...

Use the following syntax:

Me!QtySRej.SetFocus


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
 
Thanks this worked great.

'69 Camaro said:
Hi, Curt.


For the current record, yes. It will bomb with Error #94 in the near future.


Use the following syntax:

Me!QtySRej.SetFocus


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
Click to expand...
 
You're welcome. Be careful about storing a calculated value (TotalBulbs) in
the table, because the values that the calculated value depends upon can
change, which would necessitate changing the calculated value. This can
become a maintenance nightmare, as well as produce inconsistent data.

And thanks for marking our posts as answers to your question. It's much
appreciated!

Gunny
 
Hi.
Curious as to why you say it will bomb with an error 94. What did I miss?
Click to expand...

Users never seem to do the required operations in the order we expect them
to. What would you say the probability is that the user will exit the
fourth text box (likely QtySColossal) _before_ the three other text boxes
are filled in with numbers? The probability needs to be zero to prevent
Curt's code from failing.

If even one of those text boxes lacks a number, then adding NULL to the
values in the other text boxes during the Lost Focus event will halt code
execution with an Error #94 message.

HTH.

Gunny
 
Back
Top