filter a list box on a form by a field in the record

[SuzanneShah]

I've been struggling with this for days and am hoping for some help.

The scenario is:
I have an access database which records production information.
Some of the data it uses is pulled from Navision using linked tables.

I have a table in access which records machining data (TBLMachining) [Date,
Item No, Sales Order No, Qty, Length etc) - part of this is used to record
the sales price on an item (qty x weight per item x sales price). This is
done by using a combo box which looks up a linked Sales Order table
(QRYSalesOrders) which includes the sales price.
The primary Sales Order Tables have a dual Primary Key - which I can't
change as it's a linked table and cannot write back to the Navision database.
The field the form records to TBLMachining is the sales order number.
The problem is that a sales order number can have more than one order line
ie
Order No Item No Sales price
SORD25368, M000250, 0.25
SORD25368, M000255, 0.35
SORD25368, M100300, 0.40
SORD25368, M300050, 0.20

so when I select the relevant sales order number and item number, instead of
recording the price from the 4th line, it records the price from the 1st.

I've tried to get the Sales order number combo box to filter on a field on
the form which holds the item number - I can't seem to make it work.
Ideally I'd like the field to retain the data from the 4th line though.

A solution would be much appreciated.
Thanks

 

[Klatuu]

I would recommend cascading combo boxes. The first being Order Number and
the second being the Item Number. In the After Update event of the Order
Number combo box, re-create the row source for the Item Number combo so that
it filters on the selected Order Number, the requery the Item Number combo.
Then the Item Number combo will contain only items for the selected Order
Number. Your Item Number combo could contain 2 columns, the Item Number and
the Price. So, once you select the Order Number, you will then select the
Item Number, then you will have the price you want to use.

 

[Sebastian Helm]

In addition to what Klatuu wrote, you can make it a bit simpler, if you're
really only interested in the price: Set Bound Column to 2, so you get the
price directly without the detour over the Item No.

 

[Klatuu]

I don't see how you would do that, Sebastian. The price is tied to the item
number. Note the original post.