auto pricing

[abhi.karnawat]

hi,
i have 2 tables one containing stock and one containing prices.
in order to give a price to a stock item the following criteria's should match:
shape, size, color, clarity

for example:

my stock table: shape:RB, Size: 3.3mm, Color: F, Clarity: VS1, Price: 0
Price table: Size: 3.3mm, Color: F, Clarity: VS1, Price: 150$

after all these criteria's are the same the stock table should take the price 150 automatically after updating the final criteria of clarity.

i need 2 functions:
1. i need to update all the unsold stock prices at once if my price list has changed.
2. when i enter new stock in the stock table through a form it should take the price of the individual stock item automatically.

for option 2, i have tried using in my stock entry form the following code:
Clarity Afterupdate()
if shape="RB" and mm=Form_RBWPrices.mm and Col_D_Z=Form_RBWPrices.Clour and Clarity=Form_RBWPrices.Clarity then
Form_DiamondStockRegistery.GIV_Price=RBWPrices.Price
end if
end sub
----> Didnt Work
For option 1 i thought of using an update query but i have no idea.. where to start on that...and also dont know if it would be right to use an Update query.

Would appreciate the help as it would save me a lot of work time in the future.

thx
abhi

 

[Galaxiom]

To update the price on all records:
Create an Update query and drag in the two tables.
Join the two tables on the common fields (except price).
Drag the price field from the stock table into the design grid.
Enter tblPrice.Price as the "Update To" value. (BTW, naming any field the same as a table should be avoided.)
Run the query.

To get the price to update on your form:

Use the Price table as the basis for a subform.
(This subform can be not visible if you like and need not even have any controls)

Set the Master and Child Link fields as all the matching fields.
(Separate the field names with semicolons)

Run the VBA as you save the record rather than on the Clarity event. (After Update event)
(You need to save the record before the links will pull the subform record.)

Set the stock table price textbox to equal the price table field.

Me!StockPrice= Me!subformcontrolname.pricefieldname

(Without controls on the subform Access will go for the field from the record Source.)

 

[abhi.karnawat]

i did the update query.. it gave me an error.. cannot update '(expression)' ;field not updateable
please help on this function first.. thx.. i have attached the query pic in design view..

 

[Galaxiom]

It appears you have joined carat to the wrong field in dbo_Stock.

 

[abhi.karnawat]

ok thx got the update query working,
now the second part seems a little more complicated..
where would i start.. ?

 

[Galaxiom]

ok thx got the update query working,
now the second part seems a little more complicated..
where would i start.. ?

I expect you have already made a form based on a query form your stock table.
Next insert a subform control based the fields from the price table including those which define the match with the stock table and the price field.
I generally avoid the wizard particularly where it asked for the relationship with the main table. In the properties of the subform control list the fields from the main form's table that will define the match in the Link Master Fields property. List the matching fields from the Price table in the same order in the Link Child Fields property. When a Stock record is selected the matching Price record should appear in the subform.

 

[abhi.karnawat]

hey thx again for all the help i got everything working as i wanted.. your idea for the subform with multiple linking criteria's helped a great deal and now saves me a lot of time in updating my Inventory prices..

abhi

 

[Galaxiom]

Excellent. Glad to be of assistance.