Trouble using datasheet sub-form as a multi-selection tool

[MeatBytes]

Hello Everyone,



I'm currently having trouble trying to use a datasheet sub-form as a more flexible multi-selection tool, as a list box is far to restrictive when you are dealing with over a thousand items, which you will have to sort/filter through.

What I am trying to achieve is a system where a user who is making a factory purchase order can look in a datasheet sub-form which contains a list of invoiced orders and can select the ones they need by clicking on a check-box. This selection must then be linked to the factory purchase order so that the selection is preserved and can be used for other applications such as populating a list of products that are in those selected orders.

I'm attaching an image which might help you picture the scenario:

I've tried using a many to many table containing the ID's for the Invoice and Factory orders as keys and a yes/no field for the check-box but if I do that then I can't think of a way to have the list show all the Invoice orders and only store the orders that have been ticked otherwise I will have an enormous table on my hands which I want to avoid.

I hope someone can help me with my problem and that I have been clear enough in my explanation as English is not my first language.

Thank you.

[rpeare]

Can you supply a sample database (just make a copy of the elements involved and put in junk data, then compact/repair and zip it up) Please convert to something lower than 2010 though or I won't be able to view it.

[MeatBytes]

Can you supply a sample database (just make a copy of the elements involved and put in junk data, then compact/repair and zip it up) Please convert to something lower than 2010 though or I won't be able to view it.

Sure thing, thanks for taking the time to look at this.

I've tried to trim it down to to the bare minimum of what is needed without having to modify anything.
I hope the number of junk data is enough sorry it well past 2 am here so I'm getting sleepy.

I'm sorry what I gave you doesn't work in the slightest and I know it is a completely wrong way to go about this but I'm currently stuck on how to implement the system I want since multiple commercial OrderID's need to be all present and easily viewable (unless manually filtered) in a datasheet and once the check-box is ticked it should link the selected OrderID's and the single CSOrderID in the passthrough table but not store entries that aren't checked.

I hope I was somewhat legible for writing this so late.

[rpeare]

Ok so let me ask a couple of questions.

The subform showing your purchase orders is currently showing ALL data.
I'm assuming you are trying to build it so it only shows a limited selection of purchase orders which you then want to be able to select then click another button and see the detail of those purchase orders.

So what is your limiting factor, there are no fields on the main form that appear in the subform you can't possibly want to list all orders it would be hugely unwieldy over time. The list in the subform does a calculated order number based on the home company, order received date and the order ID but none of those fields are available on your main form to act as a criteria or filter.

What I would suggest is figuring out HOW you want to search for your PO's. What types of searches do you need, do you want to look up by customer? by factory? by shipping date? what? Your first step should be to LIMIT the number of items that appear in that subform (though I would change it to a listbox, it will perform better than trying to use an unbound checkbox in a subform in spite of what you think about list boxes not working. List boxes can be used to select multiple items if that is your concern) Once you figure out that you can set up a set of search criteria then be able to multi select from a list box and do what you want.


The way you currently have it set up there's no connection from your search to what's being displayed on the page and that needs to change if you're going to make this form easy to use.

[MeatBytes]

Oh, there is no search/limiting criteria for the sub-form list as it was specifically requested to be that way and only use the inbuilt column filters that come with the datasheet as a means of filtering the consumer orders.

I'm not sure what you mean by search as the Clamshell Purchase Order form is designed for data entry(what I've given you isn't finished yet) where the user will fill out all the information required to fill out a report and the entire purpose of the sub-form is to select which Commercial Orders(Which is completely separate from Factory PO's) the Purchase Order will contain. (i.e. The Factory PO will order the material for products that are within Order: MPG-1012-03 & MPG-1112-08)

The reason it is done that way is I've already completed the Commercial Orders system thus each OrderID will be linked to a list of products, packing information and other special requests. The material for these products will then need to be ordered hence the factory PO, I just need a way to link multiple OrderID's to a single CSOrderID in a easily sortable fashion(i.e. Right clicking on the Received Date column allows one to easily view the orders of this week for instance) for both a means of record keeping and in order to maintain consistency so that a user can't mistakenly order to many material or enter the wrong product name.

I hope this clarifies your question.

[MeatBytes]

After re-reading my previous posts I think I need to provide more context to what my problem is.

My Context:
I will be directly referring to the sample database which I uploaded on my second post.
(I apologise to rpeare as the first sample I attached I forgot to fix a few things, I have since re-uploaded.)

Before beginning I believe I need to make one thing absolutely clear, the Clamshell Purchase Orders(ClamshellOrdersT) and Commercial Orders(OrderT) share no values and it will be assumed that by the time the user is using the ClamshellOrderF Form there will be already 10 to 1000 plus Commercial Orders (plus product/packing details linked to OrderID) already made.

Okay, so when you first open the sample database(I recommend using the custom categories in the Navigation Pane) you will see one main form(ClamshellOrderF) and a sub-form(ClamshellOrderListSubF). Opening ClamshellOrderF will show you an early version of the form I am working on but everything that matters for the moment is there, that is going to be used for data entry.

Now the issue I'm having is that the user needs a way to select multiple Commercial Orders and have it so that those selected orders will be preserved for record keeping so that they will always know what Commercial Orders were fulfilled by which Clamshell Purchase Order. These multiple Commercial Order IDs can't just be stored in a single string(which by itself is horrible practice) since they will need to be used later on in that same form to pull up all the products that belong to the Commercial Order IDs previously selected in order to minimise user error.

My Question:
I need a way to always display a complete datasheet (as a subform) of all Commercial Orders in ClamshellOrderListSubF unless it is filtered using the tools offered in the datasheet itself(column filters) but by ticking the check box pull the data from the OrderID field of that specific record and place it in into the CSOrderListPT table under the appropriate CSOrderID and have those tick boxes stay checked when the user desires to view that record again. (And If a user unchecked a box the associated record in CSOrderListPT must be silently deleted not just have the "Included" column value be false, to save space)

P.S. I know I need to change the primary key for CSOrderListPT in order to make this work such as having both CSOrderID and OrderID as a primary key but unless I find a nice way of extracting and verifying the checked OrderIDs then the issue is mute.

I hope this give more detail into what I'm trying to ask and thank you for patient with me as I know my English is sometimes hard to understand.

[rpeare]

You're looking to create a link between the jobs that appear in the window and the current record in your data entry form?

You will not be able to do that with a simple checkbox, especially an unbound checkbox. What you likely will need is a list box with an additional button that creates the link (runs an update query in the background putting the foreign key in your table, or perhaps you can create the link with a click or doubleclick of the list box and have the results displayed as you want them on your form.

You're trying to create a link between two (currently) unrelated tables ORDERT and CLAMSHELLORDERST.

So in your ORDERT table (you can have multiple items in ORDERT for each item in CLAMSHELLORDERST it looks like) you are going to want an additional field that stores the PK of the table CLAMMSHELLORDERST.

I'm enclosing your database with an example of doing this with a list box. You can activate a link between ORDERT and CLAMSHELLORDERST with a double click on the list box. From there you can set up whatever queri you want to display the contents of those orders as a subform on the form in question.

Sample Database.zip

[MeatBytes]

Thank you for you help rpeare,

It isn't exactly what I need but I can work with it and develop it further since in your example you can only link an OrderT record with one ClamshellOrdersT which just won't work for me but is an easy fix.

I'm still going to need to find a intuitive way to allow users to filter the list box but I should be able to make something.

Thanks again rpeare you have been a great help.