Move field value from table to table

[ccg_0004]

Howdy all!

I am new to Access (only using 2 weeks) and have never taken a class. I am trying to update a DB for my company.

[]=Table {}=Field

What needs to be done is as follows:
When a new Purchase Order form is filled out each material ordered has a {ProductNumber} and has a {price}.
The {ProductNumber} is the PK in the [Inventory], while this {price} is saved in the [OrderDetails] table (in the Price field)

The table called [Inventory] stores info about the products (such a manufacturer, serial number, latest price).

However, as it comes time to order new materials the prices have changed.
That is, when a new PO form control {Price} is filled out, [OrderDetails]{Price}records the price and product number.


I need the [Inventory]{Price} to update the LATEST {ProductNumber}'s price when a new purchase order is completed. Is this difficult?

 

[ajetrumpet]

When a new Purchase Order form is filled out each material ordered has a {ProductNumber} and has a {price}.

I need the [Inventory]{Price} to update the LATEST {ProductNumber}'s price when a new purchase order is completed. Is this difficult?

No, and as a matter of fact, you should probably store an UPDATE query to handle this for you, regardless of weather or not the prices changes. Write a UPDATE query first:

UPDATE Inventory SET

Inventory.Price = forms!YourFormName!PriceControlName

WHERE Inventory.ProductID = forms!YourFormName!ProductIDControlName

I assume then, you have a button on your form to initialize the change, or new record entry (new purchase order?? If so, write a command behind it to change the price in the inventory table:

docmd.setwarnings false
   docmd.openquery "TheQueryName"
      docmd.setwarnings true

You don't have to mess around with the warnings, but if you do, you won't get that annoying popup regarding updates to your tables.

 

[ccg_0004]

Adam -

Thank you for taking the time to assist a beginner like myself. I followed the advice that
you gave and had no error messages in the code. I think it is a problem with my WHERE criteria because a parameter popup box asking for the product number appears. Here's my code:

UPDATE Inventory SET Inventory.Price = [OrderDetails Subform].Form![GearhartPurchaseOrders]![Price]
WHERE Inventory.[Product Number]=[OrderDetails Subform].Form!GearharAutoNumber;

So I get two parameter boxes and when I fill them in they update the Inventory price. But i want the query to get the info from the Form not the parameters!!


The problem I am having is directing the Update query to the appropriate controls on the
forms. Here's why: The {Price} control is located on a subform. What is the appropriate
location to direct the query to locate? The main form is "PurchaseOrders" and the subform
is "zzzOrderDetails" and the subform control is named "OrderDetails Subform"

Also, the "Product ID" control is located on the OrderDetails subform. To complicate matters further,
the "Product ID" control is a Combo box that gets its Row Source from a Select query
that displays Product ID and Description.

Any further ideas or assistance would be much appreciated. I am so close I can feel it...
UGH!

Chris

 

[ajetrumpet]

The problem I am having is directing the Update query to the appropriate controls on the
forms. Here's why: The {Price} control is located on a subform. What is the appropriate
location to direct the query to locate? The main form is "PurchaseOrders" and the subform
is "zzzOrderDetails" and the subform control is named "OrderDetails Subform"

You are exactly right Chris. But hey! At least you know the difference between the form and it's child control. A lot of people don't, and unfortunately never get to understand the concept of it! If you want a future reference for this complicated syntax, here is Bob's link for it:

http://www.access-programmers.co.uk/forums/showthread.php?t=127981

Anyway, write this SQL for the Price syntax:

= forms!purchaseorders![orderdetails subform].form!price

and write this for the productID:

= forms!purchaseorders![orderdetails subform].form!productID

I would think that should get you to the end of the road...

 

[ccg_0004]

Big Help!!! U DA MAN!

MY MAIN MAN Adam... thanks!!! That was the correct fix. Now I am having more trouble if you'd like to take a stab...

When a product number is selected in the OrderDetails Subform, fields such as description, manufacturer #, are added to the appropriate controls. I did not write this form or code, where could it be found?

I want to know because I also want the Combobox to return the price (from the Inventory table) when the product number is selected. Right now I have the price returning from the combo box through the Control Source code:

=forms!purchaseorders![OrderDetails Subform].Form![GearharAutoNumber].Column(3)

But with this code the price is bound and cannot be edited.

I want to return the price from the inventory. But if the price has changed from what was stored i want to be able to edit the value. Then, like we completed yesterday, if the price changes than the value in the Inventory table is updated as well.

 

[ajetrumpet]

Right now I have the price returning from the combo box through the Control Source code:

=forms!purchaseorders![OrderDetails Subform].Form![GearharAutoNumber].Column(3)

But with this code the price is bound and cannot be edited.

That's just one way to populate a control like this. And, it cannot be edited because it implies a specific value. If you set the control to a CS of a table field, that is not specific, as it defines a range and not just one value. Your description of the population of the other controls uses this method:

ComboBox_AfterUpdate()

  me.textbox1 = me.combobox.column(i)
    me.textbox2 = me.combobox.column(I + 1)

etc...

I want to return the price from the inventory. But if the price has changed from what was stored i want to be able to edit the value.

If you want to change your ability to edit the value in the inventory control, get rid of the current CS and add it to the After_Update event of the box. Or, if the value comes from a different table (which I think it possibly does), simply use DLOOKUP() to locate the value and populate the control. DLOOKUP would be perfect for this, but only if you use it in Basic. If you set the CS to something like this:

= DLookup("syntax")

...you will not be able to edit the value, because it will be bound to an implicit specification rather than a range (like a field).

Then, like we completed yesterday, if the price changes than the value in the Inventory table is updated as well.

All you have to do to ensure updates automatically in the table is BIND the control to table's field. However, if the RS of the form is not the table that is supposed to contain the value, or you can't update the value through the form any other way, you'll have to write an UPDATE SQL statement behind the form...