Display Error when Textbox is over a Certain amt

[hardhitter06]

Hi All,

I created a textbox on a continious form that calculates the total of a particular field (CommAmt) of all records. I have =Sum(CommAmt) as the control source of this textbox.

When this field is 20,000 or over, I would like the form to display an error saying you are over the allowed amt.

I have tried this code on the textbox for Before and AfterUpdate but it isn't working, can someone help me with what I'm doing wrong.

Private Sub Text13_BeforeUpdate(Cancel As Integer)
If Text13 > (19999.99) Then
MsgBox ("Comm Amt excedes limit!")
Cancel = True
End If
End Sub

Thank you.

 

[MSAccessRookie]

Hi All,

I created a textbox on a continious form that calculates the total of a particular field (CommAmt) of all records. I have =Sum(CommAmt) as the control source of this textbox.

When this field is 20,000 or over, I would like the form to display an error saying you are over the allowed amt.

I have tried this code on the textbox for Before and AfterUpdate but it isn't working, can someone help me with what I'm doing wrong.

Private Sub Text13_BeforeUpdate(Cancel As Integer)
If Text13 > (19999.99) Then
MsgBox ("Comm Amt excedes limit!")
Cancel = True
End If
End Sub

Thank you.

See what happens if you make the Control Source something like this instead:

IIf(Sum(CommAmt) > (19999.99), "Exceeds Limit", Sum(CommAmt))

 

[hardhitter06]

It's displaying :#Name?"...

 

[MSAccessRookie]

It's displaying :#Name?"...

Try replacing CommAmt with Me.CommAmt (or something that identifies the field)

Hang on there. How can you sum a single field? In order to sum there must be a group of items to sum. I assumed before that CommAmt was a Comumn in a table or query.

 

[hardhitter06]

I put IIF(Sum(Me.CommAmt) > (19999.99), "Exceeds Limit", Sum(Me.CommAmt)). Still the same result...

 

[missinglinq]

The BeforeUpdate and AfterUpdate events of Text13 isn't firing because it is being populated thru code; they'll only fire if you physically enter data into the textbox, thru the keyboard or using Copy and Paste.

You need to place this code in the BeforeUpdate event of the field being summed, Comm Amt if I've read the thread correctly.

 

[hardhitter06]

So put:

Private Sub Text13_BeforeUpdate(Cancel As Integer)
If Text13 > (19999.99) Then
MsgBox ("Comm Amt excedes limit!")
Cancel = True
End If
End Sub

Under the field CommAmt's BeforeUpdate and leave =Sum(CommAmt) in the control source of the Sum textbx?

I did that and it still had then Name? error

 

[hardhitter06]

And do you mean, the field that is Summed (Commamt) or the textbox that is summing the CommAmt with the control source of =sum(commamt) because I'm assuming that actual field CommAmt

 

[MSAccessRookie]

So put:

Private Sub Text13_BeforeUpdate(Cancel As Integer)
If Text13 > (19999.99) Then
MsgBox ("Comm Amt excedes limit!")
Cancel = True
End If
End Sub

Under the field CommAmt's BeforeUpdate and leave =Sum(CommAmt) in the control source of the Sum textbx?

I did that and it still had then Name? error

Something is not being referred to correctly. The most common reason for that might be a misspelled field or control name.

 

[hardhitter06]

I copied and pasted.

 

[missinglinq]

By any chance is the field Comm Amt a calculated field?

 

[hardhitter06]

CommAmt is a number that someone enters, no calculations are on this form besides the Sum of the CommAmts in the unbound textbx I'm trying to get to display the error.

 

[hardhitter06]

I've tried all these combinations and I'm still not getting it to work. Any more ideas/Modifications?

MSAccess, I just saw a question i missed. It's a continious form so I'll calculating the CommAmt of all the records under a certain UNSPSC code. The summing works fine, I just want an error to display somewhere when you exceed 20 K of the total.

 

[missinglinq]

My guess is you need a post with your database attached for us to go over. Also, what version of Access are you running?

 

