Use "Find as you Type" function to create a pull sheet

[Uvuriel03]

I have an inventory database that has a whooooole lot of part numbers. Every day, we get orders for particular parts.

Originally, we would create the pull sheet by using a query based on the inventory, and then a report based on that query to make it look nice.

The user would type in the parts they wanted, up to 10 parts. There are a few problems with this though. First of all, if there's a typo on the user's side or when the part was entered into the computer, the part won't come up at all.

Secondly, the user has to deal with clicking "OK" or hitting the enter button on a blank parameter box if they have less than 10 entries. It's annoying and time consuming.

So! I happened across this --> http://www.allenbrowne.com/AppFindAsUType.html <--awesome function that I would like to integrate into my pull sheet.

What I'm thinking is something along these lines:

Instead of initially using a query-based-report, we'll use a form. The user types the part number they want to add to the pull sheet into the search box. They find the correct part number, and now comes the part where I need a way to add the correct part number to a report that lists all instances of those part numbers from the inventory file, like the query would do. And also, I would like them to be able to add multiple parts, so I'm thinking a button to add each new part or something, but I'm not sure what the code behind the button should be/do.

Anyone got any ideas?

Thanks!

 

[Uncle Gizmo]

There are several ways of tackling this problem depending on what you actually want. If you wanted to generate a one off list that would never be used again then you could add an extra field to your parts table this would be a Boolean (Yes/No).

Have a combo box to select a part number. Adjacent to the combo box have a command button that runs an update query that sets the Boolean field to true for the selected part number, Repeat until you have selected them all.

Once you have selected the part numbers then run a report based on where the Boolean selections are true. You will need to set these booleans back to false ones you have completed ready for the next one.

 

[Uncle Gizmo]

there was an earlier post along similar lines which you may find useful here:

 

[Uvuriel03]

I actually figured out a way to make it work. Thanks!!

 

[Rabbie]

I actually figured out a way to make it work. Thanks!!

It is very helpful to other people searching the forum if you can post the solution.

 

[Uvuriel03]

Well, I'm still tweaking it a little bit, but here's the general idea.

First, I created a form and named it Pull Sheet. This Pull Sheet form will have 2 parts to it--one to find the part you want to ad, and one to show what you have already added.

This form should be based off of the parts you already have in your inventory, so it should be based on your current inventory.

I used the Find as you Type search function linked to in my first post to create a filter function. Since this needs to go in the footer section, it ends up in the middle of the form.

The next thing I did was I created a subform based on my inventory to put at the top of the Pull Sheet form. This will act as a part list as a list that narrows itself down as you type in your part.

Next I created 2 queries--An update query and a delete query. The update query I called MakeTempPull, and the delete query I callled ClearTempPull. MakeTempPull is based on the inventory. The column for Parts has the condition "[Forms]![PULL SHEET]![PART]", which refers to a box I located in the Pull Sheet form that displays the part that so far matches what you have typed into the txtFindAsUTypeValue box (see FindAsUType link above). This way, when you run the query, it adds whatever part is in the Part box to a table called "Temporary Pull Sheet". By the way, you'll need to make that table, too. It needs to be exactly the same as your inventory table, just without any data.

The delete query is blank. The reason for this is that upon opening the pull sheet form, I want the Temporary Pull Sheet table to be blank, so this query is run on the OnLoad event of the Pull Sheet form.

Last thing, I think, is to create 2 or 3 buttons. The first one is "Add to pull sheet". On the OnClick event of this button, the MakeTempPull query is run, adding whichever part number is currently in the Part box to your temporary pull sheet.

The second button is "Print pull sheet", which opens a report based on your Temporary Pull Sheet table.

And I currently have a button that refreshes the page, because I haven't figured out a few things yet. First of all, how to keep the page from blanking out if there is no match found, and secondly, how to get the TempPullSheet subsheet to refresh itself upon adding a part to the pull sheet. I suspect something with a nodata event will fix the first problem, and something with a refresh or requery on some event will fix the second. I just have to figure out what to put where.

So that's what I have so far--if you've got any questions and/or suggestions, please feel free to post them!