Combining Quantities

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

Hi, I have an order entry form with a subform attached for order
details. Whenever you add a product to the Order Details subform and
then you try to add the same product again in won't let you. Which is
fine. The problem I'm having is that I'll be sing a scanner to enter
products and when you have two products in a row I need the subform to
make the quantity 2 not give me an error message that I already have the
product entered. I tried a work around by allowing duplicates, which
also works fine but now I have a product listed 2 or more times which
looks sloppy. Any solutions? If I write a Macro what would it say? If
I write code what would it say? Thanks Everyone,
DS
(e-mail address removed)
 
The Order Details table in Northwind has a primary key made up of OrderID +
ProductID. That always seemed dumb to me: you don't want to stop customers
from ordering more of your products. Perhaps they took that approach to
demonstrate of how to use a complex key rather than as a suggestion of good
design.

The simplest solution would be to add an AutoNumber as primary key, so you
can scan as many items as you wish. Then in the print out (report), create a
group footer section based on ProductID, and sum the items there. Set the
Visible property of the Detail section to No, and it prints just one row
with the sum of the products, even though they were entered on separate
rows.

The alternative would be to use the BeforeUpdate event of the form to:
- Check if another row exists for this product.
- If so, store the details of the row in variables, cancel the event and
undo the form.
- Then locate the record in the RecorsetClone of the form, Edit, and Update.
 
Back
Top