Update Pricing on Subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
Worked perfectly. Thank you for the help.
Graham Mandeno said:
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

Lordlentz said:
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.
Click to expand...
Click to expand...
 
Back
Top