Dlookup in forms

[geeves1293]

Hi everyone,

I'm trying to select a product from a combo box in an order form, then in
the unit price text box on order form the value appears after update from
product combo box.

Thought that dlookup function would do this for me, but getting dizzy with
all the thinking. Please help.

Thanks
Geeves1293

 

[EJ Williams]

Assuming you have a table that looks like:

ITEM UNIT_PRICE
----------------- -------------------
A 1.50
B 2.35

Then you can do this from your combo box by changing its rowsource to
include the second column of information. Something like "Select ITEM,
UNIT_PRICE from TABLE Order By ITEM". You then set the columns property to 2
bound column to 1 and set the column width property to 1;0 if you want to
hide the price.

In the after_update event for the combo box add the code:

Private Sub ComboBoxItem_AfterUpdate()
TextBoxPrice = ComboBoxItem.Column(1) 'I think this is a zero based index
End Sub

The result should be that the price which is hidden in column 2 is
transfered to the unit price text box. My example assumes the column property
is 0 based index but I can't confirm that right now. If you get the wrong
data then change it to 2.

This pulls the data all into the combo box when the form is loaded and you
don't have to keep querying the table to get the price as you would through
DLookup.

I hope this helps.

Eric

 

[BruceM]

You could include the UnitPrice in the combo box Row Source. I will say
that your combo box Row Source is ProductID, Product, UnitPrice. The combo
box Bound Column is 1, the Column Count is 3, and the Column Widths are
0";1.5";1" (or something like that). If you don't want to see the UnitPrice
in the drop-down, set the third column width to 0".

To store the UnitPrice in a field, in the combo box After Update event:

Me.txtUnitPrice = Me.cboProduct.Column(2)

Columns are numbered from 0 in this case, so Column(2) is the third column.

If you don't want to store the UnitPrice, set the Control Source of the
UnitPrice text box to:
= Me.cboProduct.Column(2)

Note that if you do not store the UnitPrice (presumably the combo box is on
an OrderDetails subform. linked to an OrderDetails table, or something of
the sort), if it changes in the Product table it will change in past Order
records.

To use DLookup, in the combo box After Update event:
Me.txtUnitPrice = DLookup("[UnitPrice]","[tblProducts]","[ProductID] = " &
Me.ProductID)
This assumes the combo box is bound to ProductID, and that it is a Number
field.

Details may vary depending on the details of your database structure, field
names, control names, etc. If you need more information you will need to
provide more details.