Orders, Items, and Item types (sort on item types, but doesn't work when null)

[neockder]

OK.. so I'll do my best to describe this.. and this is my first post, so thank all of you in advance!!

I have three linked tables. The first contains orders, and the second contains items. An autonumber is generated in the orders table, and a field in the items table links to it.

the third table contains a list of all items, and what kind of item they are.



I have the orders' subform sorting based upon what type of items are found in the items table that match that order. A dropdown box on the master form lets you choose what orders to sort by, given the items in that order match the dropdown box.

this works flawlessly, using a Where condition on the orders subform, linking the dropdown as the criteria.

***The only problem is, if the order contains no items, then it will not be found, because there are no items to link the type to in the dropdown.

How would I set the null value of the dropdown to show all orders, regardless of what kind of items are found in that order?

thank you so much for your help!!! racking my brain right now..

[June7]

An order has many items? Orders should be the main form and OrderItems the subform.

[neockder]

Yep, exactly. There's a main form with two subforms. The first subform is the orders, the second - items. Maybe this question should be in a different section.

One order has several items on it, some in which case have different types of items on it.

[boblarson]

Again, the MAIN FORM should be ORDERS. The SUBFORM that is ON the main form should be OrderItems. So if you have an unbound main form then the OrderItems should be a SUB-SUBFORM on the ORDERS Subform.

[June7]

Correct section. Your forms are not in proper arrangement. You have three tables.

Orders

Items

OrderItems
this table relates orders and items.

Orders should BE the main form. OrderItems should be the subform, probably in Datasheet view. The subform container control Master/Child link properties should be the OrderID primary and foreign keys of the two tables. This will synchronize the child records in OrderItems with the Order records. Items table can be the source of rows in a combobox to select items to associate with an order.

[neockder]

Correct section. Your forms are not in proper arrangement. You have three tables.

Orders

Items

OrderItems
this table relates orders and items.

Orders should BE the main form. OrderItems should be the subform, probably in Datasheet view. The subform container control Master/Child link properties should be the OrderID primary and foreign keys of the two tables. This will synchronize the child records in OrderItems with the Order records. Items table can be the source of rows in a combobox to select items to associate with an order.

Cool. Yeah How I have it set up is basically exactly what you've written here.

I have a master form, a subform containing the orders in datasheet view on top, and a subform linked to the orders on bottom. The bottom subform populates with what order you have selected. Inside the bottom subform there is a subsubform with the items. The items form links as you have mentioned. Everything works flawlessly as far as the forms go.

You select an order in the top subform (which you cannot edit), and it populates the bottom subform with that order, which has fields that you can edit if need be.

The items for that order then populate in the bottom subsubform in datasheet view. The item field is a dropdown will all the items available, with the record source populating from the third - itemtype table. The itemtype table essentially has a list of all the items, and what catagory they fit into

Now: I have a dropdown on the master form that is linked to the item catagories. It pulls up orders in the top subform that contain items with that type or catagory. It works flawlessly as well.

If the catagory dropdown box is null, it will show all orders, as I want it to. **The problem is, if an order has no items on it at all, then it won't show up at all, ever. the order just get's lost. So all new orders never show up, because they don't have any items on them yet.

I need to figure out a way so that when that filtered dropdown is null, it will show all order, including ones that don't have any items on it.

Can i create an item type that is null, so it will pull up null item types? I'm stuck at this point..

Here's a pic of the design view:

[neockder]

Again, the MAIN FORM should be ORDERS. The SUBFORM that is ON the main form should be OrderItems. So if you have an unbound main form then the OrderItems should be a SUB-SUBFORM on the ORDERS Subform.

Just read this.. yes exactly. thats how it's set up. That's why i was thinking maybe i posted in the wrong section,. all the forms are working great! just not the filtering

[June7]

Two subforms with same RecordSource? Never seen that one before.

Here is one method to filter form and handle null. The key point is to use Like operator with * wildcard. http://datapigtechnologies.com/flash...tomfilter.html