[hardhitter06]

Hi,

Here is my zipped database. I use Access 2003. I cut out the code that you guys recommended because it wasn't working right and also I wasn't exactly sure where it needed to go and I didn't want it to effect anything when you guys looked it over.


Right now my database has some test samples so use UNSPSC code "11111111" to see the balance over 20,000.

Thank you !!

 

[MSAccessRookie]

Hi,

Here is my zipped database. I use Access 2003. I cut out the code that you guys recommended because it wasn't working right and also I wasn't exactly sure where it needed to go and I didn't want it to effect anything when you guys looked it over.


Right now my database has some test samples so use UNSPSC code "11111111" to see the balance over 20,000.

Thank you !!

I wish I had this to look at earlier. It makes things a little easier to understand. Although I am sure that I do not have all of the answers, here are a few observations.

• PET PEEVE: (Note, this is just an observation, and changing this is not required).
  • Some of your Control Fields still contain the names that Access assigned them when they were created. I try to rename Control Fields with names that describe their purpose whenever it is possible to do so. That way, when they are used in VB Functions and SubRoutines, or as parameters in Queries, it is easier to understand what is going on. It will also help in the future if you (or the next guy) ever need to come back and revise any of the code.
• The Combo Box Combo4 has a row source of "SELECT tblMain.UNSPSC FROM tblMain GROUP BY tblMain.UNSPSC;". This allows all UNSPC Codes to be available, regardless of whether they should be. Adding some sort of filtering qualifiers (Available Amount, Date Range or not) here would help you to limit the list to the ones that are currently available.
• As the data is loaded onto the form (in the Form On Load Event), the the background of the Comm Amt Field could be set to reflect its status (Perhaps it could be Red to show Overdrawn). Visual Alerts are often useful until a complete resolution is available.

I am sure there will be other suggestions and recommendations.

 

[hardhitter06]

I'm messing around with this, I'm not trying to display an error message by summing up these amts, I just wan't an error message to display in Text13 (which is the sum box of the CommAmts) displays higher than 20,000 K.

So basically,

If Text13 >20000
MsgBox "Exceeded Limit".

How do I put that in english for Access Code?

 

[MSAccessRookie]

I'm messing around with this, I'm not trying to display an error message by summing up these amts, I just wan't an error message to display in Text13 (which is the sum box of the CommAmts) displays higher than 20,000 K.

So basically,

If Text13 >20000
MsgBox "Exceeded Limit".

How do I put that in english for Access Code?

It looks like someone (you?) already tried to create that code behind the "Before Update Event" for that field.

If CommAmt > (19999.99) Then
    MsgBox ("Comm Amt excedes limit!")
    Cancel = True
End If

Note that this will not work UNLESS a single entry is greater than 19999.99.

You could try to create a hidden field that contains the Sum() for all CommAmt that is relevant, and use that value instead.

 

[hardhitter06]

MSAcc, you're back .

I was posting this last message before I was able to see your response.

Um, with your 2nd bullet pt, I like the idea, but I still need users to be able to check upon the Amount and POs of all UNSPSC codes in the system therefore, for example, if I did create a filter that only allowed ones under the balance to be seen, that would help with my other problem, but still would hide records for the users to view.

For your 3rd pt, Yeah you're right, but this thread was to get a message to state when you have been over drawn, but I can't seem to get that to work. I dunno if this would be easier, but what if the Remaining balance triggerd a response if the total went into the negative? Is that easier to code?

And thank you for your first bullet pt, you are right and I will get around to updating that.

 

[hardhitter06]

OK You might be on to something.

This error message tho, is for the searchable form, not the input form.

BUT, this hidden field that sums the total and checks would be great, but I don't know how I would add the date aspect into the calculation. Right now on the Search Form, it only returns dates that are 365 days previous to today's date. Then it sums the total.

I'm wondering if I should just allow Data Entry to the continious form so that a user can see the total at all times and I could set up the Error Message there if they exceed 20 K.