If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
filter a list box on a form by a field in the record
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 |
#2
|
|||
|
|||
filter a list box on a form by a field in the record
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. "SuzanneShah" wrote: 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 |
#3
|
|||
|
|||
filter a list box on a form by a field in the record
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. |
#4
|
|||
|
|||
filter a list box on a form by a field in the record
I don't see how you would do that, Sebastian. The price is tied to the item
number. Note the original post. "Sebastian Helm" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form, Subform, Tab key | 2nd_Stage_User | Using Forms | 17 | August 25th, 2006 12:30 AM |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Combo Box NotInList - How To Add Data To Underlying Table | 10SNUT | Using Forms | 19 | July 8th, 2005 09:12 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |