Sort detail records in an invoice

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have greatly modified the Northwinds database. My wife sells Mary Kay
products and enters them in the invoie form. The invoice details contains
the individual items. In most cases, the order that these items display or
print would not be that important, but she hs these "kits" or "bundles" that
she sells so the order is important.

For example...

PART DESCRIPTION RETAIL SELL QTY EXT SELL
0000K1 ***Kit 0001 Includes: 0.00 5.00- 1 5.00-
129779 Lipstick - Some Funky Pink Color 15.00 15.00 1 15.00
105689 Blush - some other color 12.00 12.00 1 12.00
197779 Satin Hands 55.00 55.00 1 55.00
0000K2 ***Kit 0002 Includes: 0.00 5.00- 1 5.00-
136779 Dermabrasion Gift Kit 55.00 55.00 1 55.00
245608 Lotion 8.00 8.00 1 8.00
107589 Compact 22.00 22.00 1 22.00
384668 Lotion 8.00 8.00 1 8.00


Since I need the kit line to come right after or right before a list of
products, what would you recommend I do to keep them in order? I don't want
to add an autonumber to my details table because she might screw up and enter
them out of order, then go back and insert a line which would have a higher
autonumber and would reorder when printed.

Not everything will be part of a kit. As a matter of fact, most things will
not.

Each kit is unique. Kit 001 for "Sue" will contain different colors and
styles of products thant kit 001 for "Joan" so I can't really build "defined"
kits. I just need to apply a discount (or two or three) and keep the
products with the kit.

I thought about adding a "kit part" field to the product and I could tell it
which kit each part was part of (drop down with valid entries of K1 - K9),
but I don't know if that is the best idea!

Let me know if I need to provide more details. Don't worry about preserving
my current table structure. I can work with whatever suggestion you have.

Thanks for the advice!
 
Rick, you could add a SortOrder field to the InvoiceDetail table (Number,
not required, indexed duplicates okay.) Then in the BeforeInsert event of
the subform, you can assign the next number (sample code below.) She can
then change the number to place the items in a different order. To
facilitiate resorting, I generally allow duplicates, and tell the user that
if they have 2 of SortOrder 3, the print-out is undefined for those 2 items.

You've posted a few messages about these kits, so they seem to be important
to your wife. It seems to me that she needs to be able to teach the database
about the current kits for each client, while keeping the flexibility to
alter them at any time.
Kit table:
KitId AutoNumber (pk)
CustomerID who this kit is for.
KitName whatever name this customer calls this kit
KitDetail table:
KitId Foreign key to Kit.KitId
ProductId Foreign key to Product.ProductId
Quantity Long (how many of this item in the kit)
Discount Double (how much this product is discounted in this
kit)

You could then set up a command button to add the items in a kit to an
order. It would be an Append Query statement, using KitDetail joined to the
Product table, and inserting records into the OrderDetail table. Later you
can change the kits without affecting existing orders, i.e. the Kit and
KitDetail serve only as a template for new orders.

The only structural changes to the existing database would be:
1. Add an AutoNumber pk to OrderDetail. Someone may want to order 10 of a
product in a kit, plus (on another row of the order) 2 of the same product
at full price.
2. (Optional) Add a KitId foreign key field to OrderDetail table. No
Referential integrity. This is not important, but it may help as an
indication that some items were originally added to an order as part of a
kit.

Sample code to select the next available sort order when entering a new
record in the subform:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent.InvoiceID
If IsNull(.value) then
Cancel = True
MsgBox "Enter the invoice in the main form first."
Else
strWhere = "SortOrder = " & .Value
Me.SortOrder = Nz(DMax("SortOrder", "InvoiceDetail",
strWhere),0)+1
End If
End With
End Sub
 
Back
Top