Streamline Prodcedure

[Guest]

I have a data base to inventory and track repack boxes (fireworks that have
been broken down for sell the previous season).

Each box is assigned to a specific tent # and each tent may have many boxes
and each box may have many products. Each box is given a uniqure bar code
number.

I have set up a form for entering the items in each box. The only fields in
this table based form are box #, item description, item # (autopopulates
based on description, quanity returned, and packaging type of return (each,
box, brick, pack).

When these items are entered, I run them through an append to table query
that adds prices and calcuation fields based on several critera from
different tables (prive level for customer, etc).

My propblem is that for the value of the item and value of the box to
calculate I have to physically go into a form based on this new table and tab
through the value fields. This is okay if I am only entering a few items.
But some locations will have 30 plus boxes and each box could have dozens of
items. No to mention I have to search through all of the previously entered
boxes to find the new ones.

Any suggestions on how to automate the calculations. ?

Thank You

Lynette

 

[John W. Vinson]

My propblem is that for the value of the item and value of the box to
calculate I have to physically go into a form based on this new table and tab
through the value fields. This is okay if I am only entering a few items.
But some locations will have 30 plus boxes and each box could have dozens of
items. No to mention I have to search through all of the previously entered
boxes to find the new ones.

Any suggestions on how to automate the calculations. ?

How are they being done now? on a Subform?

You can use a Totals Query to do the calculations independent of any
form.

John W. Vinson [MVP]

 

[Guest]

Yes there are done on a more detailed version of the orginal table in form
format. I will try the query, but my delima earlier was that the calculations
are complex.

For example here is the code:

Private Sub PRICEOFITEM_GotFocus()
If PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = JKEACHPRICE
ElseIf PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = JKPACKPRICE
If JKPACKPRICE.Value = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = JKBOXPRICE
If JKBOXPRICE = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = JKBRICKPRICE
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = KSEACHPRICE
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = KSPACKPRICE
If KSPACKPRICE.Value = 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = KSBOXPRICE
If KSBOXPRICE.Value = 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = KSBRICKPRICE
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = LAEACHPRICE
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = LAPACKPRICE
If LAPACKPRICE.Value = 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = LABOXPRICE
If LABOXPRICE.Value = 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = LABRICKPRICE
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = WIEACHPRICE
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = WIPACKPRICE
If WIPACKPRICE.Value = 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = WIBOXPRICE
If WIBOXPRICE.Value = 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = WIBRICKPRICE
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = WSEACHPRICE
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = WSPACKPRICE
If WSPACKPRICE.Value = 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = WSBOXPRICE
If WSBOXPRICE.Value = 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = WSBRICKPRICEElse
End If
End Sub

Private Sub Text43_GotFocus()
Text43 = PRICEOFITEM * QUANITYOFITEM
End Sub


It took me weeks to get this correct and working as I have it now.

I will try the query, in the mean time, any other suggestions?