|
|
|
Unbound controls on a form |
|---|
| message from =?Utf-8?B?TWVnYW4=?= on 1 Jun 2004 |
I'm trying to create an invoice for my project. I need to look up prices on a price table before I can pull them into an invoice details table. I have to create a multi-table query which makes my fields not editable and/or doesn't display an area for me to type in if it's a new/blank invoice. I tried to use the Northwind and other samples, but I need to go back to my price table each time for the price, not the one in the invoice details table.
Can I create an unbound form that only pulls in products and prices from a query, have an unbound contol for qty and another for extended price -- after all, just do an update query to populate invoice details?
i'm open for suggestions.
my two issues are different prices for different customers which have to be editable but always pulling from the original price and not overriding it.
The other is displaying the form or datasheet to enter the information for a blank/new invoice details.
Thanks!!!
I've been tossing this around a lot.
I got the qty to display as unbound but when I use a continuous form, if I put a qty 5 in the first product, they all change to 5.....
stumped.
|
| Allen Browne replied to =?Utf-8?B?TWVnYW4=?= on 2 Jun 2004 |
Meagan, this not how invoices are normally done.
It you look up the price for the item instead of storing the price in the
InvoiceDetail table, what happens on the day when you need to change the
current price for a product? You do realize that all the invoices you have
previously written out for the product will change as well? I'm not sure I
would want to buy products from you if your invoices change like that, as
you would never know what I was supposed to pay you.
For an example of how this is normally done, see the Order Details subform
in the Northwind sample database.
Oh, and as you found, an unbound continuous form won't work.
|
| =?Utf-8?B?TWVnYW4=?= replied to Allen Browne on 3 Jun 2004 |
Each customer has it's own pricesheet. My boss wants to be able to look up a price for a product for a specific customer but be able to change the price - maybe for a discount today only. Next time the customer orders, the original price would be effective.
Why would all of the invoices change? If I have a record of the detail, each one should remain the same. I thought if I could multiply it out each time, it would store the particular prices. Am I wrong? What if I did a temp table and then appended the details table. Would that work?
Because I need to get a specific price / customer, the Northwind example doesn't work because the resultset is not editable. I guess I'm really confused.
Any other suggestions?
|
| Allen Browne replied to =?Utf-8?B?TWVnYW4=?= on 3 Jun 2004 |
The Northwind example does provide for only one price per product. Because
the Order Details table has a Discount field, it does hint at the
possibility of assigning a generic "discount" to the customer if you are
happy to give that discount to all their orders.
If the price of any given product varies depending who you are selling it
to, you have a many to many relation between prices and products. Create a
ProductPrice table, with fields:
ProductID which product
CustomerID which customer
Price how much to charge this customer for this product.
In the Northwind sample, Order Detail subform, the ProductID combo has an
AfterUpdate event that looks up the price. You would do the same with this
system, but lookup the ProductPrice table instead of the Product table.
(There are other options such as setting a number of fixed prices for
products, and assigning the customer a Level1, Level2, or whatever so you
know which price applies to them.)
Regardless of which system you use, you still need the Price field in the
OrderDetail. Having the price there as well allows you to:
- allow a discount on a special occasion, without changing the normal price
the customer pays for the product;
- change the price the customer is to normally pay for the product, without
changing the price in existing invoices.
In summary, you need exactly what Northwind has, but without the Price field
in the Products table. Instead add the ProductPrice table discussed above.
|
| =?Utf-8?B?TWVnYW4=?= replied to Allen Browne on 3 Jun 2004 |
I'm still learning.
I'll look at the afterupdate event. I'm not sure I understood that.
I have that table you suggested already. I do also have the price in the details table.
I hope you're right. I was trying to recreate the Northwind example but having a hardtime of it.
Thank you.
|
| =?Utf-8?B?TWVnYW4=?= replied to Allen Browne on 3 Jun 2004 |
strFilter = “ProductID = “ & Me!ProductID
Me!UnitPrice=DLookup(“UnitPrice”, “ProductsPrice”, strFilter)
How can I modifiy this to send the price tier?
I’ve had trouble finding information on DLookup.
So I need something like:
strFilter = “ProductID = “ & Me!ProductID
priceFilter = “TierID = “ & Me!TierID
Me!UnitPrice=DLookup(“UnitPrice”, “ProductsPrice”, priceFilter, strFilter)
Can you send extra parameters?
|
| Allen Browne replied to =?Utf-8?B?TWVnYW4=?= on 4 Jun 2004 |
Combine the 2 into one string.
strFilter = "(ProductID = " & Me!ProductID & ") AND (TierID = " & Me!TierID
& ")"
Then end result has to look something like the WHERE clause of a query.
|
|
