Page 1 of 1

[Solved] Text boxes in Base form with their values tallied

PostPosted: Fri Mar 01, 2019 1:53 am
by weaverman55
First off i'm a senior citizen with limited computer knowledge. I have been able to set up a database table and a form associated to it. On this form i have 3 text boxes assigned for dollar amounts to be entered. Is it possible to have the 3 box values tallied and displayed into a 4th text box via a button using a Macro. The first text box(Amount) will always be filled with a dollar amount and the next 2 (Shipping Cost) & (Fees) may or may not have a dollar amount entered. The 4th box would be labeled NetProfit so i would like to have (Shipping Cost) & (Fees) subtracted from Amount for the net profit value.
This is the code I've written thus far with not knowing if it is even close to what i need for a Macro. so any help or direction would be greatly appreciated. Thanking you in advance for any consideration.

Code: Select all   Expand viewCollapse view
Option Explicit
Sub CalculateNetProfit
    Dim oForm                 As Object
    Dim oColumns           As Object   
    Dim dAmount             As Double
    Dim dShippingCost    As Double
    Dim dFees                    As  Double      
    Dim dBaseNetProfit     As Double
Rem Gets Main internal form
    oForm = ThisComponent.Drawpage.Forms.getByName(“MainForm”) ‘Get Form
Rem Check if textboxes have value
     dAmount = oForm.getByName(“Amount”).getCurrentvalue()
     dShippingCost = oForm.getByName(“Shipping Cost”).getCurrentValue()
     dFees = oForm.getByName(“Fees”).getCurrentValue()
Rem Initial netprofit
     dBaseNetProfit = 0.00
  Rem Add NetProfit if value
      if dAmount Then
          dBaseNetProfit = dBaseAmount
      End if
      if dShippingCost Then
          dBaseNetProfit = dBaseAmount – dBaseShippingCost
      Else
          dBaseNetProfit = dBaseAmount
      End if
      if dFees Then
          dBaseNetProfit = dBaseAmount - dbaseShippingCost – dBaseFees
      Else
          dBaseNetProfit = dBaseAmount
      End if
    Rem Obtain and update NetProfit
        oColumns = oForm.getColumns()
        oColumns.getByName(“NetProfit”).updateDouble(dBaseNetProfit)
    End Sub

Re: Text boxes in base form with their values tallied

PostPosted: Fri Mar 01, 2019 3:41 pm
by Villeroy
You can calculate easily with SQL. No need to use any macros.

Horizontal calculation: SELECT *, "Count" * "Price" - "Cost" AS "Profit" FROM "Sales"
selects all columns (*) from table sales and multiplies the field "Count" with the field "Price" subtracts the field "Cost" and displays the result in a field labeled "Profit".

Vertical calculation: SELECT "Product", "Shop", SUM("Profit") AS "Sum(Profit)" FROM "Query" GROUP BY "Product", "Shop"
calculates the profits of all combinations of products and shops from above query.

Both merged into one query with highest profits on top:
Code: Select all   Expand viewCollapse view
SELECT "Q"."Product", "Q"."Shop", SUM("Q"."Profit") AS "Sum(Profit)"
FROM (SELECT *, "Count" * "Price" - "Cost" AS "Profit" FROM "Sales") AS "Q"   
GROUP BY "Q"."Product", "Q"."Shop"
ORDER BY "Sum(Profit)" DESC

Re: Text boxes in base form with their values tallied

PostPosted: Fri Mar 01, 2019 4:49 pm
by weaverman55
My apologizes if i didn't make my question clear, i want the text box (NetProfit) populated after i have entered all the information into the other 3 boxes (Amount), (Shipping Cost),and (Fees). So then i can save the info into the database.
Again this is all extremely new to me but i am trying to learn. Thanking you in advance.

Re: Text boxes in base form with their values tallied

PostPosted: Fri Mar 01, 2019 5:15 pm
by UnklDonald418
Look at this example and see if it shows you a way forward.
[Example] Invoice Forms (without macros)

Re: Text boxes in base form with their values tallied

PostPosted: Fri Mar 01, 2019 5:26 pm
by Villeroy
Create a query, make a form from that query and edit the numeric fields. The calculated field can't be edited. It shows the result.

Re: Text boxes in base form with their values tallied

PostPosted: Sat Mar 02, 2019 2:16 pm
by Villeroy
download/file.php?id=17721 is one of my earliest database examples on this forum. It calculates volumes of cuboids by formula Height x Width x Depth. The first example with the subform is not particularly helpful in respect to the calculation task. The simpler forms #2 and #3 are both based on Query2.

SQL is the programming language which lets you create databases, manipulate database data and get meaningful information out of data. In the latter case (pulling information out of data) SQL SELECT statements are somewhat similar to spreadsheet formulas. An SQL SELECT statement (a query) returns a row set wich can be seen as a table derived from one or more other tables, whereas most spreadsheet formulas return a single value.

Contrary to the application programming interface of this office suite (where the forms section is the most incomprehensible part) you can learn the basics of SQL within a weekend or two. The database program you are actually working with is a stand-alone product with relevant documentation on this page: http://www.hsqldb.org/doc/1.8/guide/ch09.html
There are plenty of books and online tutorials on SQL. Since the 70ies empires have been built on SQL. There are several SQL dialects but the concept is always the same.

Re: Text boxes in base form with their values tallied

PostPosted: Sat Mar 02, 2019 4:09 pm
by weaverman55
Thanks for all you help.

Re: Text boxes in base form with their values tallied

PostPosted: Sat Mar 02, 2019 5:39 pm
by UnklDonald418
Those new to databases often have to overcome a spreadsheet mentality.
If you looked at the example invoicing database you would see that on the forms, each row has an extended price and the invoice has a grand total, but if you look in the INVOICE table none of those values can be found.
When working with databases, storing a value that can be easily calculated is considered bad form. Values like an extended price, totals on an invoice and for that matter profit on a sale should not appear in a table.
One problem with storing a calculated value in a table there is the possibility of having discrepancies which can be difficult to resolve.
Another problem, which may not be a big issue with your database, but in a database with many thousands or millions of rows the extra few bytes of storage required for that field can become significant.

Re: Text boxes in base form with their values tallied

PostPosted: Sat Mar 02, 2019 6:09 pm
by weaverman55
Thanks, I am learning so much.