quantity times by pcs in box

[JULIORUIZ2600]

Hi,

i am trying to modify the inventory management of microsoft access the following formula on the fiel quantity on hand, to keep track of inventory:

=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))

the problem i have is,

that i have a fiel call qty, for example,

Soap come in box and each box has 200pcs,

so how can i modify the above formula so it can times either received or sold by the quantity of the item?

Thank you

Note: for example if i type 2 on received field of a item that have 200 pcs, so the result should be 2x200 = 400.

thanks for your help.

 

[kjv1611]

If I understand what you want correctly, then you might could put the VBA code for this into an event procedure (such as a button, or the AfterUpdate event for one of the first text boxes). So, for example:

Assume we have the text boxes, txtQuantity, txtPcs, txtResult to go with your example.

Let's say you first input the Quantity, and then the Pieces for each order or whatever.

So, the order of updates would be Quantity, Pieces, and of course, Results from that input.

Next, we would create a short bit of code to do the computations:

Right-click on the text box, txtPcs, and choose "Properties", then on the "Event" tab, find the "After Update" event, and click the build button next to that field.

In the VBA window that pops up, edit the code, so it looks something like this:

Private Sub txtPcs_AfterUpdate()
  txtResult = txtQuantity * txtPcs
End Sub

Of course, you may also want to add some error handling, and possibly some conditions based on what could be input, but that's a general idea, I think, for what you are trying to do.

 

[JULIORUIZ2600]

Hi
thanks for your reply.

will that keep running balance?

as i want to keep onhand balance uptodate.

i have a received, sold and quantity field right now, and then the formula right now is:=Sum(nz([UnitsOrdered])-nz([UnitsReceived])) and it keep runing balanceo of received and sold transaction.

what i want is being able to used the received and sold field to times by the unit of box, some product has 200pcs or 15pcs times it by 6 on received/sold will be 1200/90 and keep balance.

thanks again

 

[SkipVought]

Hi,

What you need for each inventory id is a conversion factor.

You receive 2 large boxes of wiggies and 3 small boxes of wiggies.

Wigies come in 2 sizes: 100 wiggies per box and 500 wiggies per box so you must have a spearate inventory id for each type of box and a separate conversion factor for each.

So your initial wiggies inventory would be
2 * 500 + 3 * 100

You might receive a ROLL or SHEET of something and use it by the SQUARE INCH. So the Conversion factor would be x square inches per roll or sheet.

Skip,

_[red][/red]

 

[JULIORUIZ2600]

hI,

i have a combo product, but when i start the form the field is in blank, how can i make it to have the first product of the list display on the combo box, (by the way is unbound combo?

also how can i add a category box so it change to the corresponding category when i select the product from the above unbound combo?

categories for example, soap, category = cleaning supplies,

paper towel = guest supplies, etc.

thank you.

 

[kjv1611]

Well, if all the items in your table from which you want to pull the first record have a unique ID that is ordered (such as 1 to 1,000, etc) or soemthing of that nature, you should be able to use the DMIN function, I believe.

You can look here for reference:
[URL unfurl="true"]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctDMinDMax_HV05187184.asp[/url]

 

[JULIORUIZ2600]

hi,

i have in report productname footer this formula:=Sum(nz=IIf([CategoryName]="IN-ROOM BEVERAGE",Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage]))*([UnitPrice]),Sum(nz([UnitsReceived])-nz([UnitsSold])-nz([UnitsShrinkage]))*([UnitPrice])*1.07)that total prodcut value on stock.

how can i have in the report footer, a grand total of all the products on stock?

thank you