Order Entry Form - Case Price vs. Unit Price

[Kaloyanides]

Can someone help me with a tricky thing I'm trying to figure out?

I am not a programmer.

I have an order entry screen. We sell products by the case and by the unit. The default price for the item would be case price but we need a way to switch the price to the unit price. I thought maybe a drop-down that would allow the user to select "unit" and then the "unit" selling price would auto populate the price field? Maybe there is a better way, I don't know. Maybe a checkbox that if selected runs some vba that that changes the value in the price field from case price to unit price?

All of the data (unit price/case price) is in the tblItemMaster table.

Using a combo box, the user enters or selects the item and then the selling price (from one of the item combo columns) is auto populated into the price field. The following happens:



Me![InvoiceItemUnitPrice] = Nz(Me![cboDesc].Column(7), 0)
UpdateLine

I've posted the UpdateLine Code Below

I need some easy way for the data entry person to specify whether we're selling a unit or a full case the default being full case.

If someone could help, I would be very very grateful and I'll write a very nice review!


Select Case Nz(PricingMethod, 0)
Case 0 'don't charge tax
Me![InvoiceItemNetAmount] = MyRound((Nz(Me![InvoiceItemUnitPrice], 0) * Nz(Me![InvoiceItemQuantity], 0)) * (1 - Nz(Me![InvoiceItemDiscount], 0)), 2)
Me![InvoiceItemTaxAmount] = Null
Me![InvoiceItemTotalAmount] = Nz(Me![InvoiceItemNetAmount], 0)
Case 1 'Prices EXCLUDE tax (calc net and add tax)
Me![InvoiceItemNetAmount] = MyRound((Nz(Me![InvoiceItemUnitPrice], 0) * Nz(Me![InvoiceItemQuantity], 0)) * (1 - Nz(Me![InvoiceItemDiscount], 0)), 2)
Me![InvoiceItemTaxAmount] = MyRound(Nz(Me![InvoiceItemNetAmount], 0) * Nz(Me![InvoiceItemTaxPercent], 0), 2)
If Me![InvoiceItemTaxAmount] = 0 Then
Me![InvoiceItemTaxAmount] = Null
End If
Me![InvoiceItemTotalAmount] = Nz(Me![InvoiceItemNetAmount], 0) + Nz(Me![InvoiceItemTaxAmount], 0)
Case 2 'Prices INCLUDE tax (calc gross and determine tax amount)
Me![InvoiceItemTotalAmount] = MyRound((Nz(Me![InvoiceItemUnitPrice], 0) * Nz(Me![InvoiceItemQuantity], 0)) * (1 - Nz(Me![InvoiceItemDiscount], 0)), 2)
Me![InvoiceItemNetAmount] = MyRound(Me![InvoiceItemTotalAmount] / (1 + Nz(Me![InvoiceItemTaxPercent], 0)), 2)
Me![InvoiceItemTaxAmount] = Nz(Me![InvoiceItemTotalAmount], 0) - Nz(Me![InvoiceItemNetAmount], 0)
If Me![InvoiceItemTaxAmount] = 0 Then
Me![InvoiceItemTaxAmount] = Null
End If
End Select

[Homegrownandy]

Is it possible to zip your database and upload here?

click go advanced under your post to manage attachments.

[orange]

This may be a case where further analysis is required.
It seems (to me at least) that you could sell a Unit of X, or a Case of X (which contains y units).
Rarely would the price of Case of X = y*(Unit of X). There is likely some discount factor when buying by the Case.

But here is an additional wrinkle for consideration.
The Price of a Unit of X (or a Case of X) today, will probably change at some point in the future (next week, next month, next year??). When selling the item (unit or case) you'll want to record that price (the price of the item at the time of sale), and separately record the current selling price of the item. Also, you may want to consider things like a Special Sale, a Clearance Sale, Bargain Friday or whatever. Just some thoughts concerning prices, items, lots, customers, orders, invoices and history issues that "might" occur. Some of this may not be within the scope of your project (or post), but should be considered, and rejected as appropriate based on your knowledge of the business.

Good luck with your project.

[Kaloyanides]

It would let me attach... I deleted all but sample data. I even tried zipping it first... It goes to upload but never shows for me to drag into box below...

[Homegrownandy]

you have to click the text "upload" just under where you add the file.