Unbound controls on a form

[Guest]

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]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.....

 

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Allen Browne]

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.

 

[Guest]

That makes compete sense. I'll try it. Thank you