Update Pricing on Subform

[Lordlentz]

I currently have a form that operates similiar to the northwind orders form.
It has a subform in datasheet view that can select items from a dropdown
list. It then uses the dlookup function to populate the price field. I need
to create a command button that will update the price to current pricing for
all records on the subform. Any help is appreciated.

 

[Graham Mandeno]

You can do this by executing a single SQL UPDATE statement.

Dim sSQL as String
sSQL = "Update [OrderItemsTable] inner join [ProductsTable] " _
& "on [OrderItemsTable].[ProductID]=[ProductsTable].[ProductID] " _
& "set [OrderItemsTable].[PriceField]=[ProductsTable].[PriceField] " _
& "where [OrderItemsTable].[OrderNumber]=" & Me.[OrderNumber]
CurrentDb.Execute sSQL, dbFailOnError

Change the table names and field names above as appropriate. You might need
to requery the subform to show the updated prices.

 

[Lordlentz]

Worked perfectly. Thank you for the help.

You can do this by executing a single SQL UPDATE statement.

Dim sSQL as String
sSQL = "Update [OrderItemsTable] inner join [ProductsTable] " _
& "on [OrderItemsTable].[ProductID]=[ProductsTable].[ProductID] " _
& "set [OrderItemsTable].[PriceField]=[ProductsTable].[PriceField] " _
& "where [OrderItemsTable].[OrderNumber]=" & Me.[OrderNumber]
CurrentDb.Execute sSQL, dbFailOnError

Change the table names and field names above as appropriate. You might need
to requery the subform to show the updated prices.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I currently have a form that operates similiar to the northwind orders
form.
It has a subform in datasheet view that can select items from a dropdown
list. It then uses the dlookup function to populate the price field. I
need
to create a command button that will update the price to current pricing
for
all records on the subform. Any help is appreciated.