Reading values from tables

[tweety]

Hi



my system is a sales and stock control system, sales can be recorded, but i like to the system to be able to edit selling prices, as when a customer buys huge amounts i can apply discount rather than the form reading the values from the product table.

atm if i edit the price it will change and save the price in the product table.

i tried to add a field in the sales details (sellingPrice) but once i do that the form will not read or fetch the selling price stored in from the product table

what am i missing out?

thanks

[Bob Fitz]

i tried to add a field in the sales details (sellingPrice) but once i do that the form will not read or fetch the selling price stored in from the product table

Perhaps you could put some code in the After Update event of the the Product combo box, that will lookup the the current price in the Products table and apply that to to "SellingPrice" field. The price applied could still be changed if a discount needed to be given.

[tweety]

I have added the SellingPrice field to the form and set the control source to "=[Product].[SellingPrice]" (thats were the price is)

after running this i get the #Name? error

i also tried in the update combo box event "Me.SellingPrice = products.SellingPrice" but no joy there too

any ideas?

[Bob Fitz]

I assume that the form is bound to the SalesDetails table.
The SellingPrice control on this form should be a text box bound to the SellingPrice field of the SalesDetails table, so the Control Source property should be set to SellingPrice.
You should also have a combo box on the form from which the user can sellect a product. This should be bound to the ProductID of the SalesDetails table, so the Control Source property should be set to ProductID. It's Row Source property should be set to a SQL SELECT statement or a saved query that uses the Products table and includes any of the fields that may be needed. You would need, at least, ProductID, ProductName and SellingPrice fields. The code required for the After Update event of the combo box would be something like:
Me.SellingPrice = Me.ActiveControl.Column(2)
This line of code will set the value of the "SellingPrice" text box on the form to the value held in the 3rd column of the combo box because the numbering of the columns in a combo box start at 0, so the first is 0, second is 1 and third is 2.

[tweety]

I started again

in the salesdetails table i have added a new field "sellingPrice" i then replaced the current sellingprice on my form with the new one, so now the form is reading blank values as there is nothing in the the field, not a problem
however, i wanted the new sellingprice field to read the prices from the product table as its only 10% of the time something is sold cheaper
there is a combo box where the user can select the product from, i have added in the row source to the combo box the sellingprice from the product table and the code Me.SellingPrice = Me.ActiveControl.Column(2) after update to the combo box but still no joy....... i have attached a copy of the form, it has 2 subforms in it

[Bob Fitz]

Can you post a copy of the db in A2003 mdb format.

[tweety]

database attached in zip format (access 2000+)

[Bob Fitz]

i have added in the row source to the combo box the sellingprice from the product table and the code Me.SellingPrice = Me.ActiveControl.Column(2) after update to the combo box but still no joy.......

In the posted db, you have:
Me.SellingPrice = Me.ActiveControl.Column(3)
Change it to:
Me.SellingPrice = Me.ActiveControl.Column(2)
Then move that line of code from the AfterUpdate event of the text box called "Quantity" to the AfterUpdate event of the combo box called "ComboProductID"
Change the following properties of the combo box:
Column Count 3
Column Widths 0cm;2.544cm;0cm

[tweety]

i should have mentioned i did try with the code: Me.SellingPrice = Me.ActiveControl.Column(2) but then tried different numbers, anyway i use the in the update of the comboproductID and updated the properties of the combo box too

but now i get a runtime error 2455 (as soon i select a product from the list), when i hit debug it points to the code: Me.SellingPrice = Me.ActiveControl.Column(2)

[Bob Fitz]

Can you post a copy of the db in A2003 mdb format.

[tweety]

Sorry for the delay, was having a good go at it

[Bob Fitz]

I am unable to open the db that you atached to your last post. Perhaps something went wrong in the convertion to mdb.
Before posting another copy, just check that the Name property of the SellingPrice text box is set to: SellingPrice

[Bob Fitz]

Take a look at the attached db, which is a copy of your first post to which I have made a few changes.

At the request of the OP I have removed the db from this post.

[tweety]

thanks for looking at it, i have seen the changes, the product combo box list has the price which can then be edited in the selling price field.
is there away the product list only shows the products and the selling price from the product table can be display the price on the form (in the new field, salesdetails.sellingprice) rather that displaying it in the product list

thanks again

[Bob Fitz]

Change the Column Widths property of the products combo box from:
0cm;2.544cm;1cm
to:
0cm;2.544cm;0cm
This will hide the 3rd column which displays the price data. Once a product has been selected, its price will be displayed in the SellingPrice textbox.