Storing data in a table from a form

  • Thread starter Thread starter D Kucey
  • Start date Start date
D

D Kucey

I am using a combo box on a form to enter data. The name
that I chose in the combo box also has a value to it. Ex.
I chose Product 1 and it comes with a price of $25. My
price of course comes from my Products table.


My problem is that I want to store that price in a Orders
Details table. The tricky part is that I want to be able
to change that price later on if I want to so I can give
the customer a discount and without touching the Products
table.

I have been looking at the Northwinds db and I can not
figure out how they do it with the Orders form. I want
to use the same concept, where I click on a product and
the unit price automatically comes up. That unit price
comes from the Products table and is stored in the Order
Details table but it can be changed if desired.

Any help would greatly be appreciated.
Thanks
..
 
My problem is that I want to store that price in a Orders
Details table. The tricky part is that I want to be able
to change that price later on if I want to so I can give
the customer a discount and without touching the Products
table.
Click to expand...

You need just a little bit of VBA code to "push" the price into the
field in the combo's AfterUpdate event. Open the form in design view.
I assume you have a textbox txtPrice bound to the price field, and a
combo cboItem. View cboItem's Properties; on the Events tab pick the
AfterUpdate event and click the ... icon by it. Invoke the Code
Builder. Access will give you the Sub and End Sub lines; just add one
more -

Private Sub cboItem_AfterUpdate()
Me!txtPrice = CCur(Me!cboItem.Column(2))
End Sub

(2) means the *THIRD* column in the combo's rowsource (I'm guessing
ItemID is Column(0) and the name is Column(1)).
 
Northwind has the UnitPrice in the Products table as well as in the
OrderDetails table. That's the best way to ensure the orders retain the
correct sales prices when you later change the price of the product in the
Products table.

When the user selects a product in the Orders Subform, it uses DLookup() to
read the current price for that product from the Products table. To see how:
1. In Northwind, open the Orders Subform in design view.

2. Right-click the ProductID combo, and choose Properties.

3. Click in the After Update property (on the Event tab).

4. Click the Build button (...) beside this. Access opens the code window.

If you need help with how to prepare the 3rd argument for DLookup(), see:
Getting a value from a table: DLookup()
at:
http://users.bigpond.net.au/abrowne1/casu-07.html
 
Back
Top