Populate report with unique records chosen from a form

[m1nd64m3]

What I have so far is a simple inventory database. I have a search form that searches for the parts in the database and populates a combo box with the results, then you double click on the item and that pops up another form that displays all of the information related to that part along with how many are in stock. On this last form I am able to type a number in a box to either add/remove inventory to the new/used stock, and then click the update button and it updates the inventory. From there you click the save and close button and it will update the amount on the table and save it. the text box(s) where you add or remove stock are unbound, while the rest are bound to a table.

What I am trying to do is generate a material transfer report. I want to have that last form with the details populate a report when I click the "add to material transfer" button. I want it to pull the details from the form and add them to a report along with the number added/removed that is typed into the unbound text box. I want this material transfer report to update as new items are added to it, by going thru the search function, then to the detail form, then click "add to material transfer". So that in the end I have a report with different records on it reflecting the parts that were transferred in or out of stock.



I have searched and tried different things but I am unable to make this report update with new items as I add and remove items. So far I am only able to make it work with single Items on their own seperate reports.

[m1nd64m3]

I was thinking of another way that may be easier if this is impossible or to complicated. Could I transfer the specific records over to another forms combobox and then have a report that is based off of the records in the combobox from the form mentioned in the above sentance, then just have a "print material transfer" button on the form with the combobox that would print out the report. I'm new to access and this is the last roadblock in the way of getting this database to fully work.

[rpeare]

Are you generating a record on a table for every addition and removal of an item from inventory or are you only tracking the total items you have in stock? If you are not generating a 'history' table of all additions and subtractions of the item there's no way to do what you're asking. Any information you do not specifically store the database will have no way of reporting.

[m1nd64m3]

The database tracks the quantity new and the quantity used by using a calculation in the form and saving that calculation over the original amount. So if there is 5 new of an item in stock, then if I remove 2 of them I would put 2 in the "remove from inventory" unbound text box, then I hit the "update Inventory" button and it replaces the 5 with a 3, and then saves it to the table when I close it.

But I dont need to put the calculated number in the report, just a list of the part number, the mfr, and the quantity removed that was in the unbound text box (would be the 2 in the above paragraph).

So like you were saying, everything gets stored back in the table, except for the amount in the unbound text box, which I would like to put in the report.

[rpeare]

That's exactly the point, you are not storing the information about what you take out and when so you can't report it. If you wanted to track subtractions from your inventory you would have to have a separate table that you would have an identifier field (autonumber) the part number, the date it was removed from inventory and the quantity removed from inventory. The way you are describing what you want is not possible unless your requirement is that you be able to print out a record once, and only once, and after that print out you do not care when an item was removed (not for auditing, not for tacking usage by month, nothing) then you could probably print a report BEFORE you perform the update to your table.

[m1nd64m3]

Would it be possible to populate a temporary table or query with the info from the current record and others as the person takes things from inventory and then once the user is done removing parts from inventory a report could be generated off of the temporary table, and then have the table clear itself.

I basically am trying to create an invoice to sell items from the inventory to a specific job, and populating that invoice with the specific part numbers, then the invoice is handled by someone else. So its just a one time report each time.

I know the way I am doing it is not the best way, but its just a simple inventory that doesn't need any more data about tracking usage and dates and such, but if that is the only way to get this report function to work then I can give it a go. Where can I find more about doing inventory in this way? I have looked over the northwinds database but that is incredibly in depth and I have checked out the page made by that Allan guy but I still dont fully get it.

[rpeare]

The way you mentioned is likely the only way to do it, particularly if in the process of selling parts to a job you can sell multiple parts (which I expect is the case) So if you were to append a part ID and a quantity and a customer ID to a temp table you could probably generate an invoice for that customer. Just a word of warning, keep an original copy of the invoice because if it gets lost in the mail you will not be able to re-generate it through your database without putting in fake numbers then trying to back them out again.

[m1nd64m3]

Yes the multiple parts thing you mentioned is the case. I think I will try the temp table method, for the invoice thing I will set it up to print a copy and also have it print a pdf version to the computer.