query calculation

[Gx]

I have this form with the fields:

* Product: (drop down combo box)
with choices - X, Y

* Amount: (currency )

* Balance (currency)

I want to associate product X with the percentage amount of 50 %, Y with 10
%
So by selecting product X from the drop down combo box and then typing an
amount in the Amount field...... the Balance field will then automatically
calculate: amount x 0.50
The problem is the association part from the selection of a product in the
drop down box. How can i do this in a query ?

[Allen Browne]

You don't do this kind of thing in a query. Use a form.
Use Datasheet view if you want the form to look like a query.
You can then use the AfterUpdate event procedure of the combo to assign a
value to the balance.

The combo box will have its Row Source property set to the Product table. If
you already have it set up, add a field to the Product table to indicate the
percentage. It will be a Number field, of size Double, and its Format
property set to Percent.

Now set the RowSource of the combo to a query statement that includes the
percentage field. Something like this:
SELECT ProductID, ProductName, AssocPercent FROM tblProduct;

Then set the combo's AfterUpdate proeprty to
[Event Procedure]
Click the build button beside this so Access opens the code window.
Enter the code so it looks like this:

Private Sub ProductID_AfterUpdate()
With ProductID
If Not (IsNull(.Value) Or IsNull(Me.Amount)) Then
If IsNumeric(.Column(2)) Then
Me.Balance = Round(Me.Amount * .Column(2), 2)
End If
End If
End With
End Sub

Note:
The Column property is zero based (i.e. the first column is 0, the 2nd is 1,
....).
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Gx" <sk**@optusnet.com.au> wrote in message
news:3f***********************@news.optusnet.com.a u...

I have this form with the fields:

* Product: (drop down combo box)
with choices - X, Y

* Amount: (currency )

* Balance (currency)

I want to associate product X with the percentage amount of 50 %, Y with 10 %
So by selecting product X from the drop down combo box and then typing an
amount in the Amount field...... the Balance field will then automatically calculate: amount x 0.50
The problem is the association part from the selection of a product in the
drop down box. How can i do this in a query ?