How do I do this?

[Nocturnal]

I want to have a drop down box that has a set of things such as items in
inventory. If I select an item, say for instance, I select Hard drive, I
then want another field populated with the price the hard drive currently is
set at. Would I achieve this through AfterUpdate? I'm not even sure how to
use VBA, I'm a novice but I'm trying to create a commission database for
myself. Commission as in sales commission. I can sell several items, each
item is worth x (constant). I get paid a percentage of x. The percentage
is also a constant. I would like to be able to I guess have a form that
let's me enter:

CustomerName
Invoice#
Service
Then have the service be a combo box which has several items. If I
select one item, I would like to have another field populated with the price
of said service/item.
Commission I will receive

I'm guessing I would actually use a report in order to see how much
commission I would be getting.

Any ideas? Thanks!

 

[Allen Browne]

Yes, use the AfterUpdate event procedure of the combo to lookup the current
price for the product, and assign it to the price field.

For an example, open the Northwind sample database that installs when you
load Office. Open the Order Details subform, and take a look at the
ProductID combo.

 

[Nocturnal]

That is just what I was looking for. Thank you and have merry Christmas and
a happy New Year!

 

[Nocturnal]

Allen,

Private Sub Product_ID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![Product ID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![Unit Price] = GetListPrice(Me![Product ID])
Me![Discount] = 0
Me![Status ID] = None_OrderItemStatus


'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End Sub

That is the code I found. However, I'm not confused. What or where is the
GetListPrice code located? I can't figure out what GetListPrice does.
Also, what does the Me! stand for? Any resources online that I can read
more about this?

 

[Allen Browne]

Hmm. I don't recognise that.
Put the cursor in GetListPrice,and press F2 to see where it comes from.

I would have expected something like:
Me.UnitPrice = DLookup("PriceEach", "ProductTable", "ProductID = " &
Nz(Me.ProductID,0))

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Private Sub Product_ID_AfterUpdate()
'Initialize price and discount for each product change
If Not IsNull(Me![Product ID]) Then
Me![Quantity] = 0
Me.Quantity.Locked = False
Me![Unit Price] = GetListPrice(Me![Product ID])
Me![Discount] = 0
Me![Status ID] = None_OrderItemStatus


'Empty Product records mean user wants to delete line item
Else
eh.TryToRunCommand acCmdDeleteRecord
End If
End Sub

That is the code I found. However, I'm not confused. What or where is
the GetListPrice code located? I can't figure out what GetListPrice does.
Also, what does the Me! stand for? Any resources online that I can read
more about this?

--


Nocturnal @ http://www.randomfix.com

Yes, use the AfterUpdate event procedure of the combo to lookup the
current price for the product, and assign it to the price field.

For an example, open the Northwind sample database that installs when you
load Office. Open the Order Details subform, and take a look at the
ProductID combo.

 

[Allen Browne]

Make that Shift+F2.

 

[Larry Linson]

I want to have a drop down box that has a set
of things such as items in inventory. If I select
an item, say for instance, I select Hard drive, I
then want another field populated with the price
the hard drive currently is set at. Would I achieve
this through AfterUpdate?

I'd approach this just a little differently than my colleagues have
suggested. Use as Row Source for the ComboBox a Query that includes
ProductID (if you need it, which you may not), ProductName, and
ProductPrice.

"txtPriceThisSale" is the Text Box Control on your Form that is where you
want to save the price, and "cbocboProductAndPrice" is the name of the Combo
Box from which the user is to select the product. Use your own preferences
for name... be sure, if you leave blanks in the name, to surround the name
with square brackets [ ].

In AfterUpdate, with the three fields in Row Source,

Me.txtPriceThisSale = Me.cboProductAndPrice.Column(2)

If you don't need or use the ProductID, it would be Column(1), instead.

Larry Linson
Microsoft Access MVP