Get a textbox to behave like an excel cell.

[raziel3]

Hello all,
Is there a module or something available that allows the user to do simple calculation in a text box? For example, if I enter 1+1 in a textbox the textbox value becomes 2.

Nothing too elaborate just +, -, x, ÷

 

[CJ_London]

Depends if the text box is bound or not and if bound the type of field it is bound to ~ it would have to be text

but the principle is in the controls after update event put

Controlname=eval(controlname)

 

[MajP]

Would a simple pop up calculator work? I have one you can click in a field and do the calculations, returning the value back to the control.

 

[Pat Hartman]

Keep in mind that making Access work like Excel will be frustrating. it will also result in the field not being bound and therefore not saved when the record is saved.

There are far better methods. @MajP gave you one.

 

[Edgar_]

In Excel, calculations initiate when the user types an equals sign in a cell before entering the formula. Similarly, in this code, typing the equals sign in the textbox triggers it to disconnect from its data source in order to avoid conflict with the operators you will use for your calculation. To visually indicate this change, the textbox adjusts its border style, providing feedback on the disconnection. Subsequently, after entering a calculation and pressing Enter, the result is stored in a public variable using the AfterUpdate event of the textbox. Additionally, the KeyPress event rebinds the control, adjusts the border style again, and finally writes the previously stored calculation.

The code and this explanation captures the concept, but you may need to account for other events in your implementation.

Option Compare Database
Option Explicit

Private calc As Double

Private Sub CalculationTextbox_AfterUpdate()
    calc = Eval(Mid(Me.CalculationTextbox.Text, 2)) 'Mid removes the equals sign
End Sub

Private Sub CalculationTextbox_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 61 'equals
            Me.CalculationTextbox.ControlSource = ""
            Me.CalculationTextbox.BorderStyle = 2
        Case 13 'enter
            Me.CalculationTextbox.ControlSource = "Calculation"
            Me.CalculationTextbox.BorderStyle = 1
            Me.CalculationTextbox = calc
    End Select
End Sub

EDIT AFTER KITAYAMA's OBSERVATION:
This requires the following setting:
Options > Client Settings > Move after enter > Don't Move

Note: In my tests, you can still make it work without changing the previous option, but it requires these extra steps:
1. Setting the Form's cycle event to Current record
2. Pressing Enter key again and again until you get to the calculation textbox, only then will the Enter key be detected correctly.

Please check post #10 on how to address this in a better way.

 

[raziel3]

In Excel, calculations initiate when the user types an equals sign in a cell before entering the formula. Similarly, in this code, typing the equals sign in the textbox triggers it to disconnect from its data source. To visually indicate this change, the textbox adjusts its border style, providing feedback on the disconnection. Subsequently, after entering a calculation and pressing Enter, the result is stored in a public variable using the AfterUpdate event of the textbox. Additionally, the KeyPress event rebinds the control, adjusts the border style again, and finally writes the previously stored calculation.

The code and this explanation captures the concept, but you may need to account for other events in your implementation.

Option Compare Database
Option Explicit

Private calc As Double

Private Sub CalculationTextbox_AfterUpdate()
    calc = Eval(Mid(Me.CalculationTextbox.Text, 2)) 'Mid removes the equals sign
End Sub

Private Sub CalculationTextbox_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 61 'equals
            Me.CalculationTextbox.ControlSource = ""
            Me.CalculationTextbox.BorderStyle = 2
        Case 13 'enter
            Me.CalculationTextbox.ControlSource = "Calculation"
            Me.CalculationTextbox.BorderStyle = 1
            Me.CalculationTextbox = calc
    End Select
End Sub

I think this together with @MajP calculator app can get me started on what I want to do.

Thanks.

 

[MajP]

The calculator app is not mine, but I built the wrapper function to make calling it easier. It comes from

M

Thread 'Popup Calculator c/w example use'

Mar 9, 2006

The demo shows how to use the calculator in a database application.

The table used in the application only contains a Numeric field and is bound to the text control on the form. To activate the calculator, simply double click on the text box. If the text box is blank, the calculator will open with no value displayed, otherwise the calculator will open with the value already stored in the text box.

The user can use the numeric keypad as well as the calculator.


Runs with Access 2000

• MStCyr
• Replies: 0
• Forum: Sample Databases

I thought I had a more modern looking one too, but cannot find it.

 

[Uncle Gizmo]

I haven't studied the code but I've never seen this particular way of opening a pop-up form before I thought it was very clever!

=PopupCalc([Screen].[ActiveControl])

 

[KitaYama]

In Excel, calculations initiate when the user types an equals sign in a cell before entering the formula. Similarly, in this code, typing the equals sign in the textbox triggers it to disconnect from its data source in order to avoid conflict with the operators you will use for your calculation. To visually indicate this change, the textbox adjusts its border style, providing feedback on the disconnection. Subsequently, after entering a calculation and pressing Enter, the result is stored in a public variable using the AfterUpdate event of the textbox. Additionally, the KeyPress event rebinds the control, adjusts the border style again, and finally writes the previously stored calculation.

