Updating inventory from form

[jorr]

Hello,
I am trying to build a simple inventory/materials database. The major objective is to be able to pull up an item number, see the current quantity I have and then choose to use a certain amount which needs to be deducted from what I'm showing as current quantity level.

I'm having a hard time figuring out how to do the update part. My thought was to do one table that would hold item #, description, quantity, etc. I would just need a text box on my form that would let me enter a quantity to use, then click an update button that would deduct that amount from what is currently stored in the quantity field.

Any suggestions on how to do this?

Thanks.

[reignlucas]

Hi darling,

Try messing with the list box features to be able to add/subtract the amounts needed from the rest of the balance. I believe it's a listbox which would fit your needs. Hopefully, someone else will come along and offer a bit more insite on how to impliment a list box into your form and update the table information. I wish I was more help, but I'm literally 1 day old in the open office world.

good luck!

[voobase]

Hi there,

How did you go with this one?

I think you could achieve what you need to do with a fairly simple macro. First, here are a quick list of some things that might assist you in understanding the way Base works (if you are brand new to Base)...


Base Tips...
Create your tables with "design view", not the wizard. After hitting save, Base will ask to add a primary key. Say yes and click back on that field and change it to "auto increment" down the bottom. If you have a second table that you may wish to use in a subform, don't forget to add an extra field to be a foreign key field of type integer.

Use the "form wizard" to start off the design of your form. It allows you to also create a subform if required. The wizard will then allow you to link the Mainform "ID" field to the SubForm "Foreign_Key" field

To edit your form in "design mode" right click the form in the GUI and select "edit".

To toggle "design mode" on and off, there is a button on the "Form design" toolbar that looks like a "set square".

When a form is created with the "wizard" and you work on it in "design mode", the wizard will have grouped the label with the control. This means that when you right click on a control to get to its properties, only those which are common to both it and the label are displayed. To see all the properties you must either "ungroup" them (right click menu) or Ctrl click the control when selecting it. An even better way to see the full properties is to get used to using the "form navigator".

As just mentioned, there is a good button to get to know and it is called the "form navigator". It is three buttons to the right of the "design mode on/off" button. It displays all the forms and controls that exist in your "writer doc" form in a "tree like" view. You can then easily right click on things to get to their properties. You can also add in extra mainforms and subforms with this tool.

When looking at the properties of a control or form there is an "events" tab. This is how you trigger your macro code.

The short cut to get to your macro code is Alt F11.

Make sure your Data Base is registered with OpenOffice (Especially if you have changed its name using "Save As" at any stage). Go To Tools>Options>OpenOffice.Base>Databases and select where you saved the file.

OK, now the bits for your form and the macro you will need....

From the "form control" toolbar, draw a text box and a push button on to your form.

First it is important to edit the name (not label) of the "Subtract" text box, so that we can address it from our macro code. Using one of the methods from above, go to the properties of the text box and select the general tab. Where it says "name" put in the value "tbSubtract_Box". When we use this name in the macro code we will need to remember that it is case sensitive. While you are at it check what the name of your "current quantities" textbox the same way. If you are using a "table grid" right click up the top and choose "control" and check the name in the same way. In the code I have used the name "tbTotals".

The macro will need to get the value that you have in the field which contains the "totals", subtract the amount in the text box and then update the "totals" field in your form again. The macro will be slightly different, depending on whether you are using a "table grid" in your form or have chosen text box fields. You will also need to supply the correct name of the field that contains the "totals". (remembering it will be case sensetive)

Code: Select all

Sub Subtract_Value (oEv as object)

dim oForm as object
dim varTotal as integer
dim varSubtract as integer
dim varResult as integer

oForm = oEv.source.model.parent
    Rem... un-comment the one below which is more relevant for you. Make sure you use the correct names from your form.
'oTotals_Control = oForm.GetByName("tbTotals") Rem... for a text box field that has the name "tbTotals"
oTotals_Control = oForm.GetByName("MainForm_Grid").GetByName("tbTotals") Rem... for a text box field in a TableControl called "TableControl" that has the name "tbTotals"
oSubtract_Box = oForm.GetByName("tbSubtract_Box")

Rem... Get the values and put them in variables.
varTotal = oTotals_Control.CurrentValue
varSubtract = oSubtract_Box.CurrentValue
varResult = (VarTotal - varSubtract)

Rem... Put the result back in the totals box
oTotals_Control.BoundField.UpdateInt(varResult)

Rem...Save the form
If oForm.IsNew then
oForm.InsertRow
Else
oForm.UpdateRow()
End if

End sub

There are a couple of places you can put the macro. It is possible to have it with the form or kept in your macro library (MyMacros) which is part of the OpenOffice install. The problem with keeping it with the form is that you may get a macro warning each time the form is opened. The problem with keeping it in "MyMacros" is that the macro file dosen't travel with your database file. In either case you will need to go to the properties of the "pushbutton" in your form and go to the "events" tab. You will need to choose the [...] next to the "when Initiating" event and then push the "macro" button and locate where the macro is put.

I have whipped up a very quick demo database for you. You will need to save it somewhere then make sure that you register it with OpenOffice. To do this go to Tools>Options>OpenOffice.Base>Databases and select where you saved the file. In this case I have saved the macro file in the form.

Let me know how you go...

Cheers

Voo

[Villeroy]

There are a couple of places you can put the macro.

Version 3 allows macros defined for the database container. So database specific macros work like all other macros embedded in documents.

[voobase]

Hi Villeroy,

I guess that would mean it shouldn't get the macro warning then. Is that what you have heard?

Voo

[vsp]

OK, so I'm using 3.2.1 and this seemed to solve a problem I had, only it does some very peculiar things indeed!

I have three fields - price, qty and total. When the user changes price or qty a macro fires up which gets the price and qty (successfully) on the grid and calculates total. But when it puts total back, the field I entered is immediately cleared!

[vsp]

The code is thus:

oDoc=thiscomponent.drawpage.forms
oForm=oDoc.getbyname("MainForm")
oSubForm=oForm.getbyname("SubForm")
oSubFormGrid=oSubForm.getbyname("SubForm_Grid")

oPrice = oSubFormGrid.GetByName("DETL_UNITPRICE")
oQty = oSubFormGrid.GetByName("DETL_QTY")
oTotal = oSubFormGrid.GetByName("DETL_TOTAL")

nQty = val(oQty.text)
nPrice = val(oPrice.text)
nTotal = nQTY * nPrice

sTotal = "" & nTotal
' oTotal.value = sTotal
'msgbox sTotal
' oTotal.updatestring(sTotal)
oTotal.BoundField.UpdateInt(nTotal)

[vsp]

OK, hopefully this might help others.....

The macro was on a change-of-text event but I moved it to a lose-focus event and now it all works perfectly. I can't explain the logic except that somehow something must have been getting screwed up in the database handling logic.....

[ouchd'greyt]

HI,

I think this code i'm looking for, on how to update/subtract the form using other textbox, but what if the value you are subtracting for on the form already reached the 0 value that should have a warning message says "Value already 0" when there' another attempt for subtracting the 0 value?.


Thanks