Order Details Pricing

[Jim P - MA]

I have an application that seems to be unusual in that for each product
record, I would like the ability to store differing Quantity, Pricing, and
Special Notes to later appear in a Combo Box for users to enter the proper
price from the list. Any suggestions on how this would best be
accomplished? The pricing data is currently stored in the record rows as
Q1, P1, Q2, P2 etc. How do I convert these rows to a usable table or query
to feed the Combo Box during Order Detail entry by user?

Any help or direction would be GREATLY appreciated!

Thank you,
Jim Pond

 

[Alec M1BNK]

Read my questions and the limits of my knowledge will soon be apparent, but I
try to help where I can, so I'll have a go. Doubtless there are better
solutions but here is what I would do.

My solution would be to have a table containing rows of productID, Quantity,
Pricing and a primary key, let's call it PriceKey

This table could be linked to the main table by the productID field through
a one to many relationship

The user could select the productID by whatever means. Selecting the
appropriate productID in such a query would give all the quantity-price
combinations for that value of productID. The results of the query should
include the primary key mentioned earlier, PriceKey. A combo box would allow
the user to select the appropriate value of PriceKey, but by setting the
column width of that particular column to zero they would not be aware of
that. the combo box would present three columns from the query mentioned
above. If for example, the row order was PriceKey, Quantity, Price, then the
bound column would be 1, the number of columns 3 and the widths perhaps
0;2;2cm in the properties of the combo box.

An AfterUpdate event, macro or VBA, could then be used to write the values
of Quantity and Price to two text boxes in the form for whatever further
processing you required.

That's my best stab at it with the information provided.

Alec

PS, just looked and I forgot the special notes bit, but still, you get the
idea without them I hope