The code and this explanation captures the concept, but you may need to account for other events in your implementation.

Option Compare Database
Option Explicit

Private calc As Double

Private Sub CalculationTextbox_AfterUpdate()
    calc = Eval(Mid(Me.CalculationTextbox.Text, 2)) 'Mid removes the equals sign
End Sub

Private Sub CalculationTextbox_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 61 'equals
            Me.CalculationTextbox.ControlSource = ""
            Me.CalculationTextbox.BorderStyle = 2
        Case 13 'enter
            Me.CalculationTextbox.ControlSource = "Calculation"
            Me.CalculationTextbox.BorderStyle = 1
            Me.CalculationTextbox = calc
    End Select
End Sub

@Edgar_ Does the sample file you attached, works for you.
I had an OLE error with your text box.
I deleted the textbox, add a new one, renamed it and added the events. Everything's fine up to when Enter is pressed.
Pressing Enter does nothing. The textbox remains the same.
( For some reasons beyond my knowledge, Enter key's Ascii is 6 for me. I tested both with 6 and 13. But nothing happens.
Here's what I have.

Thanks.

 

[Edgar_]

@Edgar_ Does the sample file you attached, works for you.
I had an OLE error with your text box.
I deleted the textbox, add a new one, renamed it and added the events. Everything's fine up to when Enter is pressed.
Pressing Enter does nothing. The textbox remains the same.
( For some reasons beyond my knowledge, Enter key's Ascii is 6 for me. I tested both with 6 and 13. But nothing happens.
Here's what I have.

Thanks.

I checked in another machine with another version of MS Access and I could not reproduce the OLE error, but I did find the culprit for the Enter key acting up against this method. The problem was in
Options > Client Settings > Move after enter > Next field
After setting it to Don't move, the method worked on that machine

However, I understand that others may not want to change that behavior. For those of you who enjoy going to the next field after pressing the Enter key, moving the calculation and rebinding behavior to the AfterUpdate event would allow you to still get the calculation done. In fact, that behavior resembles Excel even more and it looks like this:

Option Compare Database
Option Explicit

Private calc As Double

Private Sub CalculationTextbox_AfterUpdate()
    If Left(Me.CalculationTextbox, 1) = "=" Then
        calc = Eval(Mid(Me.CalculationTextbox.Text, 2))
        Me.CalculationTextbox.ControlSource = "Calculation"
        Me.CalculationTextbox.BorderStyle = 1
        Me.CalculationTextbox = calc
    End If
End Sub

Private Sub CalculationTextbox_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 61 'equals
            Me.CalculationTextbox.ControlSource = ""
            Me.CalculationTextbox.BorderStyle = 2
    End Select
End Sub

Now that we're going into further details, it will probably be beneficial to also set the Form's Cycle property to Current Record

EDIT: I added a condition so that the calculation is done only if there is an equal sign, otherwise, if the value was entered directly into the textbox, the code was going to remove the first character. Many other ways exist to handle this, I'm just doing the simplest I could think of for now.

 

[KitaYama]

I checked in another machine with another version of MS Access and I could not reproduce the OLE error, but I did find the culprit for the Enter key acting up against this method. The problem was in
Options > Client Settings > Move after enter > Next field
After setting it to Don't move, the method worked on that machine

View attachment 115087

However, I understand that others may not want to change that behavior. For those of you who enjoy going to the next field after pressing the Enter key, moving the calculation and rebinding behavior to the AfterUpdate event would allow you to still get the calculation done. In fact, that behavior resembles Excel even more and it looks like this:

Option Compare Database
Option Explicit

Private calc As Double

Private Sub CalculationTextbox_AfterUpdate()
    If Left(Me.CalculationTextbox, 1) = "=" Then
        calc = Eval(Mid(Me.CalculationTextbox.Text, 2))
        Me.CalculationTextbox.ControlSource = "Calculation"
        Me.CalculationTextbox.BorderStyle = 1
        Me.CalculationTextbox = calc
    End If
End Sub

Private Sub CalculationTextbox_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case 61 'equals
            Me.CalculationTextbox.ControlSource = ""
            Me.CalculationTextbox.BorderStyle = 2
    End Select
End Sub

Now that we're going into further details, it will probably be beneficial to also set the Form's Cycle property to Current Record
View attachment 115089

EDIT: I added a condition so that the calculation is done only if there is an equal sign, otherwise, if the value was entered directly into the textbox, the code was going to remove the first character. Many other ways exist to handle this, I'm just doing the simplest I could think of for now.

Thanks for checking.
I just checked your new version.
No error and the enter key works as it should.

But there's a major problem.
Open the form , type =10+10 or anything, Press Enter Key. The equation is calculated and it replaces to 20.
So far, fine.

Now move to the next record. =10+10 is sitting there.
While in the new record, type something in SomethingTitle textbox, now you receive an error telling you =10+10 is not suitable for your bound field as soon as the form gets dirty.
I know I can add some more code to clear it and I may face some other typical problems, in addition to as you guessed, sacrificing how Enter key behaves, or keeping the form on current record which needs a button to save the record, and possibly more of this kind, is too much price for too little gain.

I'm not criticizing you and I also know you're just trying to find a solution for what the OP has asked for.
For now, I will stick to what I have at present (a separate un-bound textbox for calculation.)

Now, I think I can understand what @Pat Hartman meant in #4

Million thanks for your time and help.

 

[Edgar_]

a separate un-bound textbox for calculation

That is a good approach.

Now move to the next record. =10+10 is sitting there.

I'll check why that is. I suspect the old value is at it.

 

[DickyP]

As a matter of pure curiosity why would you want to do a calculation in a cell?

 

[KitaYama]

As a matter of pure curiosity why would you want to do a calculation in a cell?

I'm not the OP and I can't answer on his behalf. He may have more powerful reasons.
In my case, there are different cases and if I go into depth, it would be very confusing. Just a simple one.

I'm trying to order 178 sheets of a specific material. I ask for a quote and the quote I receive contains the total price.
Apparently In a database, we don't save the total, but quantity and price per unit.
Well, when I want to issue the order, I have to take a calculator and divide the total price by the sheets' quantity to have the per unit price. For me it makes more sense to do it in a textbox in a form, rather than using a calc.
It's only the tip of the problem. There are also other concerns when the mass, weight and other stuff come to play,

 

[CJ_London]

I use the principle in a stock taking form where I might have in one location 2 boxes of 3, 5 boxes of 12 and 1 on its own. But I store the calc in a bound text field and use eval to populate a quantity field.

In another case where location is not a requirement user might enter 6, then further along the stocktake they find another 3

I keep the calc so it is easier to work out where an issue is if there are reconciliation problems

Principle has also been used when creating a delivery note or returns note

 

[DickyP]

I'm not the OP and I can't answer on his behalf. He may have more powerful reasons.
In my case, there are different cases and if I go into depth, it would be very confusing. Just a simple one.

I'm trying to order 178 sheets of a specific material. I ask for a quote and the quote I receive contains the total price.
Apparently In a database, we don't save the total, but quantity and price per unit.
Well, when I want to issue the order, I have to take a calculator and divide the total price by the sheets' quantity to have the per unit price. For me it makes more sense to do it in a textbox in a form, rather than using a calc.
It's only the tip of the problem. There are also other concerns when the mass, weight and other stuff come to play,

I use the principle in a stock taking form where I might have in one location 2 boxes of 3, 5 boxes of 12 and 1 on its own. But I store the calc in a bound text field and use eval to populate a quantity field.

In another case where location is not a requirement user might enter 6, then further along the stocktake they find another 3

I keep the calc so it is easier to work out where an issue is if there are reconciliation problems

Principle has also been used when creating a delivery note or returns note

Cheers - thanks guys - makes sense.

 

[raziel3]

You are both right @CJ_London and @KitaYama I am using it in an Inventory, Purchase order and Purchase Forms. Normally I have a calculator on my side.

Vendor bills are formatted differently so when I am entering it in the database, which is quantity and total cost less vat/taxes, I need a calculator to find out cost less taxes. Bills usually come like this:

-Item, Quantity, Vat Inclusive Total
-Item, Quantity, Vat Exclusive Total

So when I'm entering the total say $525.00 for 20 units from a vat inclusive bill I was planning just to input "=525/1.125" in the Cost Field.

 

[Mike Krailo]

I guess I still don't understand the application for this functionality. If I'm doing a purchase order and buy x amount of things and there is a total cost involved, I'm going to put the total cost into system and not the cost per item. You could certainly calculate the cost per item from the total, but for a simple purchase order I would just put the total expenditure in the database and be done with it. Your not breaking any rules by doing it that way.

I would assume that when you get a quote that it does not include any tax, correct? You can calculate that yourself by including the current rate. If you are always doing a similar calculation as in your example, just enter the quoted total in your cost per item box and use a button to do the calculation as in this example.

You can still use the excel like features as well, but if it's always going to be like your example, why not make it even easier to enter. I set it up so that it handles divide by zero errors and only does a calculation if the record is dirty, that way repeated button presses do not keep recalculating the value down to very small inaccurate numbers. So just enter the total from the quote and press the button.

 

[raziel3]

I guess I still don't understand the application for this functionality.

The idea is that when an invoice comes in with the vat inclusive total, use the functionality to get the vat exclusive total for that field.

It seems pretty unnecessary but an invoice with 20 line items, switching between a calculator and the database can sometimes become tedious.

@Edgar_ that works great so far. (Still testing)

 

[Mike Krailo]

Wow, those are messed up invoices then. The normal way an invoice should be is to have no tax in the numbers for items until the very end so you can easily see the difference. So in addition to getting the price per unit, you need to remove the vat tax as well. That sucks but I would still try make something up such that you put their numbers in, and your desired values are calculated out and entered into your database. A custom calculator form or the button method I provided with a modification to account for stripping the tax out of the total first. It's up to you.