Fitness 54
By Richard Rost
16 days ago
Allow Users to Enter Equations Directly Into a Field
In this Microsoft Access tutorial I will show you how to let users type mathematical equations directly into form fields, such as the CaloriesPerUnit field, and have Access evaluate and store the result instantly without using any pop-up input boxes. We will walk through using the Form_Error event to intercept invalid input, evaluate the expression, handle Access quirks with error handling, and ensure calculations are updated live as users type equations into your forms.
Members
In the extended cut, we will make the equation-typing feature work with any field and any form, and I will show you how to get notifications while using it. We will learn how to generalize the solution beyond just one field and enhance the functionality.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, let users type equations in forms, equation input without popup, evaluate user input equations, form error event, intercept data error 2113, error handling in forms, evaluate textbox input, allow calculations in form fields, acDataErrContinue, remove equals sign from input, CaloriesPerUnit field, Eval function, On Error Resume Next
Subscribe to Fitness 54
Get notifications when this page is updated
Transcript
Today I'm going to show you how to let your users type equations directly into any field in your Microsoft Access Forms - no pop-up required.
Back in lesson 49, I showed you how to do the same thing with Excel style equations. That required a pop-up input box. The user would hit the equal sign. That required this input box to come up with the calculation. You could type in the equation here and then it would get evaluated and stored back in that field again.
A lot of you emailed me asking if it is really necessary. No, it is not necessary if you set up a hidden text box. I think we talked about this in that lesson. You can make a hidden text box that stores text. You could save the value in the text and evaluate it. But then you have to set up a hidden one of those for each one of these fields, which is a pain. That's why I popped up the input box.
But then I thought to myself, there has to be a better way. There is a better way. But it involves some tricky error handling. We can basically let the user type whatever they want in that box, let it generate an error, hide the error message, intercept it, see if it is a calculation or an equation that we can figure out, save that value back to it, and then tell it to ignore the error. It took a little brain noodle firing for me to come up with this one, but it works.
Let me show you how.
We're going to come in here and go to the form properties.
By the way, just because we learned something new - a new way of doing something - doesn't mean the old way was bad. It's like when science learns something new, it doesn't mean the old way was necessarily bad. It might have been - bloodletting to get rid of evil demons if you're sick - that can... yeah, well, that's not really science. But it just means that we've discovered a new way of doing it, while the old way was perfectly fine.
We can turn key preview off. We're no longer going to intercept that key down event. So if you have a key down event in here, you can just get rid of that form key down event.
Members, we built the thing called do calculator. We're going to update that in the extended cut. But for everybody else, just get rid of that form key down event. I think we have it in another place too. So wherever you have it, get rid of it.
Members, you can delete your do calculator function. We're going to make something different today in the extended cut.
Now what I want to do is access the form error event. Go to the form properties and then find Form_Error On Error. There it is, way down here. Go into the On Error event.
This event is going to pop up whenever anything in this form generates an error. It will give you a data error, which is the error number. You can also give it a response, meaning how do you want to respond to it. In other words, you can say show the default error message or hide the error message. We'll handle it ourselves, which is what we're going to do.
I have a whole separate video on trapping form errors. You can take the weird Access messages like "an error has occurred 32" or whatever and give your users more user-friendly messages. Which is basically what we're going to do except instead of a message, we're just going to ignore it. We're going to evaluate it if we can, and if not, just ignore it and give them the message.
First we have to know what error gets thrown - what number gets generated if the user tries typing any calculation. For now, just message box the data error. Let's figure out what number it is.
You can look it up on Microsoft's website if you've got an hour to spend. I just have Access tell me what it's doing. In here, I'm going to come down here and use a different one - I've already got my meals in here today. If I type in equals 2 plus 8, the error number that gets generated if you type invalid data into that field is 2113.
That's the error message you normally get. We're going to ignore that. Hit escape. So it's 2113.
Here I'm going to say: if data error equals 2113 then - this is, "the value you entered isn't valid for this field." Basically, they typed in something that they're not supposed to type in, like Joe.
Now, we don't want to throw this for just any field, because if they try typing in something invalid in another field or a time that's invalid, I don't want it to work for every single field. So let's just focus on the Calories field - CaloriesPerUnit.
Members, we're going to make it global so we can use it anywhere in any form and any field. But for now, let's just focus on one field.
Here, I'm going to say: if the user is on the CaloriesPerUnit field, if Screen.ActiveControl.Name = "CaloriesPerUnit" then do some stuff.
What is "some stuff"? Well, I want to get the text that they typed in and see if I can evaluate it. So we need to store that in a local variable. So:
Dim s As String
s = CaloriesPerUnit.Text
We have to get the text in the box, not the value in the box, because an error has been generated. That value has not been saved yet. It hasn't been saved to the table. It's not stored anywhere except as just text in a text box. That's what the .Text value is. It's not the value of the property or the field underneath, just the text in the box.
Now let's see if we can evaluate it. So: s = Eval(s)
Now, if after you've evaluated it, it turns out to be a number, then it's good. We can save it in the field.
So: if IsNumeric(s) then
CaloriesPerUnit.Text = s
We're replacing the text. You can't save it to the value at this step. Trust me, I've tried. All we're doing is replacing the text that the user typed in. It's not saved to the field yet until we exit out of this event. But we're going to set that equal to s. Now we should be good, because we put a number in there, so we can ignore the error.
So: Response = acDataErrContinue
That's just a fancy Access constant. The other one is acDataErrDisplay, which is the default. But you don't need to know that because if you just don't give a response it will use that response.
And there's one more thing I want to put in here. If you're used to typing in the equal sign and you want to keep the equal sign, that's fine, just remove it in here. So right here I'll put:
If Left(s,1) = "=" Then
s = Right(s, Len(s)-1)
That removes the equals from the front. I'm in the habit of using the equal sign now, so you can use it or not use it - it's up to you.
Save it. Debug, compile once in a while. Come back out. Now I'm going to close it, reopen it, come down here to my test one, and type in =1+3, and press Enter.
You're going to get an error message here. If you hit debug, it is because we're trying to set it back equal to something else - equal to s, which is 4. That's correct. But what's happening is it's generating an error message inside of here now that we then have to also ignore. I know it's weird. The Form_Error fires, brings you into this code.
But as you can see, it's working. So now we just have to ignore that error too. I know it's weird, but the problem is fixed by simply doing this. Watch. Come right up here. Let's do it in here. We can do it at this point.
We're going to say: On Error Resume Next
And if you want to be safe, down here, say: On Error GoTo 0
That turns off error handling, in case later on you decide to add more stuff down here. We don't want to just leave error handling off.
Save it. Debug, compile again. We don't have to. Close it. Open it. Come down in here and type in =3+1. Enter. There's your four.
The nice thing is you can keep going because it just replaced the text. We're still editing the text. So now I can also go *2. Enter. There's an 8. +3, Enter. +3-4*2, Enter. And it keeps going until you hit Tab, and then it saves it.
When I figured that out, I was like, oh man, when I hit that last error message before I realized you have to do this, I was kind of stumped. I'm like, why is it still throwing an error? Because we're in the Form_Error event. It shouldn't be doing that.
But it does. But if you ignore it, it works. Access has all these little quirks. I love it, but it's just got all these quirks. It's saying, "oh, you can't do that," but it lets you.
So that's it. That's how you can do it. Now you can go in here and you don't need the equal sign now, you can just type in 10*3. Boom. Because we're chopping off that equal sign. And anything you can evaluate - 30+(8/4). I don't want to put fractions in there, because you'll get fractions. If you don't want fractions, you can round that stuff off too.
So like, 10/3, you're going to get that. If you don't want that, I'd come in here and then you could just say int(s) like this, see if we can get away with that: 10/3. Yeah, that'll work. That's fine.
But that's it. There you go. That's how you do it. That's the technique. I have now shown you my secret.
Members' extended cut: we have a little more work to do. We're going to make it so that this works with any field, and we're going to get notifications while we're doing it. Sorry about that, I had a reminder pop up. We're going to make it work with any field and any form and have some fun. So that's in the extended cut.
And I've got noises going off in my office here. Someone's beaming in, my dog's just barked in the background. Oh.
So that's it. That's your TechHelp video for today. Hope you learned something. That was pretty cool.
I hope you enjoyed this video. Hit that thumbs up button right now and give me a like. Also be sure to subscribe to my channel, which is completely free. Make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access Experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mention in the video can be found in the description text below the video. Just click on that "Show More" link right there. YouTube is pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basic Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on. And did I mention it's completely free?
If you like Level 1, Level 2 is just one dollar. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month. And yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses - all of them, from every subject - and you get one free advanced or developer class every month after finishing the expert series.
You can become a diamond sponsor and have your name listed on the sponsor page on my website.
That's it.
Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today.
Live long and prosper, my friends. I'll see you next time.
TOPICS:
Allowing users to type equations directly into Access form fields
Accessing and handling the Form_Error event
Determining the specific error number for invalid field data
Filtering error handling to work with a specific field
Extracting user input using the .Text property
Evaluating user-entered expressions with the Eval function
Replacing text box content with evaluated results
Removing the leading equals sign from expressions
Suppressing default Access error messages with acDataErrContinue
Using On Error Resume Next to suppress runtime errors
Restricting changes to only targeted text box fields
Converting evaluated results to integers with Int function
Allowing chained calculations by editing the text box repeatedly
COMMERCIAL:
In today's video, I'm going to show you how to let your users type equations directly into any field on your Microsoft Access forms and have them automatically calculated, all without the need for a pop-up box. You'll learn how to use the Form_Error event and some clever error handling in VBA to evaluate and save those expressions smoothly, including tricks like removing the leading equal sign and handling tricky error messages. Plus, I'll share solutions to a few common Access quirks along the way. In today's Extended Cut, members will also see how to expand this technique to work with any field on any form and enable notifications as calculations happen. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What was the major improvement presented in this video compared to the previous lesson about entering equations in Access forms?
A. It avoids the need for a pop-up input box by letting users type equations directly into any field.
B. It requires using a more complicated pop-up input box for all calculations.
C. It makes all fields on the form read-only to prevent errors.
D. It only allows equations in hidden fields, not visible ones.
Q2. In the new method, how are equations processed when the user types them directly into a field?
A. By intercepting the error generated, hiding the error message, and evaluating the input.
B. By forcing users to correct all inputs before saving.
C. By requiring the user to press a special function key before entering an equation.
D. By automatically saving text without checking for errors.
Q3. What is the purpose of using the Form_Error event in Access for this method?
A. To catch errors generated by user input and handle them programmatically.
B. To display every Access default error message.
C. To disable editing in all form fields.
D. To automatically format all user inputs as currency.
Q4. Which specific error number is generated when a user enters an invalid value for a field, such as an equation?
A. 2113
B. 1001
C. 4000
D. 3265
Q5. What is the main reason for using CaloriesPerUnit.Text instead of CaloriesPerUnit.Value when evaluating the input?
A. The .Text property contains the unsaved user input that triggered the error.
B. .Value contains the result of the previous equation.
C. .Text is always more accurate than .Value.
D. .Value cannot be accessed in the Form_Error event.
Q6. When users want to type Excel-style equations that begin with an equal sign, what must be done in the code?
A. Remove the equal sign from the start of the string before evaluation.
B. Replace all plus signs with equals signs.
C. Add a semicolon at the end of the string.
D. Reject any input that starts with an equal sign.
Q7. What does the code Response = acDataErrContinue accomplish in the Form_Error event?
A. It tells Access to ignore the default error and continue processing.
B. It displays the Access default error message.
C. It restarts the form.
D. It erases the user's last input.
Q8. Why is the statement On Error Resume Next used in the event code?
A. To suppress additional errors that may occur when handling the original error.
B. To force the code to stop executing.
C. To make all error messages visible to the user.
D. To automatically log all errors to a table.
Q9. Why should the On Error GoTo 0 statement be used after the error handling code?
A. To turn off the "ignore errors" mode and restore normal error handling.
B. To delete all form errors.
C. To force Access to save the record.
D. To reset all form fields.
Q10. What is the primary benefit of this new error-handling technique for entering equations in Microsoft Access forms?
A. Users can type equations directly into fields without leaving the form or using pop-up boxes.
B. All user input is automatically validated against a numeric list.
C. Form performance is greatly increased by disabling all error handling.
D. It requires users to learn VBA to enter equations.
Q11. In what case would you want to round or convert the calculated result to an integer before saving?
A. When you want to prevent fractions from appearing in the field.
B. When you want to allow decimal values.
C. When only text values should be saved.
D. When calculating dates.
Q12. According to the video, what will be the focus of the extended cut for members?
A. Making the technique work with any field and receiving notifications.
B. Removing all error handling code.
C. Limiting calculations only to number fields.
D. Turning off all input validation.
Q13. What is a key advantage of trapping form errors and handling them yourself?
A. You can provide more user-friendly error messages or custom responses.
B. You can make Access print the error to the printer.
C. Errors are always ignored and never visible to users.
D. It eliminates the need to use any form fields.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone will show you how to allow users to type equations directly into any field in your Microsoft Access forms, without requiring a separate pop-up input box.
In a previous lesson, I demonstrated a method to let users enter Excel-style equations, but it relied on a pop-up box to evaluate the calculation. Many of you reached out and asked if it was really necessary to use a pop-up every time. The answer is no, as long as you set up your form properly. In that earlier lesson, I mentioned that you could use a hidden text box to handle this, but the drawback is that you have to create one for every field where you want this feature. That quickly becomes tedious.
I realized there had to be a better solution, so I thought it through and found a way. It involves a bit of creative error handling in Access, but it is effective. The idea is to let users type whatever they want into the field. If what they enter does not conform to the field's data type, it triggers an error. We can then intercept that error, determine whether the input is something we can evaluate as a calculation, process it, and save the result, all while suppressing the default error message.
Let me walk you through the setup process.
First, let us talk about approach. Just because we find a new solution does not mean the previous method was inferior. Sometimes a new way is just a refinement or an alternate option, not a replacement of something that worked before. That's how knowledge advances.
In this new approach, you can turn off key preview on your form because you are no longer handling calculations with a key down event. If you still have code in a form key down event or if you built a function called do calculator, you can remove those. Members, if you've been following along, know we will revise this function in the Extended Cut to handle things in a more global way.
Now, configure the form's error event. Open your form properties and locate the On Error event for the form. This event activates any time an error occurs on the form, including data entry issues. The error event provides you with an error number and allows you to decide how to respond. You can either show the Access default message or handle the error yourself. In this case, we want to handle it ourselves.
I have another video where I explain how to trap form errors and give users friendly feedback instead of Access's default cryptic messages. Here, instead of displaying a message, we intend to process the entry and handle or ignore the error depending on the case.
The first step is to identify the error number that is generated when someone types a calculation into a field that expects a number. Rather than hunting through Microsoft's documentation, it is much simpler to just trigger the error and look at the code. If you enter something like "=2+8" in a numeric field, Access generates error number 2113. That is the "value you entered isn't valid for this field" error.
We do not want to intercept errors on every field, though. For this example, let us focus on one field, such as CaloriesPerUnit. (Members, we will turn this into a global solution in the Extended Cut.)
Within the error event, check if the active control is the CaloriesPerUnit field. If so, get the text that the user typed into the box, not the field value itself, because the value is not yet stored in the table after an error occurs. The text property gives you access to what was entered.
Next, try evaluating the entered text. If the result is numeric, replace the text in the box with the result. Do not attempt to set the field value at this stage, because the form is still in the process of resolving the error. Just overwrite the text, and Access will use it when saving the field.
After replacing the text, you can tell Access to continue and ignore the error using the appropriate constant. By default, Access would display the usual error message, but you want it to move on quietly.
Now, many users are accustomed to starting calculations with an equal sign. If that is the case, simply strip out the equal sign before evaluating the calculation. You can do this by checking if the first character is an equal sign and then removing it.
After saving and compiling your changes, test the new approach. Try entering "=1+3" and see the result. At first, you might still encounter an error message, because Access actually re-enters the error event during this process. To address this quirk, use error handling code that tells Access to move past any further errors generated inside the error event procedure. Make sure you turn off the error handling again at the end of the procedure so it does not affect other parts of your code.
Once that is in place, you should find that typing things like "=3+1" or even "10*3" directly into the field now calculates the total and leaves the result in the box. You can continue building on results, such as adding, multiplying, or chaining calculations, and Access will process them as you go along. The calculation updates every time you hit Enter, and the value will get saved to your table when the field loses focus.
If you do not want the result to be a fraction, you can wrap your evaluation in a rounding or integer function to produce only whole numbers.
That covers the basic technique. This method radically simplifies equation entry for your users without the need for separate pop-up boxes or hidden controls. All of the evaluation and error handling happens seamlessly in the background.
For members, the Extended Cut will demonstrate how to make this work with any field in any form and set up notifications for calculation entries.
Thank you for joining me for today's lesson. If you would like to follow along with a complete video tutorial, including step-by-step instructions for everything I discussed here, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Allowing users to type equations directly into Access form fields
Accessing and handling the Form_Error event
Determining the specific error number for invalid field data
Filtering error handling to work with a specific field
Extracting user input using the .Text property
Evaluating user-entered expressions with the Eval function
Replacing text box content with evaluated results
Removing the leading equals sign from expressions
Suppressing default Access error messages with acDataErrContinue
Using On Error Resume Next to suppress runtime errors
Restricting changes to only targeted text box fields
Converting evaluated results to integers with Int function
Allowing chained calculations by editing the text box repeatedly
Article
If you want your users to type equations directly into fields in your Microsoft Access forms, without needing any pop-up input boxes, you can accomplish this using form-level error handling. This approach allows users to enter Excel-style calculations, such as =2+8 or 5*3, straight into a field like CaloriesPerUnit, and Access will automatically evaluate the equation and store the result back in the same field. You do not need to use any hidden text boxes or complicated workarounds.
Here is how you can set this up. First, open your form in Design View and go to the properties window. Select the form itself (not a particular control), and find the On Error event. This event is triggered whenever any error occurs as a result of user data entry or other changes in the form. Click the builder button (...) to open the VBA editor and create a new event procedure.
Inside this procedure, Access will pass in the DataErr value, which is the error number, and Response, which is the way Access will handle the error. For example, if a user types something invalid into a field (like an equation in a numeric field), an error will be thrown. We want to trap that error, evaluate the equation, and if it makes sense as a calculation, save the result.
Let us focus on a field called CaloriesPerUnit as an example. First, we need to determine which error number Access throws when a calculation is typed into that field. You can discover this by adding a simple `MsgBox DataErr` line to your error handler and typing an equation into the form. You will probably find that the error number is 2113, which means the value entered is not valid for that field.
Next, we want to make sure we only apply our logic to the intended field and not every single field with invalid data. To do this, check if the current field with focus is our target field (CaloriesPerUnit):
If Screen.ActiveControl.Name = "CaloriesPerUnit" Then
Within this block, get the text the user typed into the box, not its value. The `.Text` property gives you the raw unvalidated string currently in the textbox.
Dim s As String
s = CaloriesPerUnit.Text
Many users are in the habit of typing an equal sign at the start of a calculation. You can remove that equal sign so Access can evaluate the rest of the calculation easily:
If Left(s,1) = "=" Then
s = Right(s, Len(s)-1)
End If
Now, try to evaluate the calculation using Access's `Eval` function. After evaluating, you should check if the result is numeric, and if so, store it back into the textbox:
On Error Resume Next
s = Eval(s)
On Error GoTo 0
If IsNumeric(s) Then
CaloriesPerUnit.Text = s
Response = acDataErrContinue
End If
`acDataErrContinue` tells Access not to display its standard error message dialog.
Note that after you set `CaloriesPerUnit.Text = s`, you may encounter another error even though you are inside the error handler. This is one of Access's quirks. However, simply including `On Error Resume Next` before this step handles this problem and stops Access from throwing a subsequent error.
Now save and test your form. Type in values like `=1+3` or `10*3` directly into the CaloriesPerUnit field and press Enter. Access will process the calculation and put the result into the field. You can even chain calculations as you continue to edit: type `+2`, press Enter to get 32, then `*2`, and get 64, and so on. The calculation is only committed when you exit the field (such as by pressing Tab).
If you want to make sure only integers are stored, you could wrap the result in the `Int()` function, so that instead of:
CaloriesPerUnit.Text = s
you use:
CaloriesPerUnit.Text = Int(s)
This will round down any fractions so no decimals are stored.
With this approach, you no longer need any pop-up input boxes, hidden text boxes, or extra controls. You simply allow your error handler to trap invalid field data, check for valid calculations, and if possible, store the calculated result in the intended field. Your users can now enter equations directly into any field you set up this way, making your Access forms much more flexible and user friendly.
Here is a summary of the VBA code you would place in the form's On Error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2113 Then
If Screen.ActiveControl.Name = "CaloriesPerUnit" Then
Dim s As String
s = CaloriesPerUnit.Text
If Left(s, 1) = "=" Then
s = Right(s, Len(s) - 1)
End If
On Error Resume Next
s = Eval(s)
On Error GoTo 0
If IsNumeric(s) Then
CaloriesPerUnit.Text = s
Response = acDataErrContinue
End If
End If
End If
End Sub
You can adapt this technique for other fields by repeating the relevant section or by making the field name dynamic. With this method, users can now type equations directly into your Access forms and get instant results.
|
