# How to do math inside of form?

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hello All, I have been creating an inventory database for a small company that I started working at. This is a seed company and we store our product in steel boxes. As these boxes with product go through the plant they can gain and lose weight depending on the process. I have a database created with 4 tables. 1 with the product numbers and descriptions. 1 with locations in the plant where they can be stored. 1 with the boxes and the last one that stores all of the weight and location adjustments to each box based on the product number and box number. All of the relations are set up and working great.

The issue: I created a form that I use when the product completes from a process and it works well enough. The problem is that when I am updating the weights of the boxes I have to figure out the math myself from the start weight of the box to the output weight.

Ex: 3100 lbs to start 2900 lbs after/ for this example I would have to figure out the difference and enter -200 in the field.

I have been trying for days to figure out how to make this entry be something like this example: 3100 lbs to start 2900 lbs after/ enter 2900 and then it will populate the adj table with the -200.

Thank you for any and all help! (also, I am not very well versed in macros so my thought was just to populate a variable that is then passed to the table but have no clue how to accomplish this.)

EDIT: Hello and thank you. I have attempted to attach my file. The structure for the file is this:

My form "MISC INV UPDATE" is updating the ADJ file with the data in the populated fields. The issue is that I am having to do the math to get the difference.

(From previous example) What I am shooting for is instead of having to enter "-200" in the weight column I would like to be able to enter "2900" and have a script or something run that does the math and enter's "-200" in the ADJ table for me.

Does that make any more sense?

C:\fakepath\BOX INV.odb

edit retag close merge delete

@matthewcb4 Please do not post questions as wiki. It helps no one.

( 2018-08-10 20:03:21 +0200 )edit

Got-it, sorry didn't know what that meant.

( 2018-08-10 20:21:52 +0200 )edit

Hello Ratslinger, would you be able to help with 1 more issue I have. Everything works great but it would be awesome if I could possibly get a macro to update my subform once I leave the "LOT" field. I have tried to write a macro based on others examples but something is not working properly.

Sub RefreshMainTable Dim frm,frm1, frm2, oList frm=ThisComponent.Drawpage.Forms frm1=frm.getByName("MainForm") oList = frm1.getByName("txtLOT") oList.commit frm2=frm1.getByName("SubForm") frm2.reload

( 2018-08-23 20:30:50 +0200 )edit

@matthewcb4 In future please post as new question as it is different from original post and hidden from others looking for similar answer.

Sub RefreshMainTable
Dim oForm As Object
oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
End Sub


Attach to When losing focus event of list box.

( 2018-08-23 22:24:31 +0200 )edit

Sort by » oldest newest most voted

Hello @matthewcb4,

Normally you would do this using SQL. I am not certain what result you are looking for and where and how you have all stored. You state a table which has all weight adjustments. Seems in SQL get original weight, add/subtract adjustment(s) and get result.

  Select OriginalWeight - Adj1 - Adj2 As FinalWeight From MyTable(s)


Also not clear what you are doing with the result. If placing in another field that is typically not done since the data can be gotten anytime from data on hand unless these adjustments vary (again not clear).

Possibly need to provide more on your process.

Edit:

After spending needless hours on this, attached is a sample. It works but in my opinion this entire Base file needs a great amount of work. The actual code only took less than 30 minutes. The rest of the time was spent trying to figure out what you had. You never answered questions presented, control names were duplicated, queries have multiple columns output with same name, there is no consistency on naming conventions, forms are difficult to work with and more. This really needs a major re-design & re-write.

In the form itself have added a new Numeric Field to hold the calculated result & the figure is used to update the ADJ table. The QUANTITY US is just for entry of the "2900" Weight thing? (no answer there either) and once you exit the field the calculated amount will appear in the new control with title of Weight Diff.

The macro is in the document and is attached to the When losing focus event of your fmtQUANTITYUSED control.

Here is the macro code used:

Option Explicit

Sub CalculateWeight

Dim oForm        As Object
Dim oColumns     As Object
Dim oStatement   As Object
Dim oQuantity    As Object
Dim dQUsed       AS Double
Dim dLot         AS Double
Dim dBox         AS Double
Dim dNet         AS Double
Dim sSQL         AS STRING
Dim result
Dim CursorTest
oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
dQUsed = oForm.fmtQUANTITYUSED.Text
dLot = oForm.txtLOT.SelectedValue
dBox = oForm.lbBOX.SelectedValue
If dLot = 0 Then
MsgBox "Nothing selected in LOT!"
Exit Sub
End If
If dBox = 0 Then
MsgBox "Nothing selected in BOX!"
Exit Sub
End If
if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
Thisdatabasedocument.CurrentController.connect
endif
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
sSQL = "Select Sum(""QUANTITY"") from ""ADJ"" where ""LOT"" = '" & dLot & "' AND ""BOX"" = '" & dBox & "'"
result = oStatement.executeQuery(sSQL)
'CHEK IF ANY RECORDS RETURNED'
CursorTest = result.first
If CursorTest = "False" Then
MsgBox "No Records"
Exit Sub
End If
oForm.nfCalcWeight.Value = dNet
oColumns = oForm.getColumns()
oQuantity = oColumns.getByName("QUANTITY")
oQuantity.updateDouble(dNet)
End Sub


Sample with macro included ----- BoxInv.odb

more

What still has me a bit confused is this term weight since you circled Quantity on the form and it updates a field named Quantity. Also where did you get 2900 from? Is this the box weight? If so, why isn't it part of the Box table? Seems this could all be a lot more automated.

( 2018-08-10 21:33:46 +0200 )edit

I think more to the original question, and in general terms, to perform math within a form will need macro specific to your needs. For an example of this see this post -> Base Form data uneditable and check out the samples.

( 2018-08-10 22:25:14 +0200 )edit

Hello and thanks again. The "BOX" table only has the box numbers and the light weight/tare weight. I want to retain a transaction history on the box weight and what has been done to it. This is why I am not just changing the weight of the box in the BOX table. I am keeping a record of all of the transactions to each box so that I can have a running balance based on accumulation. So by doing it this way, to adjust the boxes weight I have to enter in the difference instead of the new total.

( 2018-08-11 00:05:15 +0200 )edit

Hello again and thank you so much for all of your work on this. That worked perfectly. I am sorry for the confusing form. I didn't expect to include all of those fields when I first started on this so I didn't bother with renaming. I am still getting comfortable with sql and didn't really have the time to learn enough about macros to accomplish this on my own. But thanks again, you have really saved me a lot of time. This is for agriculture and we are going to be harvesting soon so huge THANKS!

( 2018-08-14 21:51:20 +0200 )edit