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

Creating and using forms
Post Reply
weaverman55
Posts: 5
Joined: Fri Mar 01, 2019 1:20 am

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

Post 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

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
Last edited by Hagar Delest on Sat Mar 02, 2019 8:15 pm, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1.6
OS X 10.14.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text boxes in base form with their values tallied

Post 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

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
weaverman55
Posts: 5
Joined: Fri Mar 01, 2019 1:20 am

Re: Text boxes in base form with their values tallied

Post 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.
OpenOffice 4.1.6
OS X 10.14.3
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Text boxes in base form with their values tallied

Post by UnklDonald418 »

Look at this example and see if it shows you a way forward.
[Example] Invoice Forms (without macros)
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text boxes in base form with their values tallied

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Text boxes in base form with their values tallied

Post 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.
Last edited by Villeroy on Sat Mar 02, 2019 4:16 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
weaverman55
Posts: 5
Joined: Fri Mar 01, 2019 1:20 am

Re: Text boxes in base form with their values tallied

Post by weaverman55 »

Thanks for all you help.
OpenOffice 4.1.6
OS X 10.14.3
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Text boxes in base form with their values tallied

Post 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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
weaverman55
Posts: 5
Joined: Fri Mar 01, 2019 1:20 am

Re: Text boxes in base form with their values tallied

Post by weaverman55 »

Thanks, I am learning so much.
OpenOffice 4.1.6
OS X 10.14.3
Post Reply