How to do math inside of form?

[matthewcb4]

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?

BOX INV.odb

[Ratslinger]

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

[matthewcb4]

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

[matthewcb4]

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

[Ratslinger]

@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")
	oForm.getByName("SubForm").reload()
End Sub

Attach to When losing focus event of list box.

[Ratslinger]

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 dAdj         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
    dAdj = result.getDouble(1)
    dNet = dQUsed - dAdj
    oForm.nfCalcWeight.Value = dNet
    oColumns = oForm.getColumns()
    oQuantity = oColumns.getByName("QUANTITY")
    oQuantity.updateDouble(dNet)
End Sub

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

[Ratslinger]

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.

[Ratslinger]

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.

[matthewcb4]

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.

[matthewcb4]

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!