Auto update fields

[binny]

Can someone please tell me how to have the priced field automatically show
the preset price of a product. When I enter an order into my form. I don't
want to have to manually enter in the price of the product. Every time I
select a product from a drop-down list. I know this can be done, because
there is an ex-ample of it in the templates. I don't seem to be able to ask
the right question, from help to get the answer I need. In short, I don't
know what I don't know. I'm sure this solution is quite simple, but I just
can't seem to find it.

 

[John W. Vinson]

Can someone please tell me how to have the priced field automatically show
the preset price of a product. When I enter an order into my form. I don't
want to have to manually enter in the price of the product. Every time I
select a product from a drop-down list. I know this can be done, because
there is an ex-ample of it in the templates. I don't seem to be able to ask
the right question, from help to get the answer I need. In short, I don't
know what I don't know. I'm sure this solution is quite simple, but I just
can't seem to find it.

It's actually a bit tricky, and requires a line or two of VBA code.

Include the price as one of the fields in the combo box's RowSource query. The
field need not be visible - you can set the ColumnWidths property of the combo
to conceal it by setting the width of that column to 0.

In the combo's AfterUpdate event you can then "push" the price from the combo
box into the bound textbox. Let's say the combo is cboProduct based on a query

SELECT ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductName;

ColumnCount would be 3, Bound Column 1 (to store the ID), and ColumnWidths
something like

0";1.5";0"

so that the user sees only the product name.

In the AfterUpdate event property of the combo click the ... icon and choose
Code Builder. Access will give you the Sub and End Sub lines - edit it to:

Private Sub cboProduct_AfterUpdate()
If Not IsNull(Me!cboProduct) Then ' did the user select a product?
Me!txtPrice = Me!cboProduct.Column(2) ' zero based, (2) is the 3rd field
End If
End Sub

 

[Armen Stein]

Can someone please tell me how to have the priced field automatically show
the preset price of a product. When I enter an order into my form. I don't
want to have to manually enter in the price of the product. Every time I
select a product from a drop-down list. I know this can be done, because
there is an ex-ample of it in the templates. I don't seem to be able to ask
the right question, from help to get the answer I need. In short, I don't
know what I don't know. I'm sure this solution is quite simple, but I just
can't seem to find it.

I assume you just want the price to default, then you can change it if
necessary.

Add the price as a column in your combobox. If you don't want it to
display, set its width to zero. Make a note of which column it is
(2nd, 3rd, whatever).

In the After Update event of the combobox, use some code like this:

Me!MyPriceField = Me!MyCombobox!Columns(X)

Change X in the line above to the column number of the Price, minus 1.
Columns are zero-based, so the first one is 0, second is 1, etc.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[binny]

Thanks John that worked a treat. I am currently building a database for my
local sports Association to handle bar and canteen inventries, and event
nominations. I enjoy a challenge and learning access from scratch, is
certainly challenging! So no doubt I will be in touch against from time to
time in the next month.
Once a gain. Thank you very much for the speedy reply and useful advice.

 

[binny]

Sorry Armen that returned a run-time error code 451

 

[John W. Vinson]

Sorry Armen that returned a run-time error code 451

Just a minor typo - I'm sure Armen meant to say Column(1), without the "s".

 

[Armen Stein]

Just a minor typo - I'm sure Armen meant to say Column(1), without the "s".

Yes, thanks John - I was writing some quick air code there.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com