Order Details in a Subform

[Thomas Pagel]

Hi,

I have a form basically for entering orders. So we have a combobox for the
customer, a field for the order date, ... and a subform with the order
details. In that subform we can choose a product number by a combobox and
can enter an amount.

Behind the scenes we have an order table, a table for the order details and
the products.

The problem is, that I want to have a field for the product name just beside
the product number. It should be a seperate field to have a nice formatting.

I tried to change the datesource of the subform to a query linking the
products with the order details. It works sometimes but if I change the
product number, the product name is not refreshed. If I do a requery, the
first record is selected after that, so it jumps away from the record I was
just editing.

A different way was to use an unbound field and search the product name
(i.e. by dlookup()) after changing the product number. That works, but it
always changes all product names (all the records) to the product I just
entered, so
regardless of all the different products you entered for one order, they all
get the same name.

I hope my problem got clear... What can I do?

Thanks,


Thomas

 

[Marshall Barton]

I have a form basically for entering orders. So we have a combobox for the
customer, a field for the order date, ... and a subform with the order
details. In that subform we can choose a product number by a combobox and
can enter an amount.

Behind the scenes we have an order table, a table for the order details and
the products.

The problem is, that I want to have a field for the product name just beside
the product number. It should be a seperate field to have a nice formatting.

I tried to change the datesource of the subform to a query linking the
products with the order details. It works sometimes but if I change the
product number, the product name is not refreshed. If I do a requery, the
first record is selected after that, so it jumps away from the record I was
just editing.

A different way was to use an unbound field and search the product name
(i.e. by dlookup()) after changing the product number. That works, but it
always changes all product names (all the records) to the product I just
entered, so
regardless of all the different products you entered for one order, they all
get the same name.

If you include the product name field in the combo box's
RowSource, the it's easy to display both the number and the
name. For example, let's say the row source query looks
like:

SELECT productNum, ProductName FROM Products

and the combo box's bound column is 1 so it displays the
number. Then you can display the name in a text box by
using an expression:
=cboProduct.Column(1)

Note that the BoundColumn property starts numbering the
fields with one, but the Column property starts with zero
(Column(1) refers to the second column).

 

[Thomas Pagel]

Marshall,

Thanks, works perfectly...


Thomas

If you include the product name field in the combo box's
RowSource, the it's easy to display both the number and the
name. For example, let's say the row source query looks
like:

SELECT productNum, ProductName FROM Products

and the combo box's bound column is 1 so it displays the
number. Then you can display the name in a text box by
using an expression:
=cboProduct.Column(1)

Note that the BoundColumn property starts numbering the
fields with one, but the Column property starts with zero
(Column(1) refers to the second column).