Creating an Invoice on Products with Varying Prices

[Guest]

Hi.
Thanks in advance for your help

I'm trying to create an invoice for an application that uses the invoice as the order form.
There are numerous products with varying prices depending on the customer.
I've tried to follow the Northwind example but my application is different.
The Northwind example using a price that comes from the "details" table. I need to see the original price sheet price but also be able to edit it for a particular order.
I set up my design like the Northwind but I can't get it to work. If I create a query to pull in the details, it's an empty query and therefore I have no row. Is there a way around that
I created a temporary table that holds the prices for the specific customer them I'm trying to create an extended Details query using that for the price and product like the Northwind example does - but that doesn't work either.

Right now, I created a form that passes the customer information and price list information to a form.
That form has a subform with invoice information. Then, I'll have a details form as a subform to the invoice form
I don't think I'm doing that completely correctly but when I try to follow Northwind's example on that, again - when I'm trying to create a new invoice, I'd have to type all of the customer information and that's not a good option

Can anyone help me get these details
Maybe it's just setting different defaults for the tables.
They are standard - Details is the same as Northwind. Then the ProductsPrice table has descripiton and price

Thanks

 

[cindybidar at comcast dot net]

I'm not sure I'm understanding you correctly, but I will give you an
example of what I did in this situation. In the invoice subform (details
of the invoice), there are text boxes txtPartNumber, txtDescription,
txtPrice. In the AfterUpdate event of txtPartNumber, write the code to
look up the price, and populate txtPrice with that value. You can use
DLookup for this, but you might find writing a function which opens a
recordset to be quicker. The field txtPrice will still be editable, so
if the customer gets a discount you can enter it manually.

If, however, you want to keep track of different pricing for different
customers, then you will need a linking table between customers and
products, something like tblCustomerPricing, containing fields
CustomerID, ProductID, Price. Then you can look up the pricing from this
table instead of tblProducts.

Hope that helps you out some, or at leasts points you in the right
direction.

Cindy