Update a Table from a Form

[BroomerEd]

I have a form where I select a product code from a dropdown box and, via a dlookup formula, retrieves the current selling price of that product from a query. I wish to store both of these numbers in the underlaying table. The product code works because you can set that up with the dropdown box. My problem is that I've tried a set value macro after the update of the price field but it doesn't get in to the table where I need to store it. These prices can change each month so I must store the current price.

Any assistance is appreciated.

 

[GingerR]

What does your macro say?
You say the price " but it doesn't get in to the table where I need to store it"; where does it go? Do you mean it doesn't go anywhere?

Can I ask something about your structure? Instead of saving the "current price", can't you store the prices by date? So then:

ProductID DateFrom Price
1 1/1/07 2.34
1 2/18/07 3.15

Then you don't have to store the current price - I assume the Products are on an "Order" which has "Date" that you can refer to this table to see what the price was then.

That being said, did you try the combo box's CHANGE property? you could put something in like

Me.txtPrice = dlookup("Price","ProductPriceTable","ProductID = " & Me.cboProductID)

Or, if there is only ONE "current price", you can add that to the combo box's RowSource (and hide the Price column) then instead put

Me.txtPrice = Me.cboProductID.column(2)

(where '2' is column 3 - columns start numbering at 0)





Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BroomerEd]

Thanks Ginger for responding to my dilema. I'm setting up this database for use by sales people and I want to make it simple where they just have to upload their monthly pricing file.

I have attached below (hopefully, this is the first time using box.net) a file that includes the table structure where I want to store the price as "Regular Cost", the form where I input the item and it retreives the price, "Reg Cost", the query where the Dlookup finds the price to populate the form, the design version of the form showing the Dlookup function and the macro where I try to pick the price up from the form and add it to the table.

Your help is appreciated

 

[GingerR]

Hi--I can't get the file here at work (prohibited).
Did you try Dlookup? Did it work?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BroomerEd]

The Dlookup to get the price into the form works but I still can't get it into the table where I need to keep it.

 

[GingerR]

PLEASE post your code, name of text box you want it to go into, name of form. Where is your code located? Is the text box BOUND to your table, meaning in the text box's ControlSource property is the name of the field you want the data to go into?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BroomerEd]

I've managed to update, via a dlookup formula, [reg cost], which is an unbound control of subform [bid detail entry form] with underlaying table [bid detail table] whose master form is [bid master entry form]. The [bid detail table] contains the control [regular cost] which I wish to update with [reg cost] from each of the records created in [bid detail entry form].

I've used the macro Set Value, Item:[Bid Detail Table]![Regular Cost], Expression: [reg cost]. I run this "after update" of [reg cost] in the subform [bid detail entry form] which happens via the dlookup formula.

Does this help?

 

[MajP]

I think you are making this too hard. Use Ginger's second suggestion and avoid DLookup. The database lookup functions are problematic at best. I avoid them at all cost.

1) Keep your regular cost field bound to your bid details table.
2) Bound your product code combo to the bid detail table
3) In the sql for your product code combo return two "columns", product code and reg price. You can choos to show the reg price or you can hide it. To hide it set the values
bound column: 1
column count: 2
column widths: 1";0" (1 is an example width)
4) in the after update of your combo run a code like this. Change the names to meet your names:

Private Sub cmboItem_AfterUpdate()
Me.txBxRegularCost.Value = cmboItem.Column(1)
End Sub

This will put the cost into the bound field.

As Ginger pointed out the columns are indexed 0,1,2...