Multiple Recordset Entry form using unbounded textbox

[feathers212]

I am trying to incorporate inventory counts into my database.

Inventory counts are conducted once a week on most of our materials. I want to create a Count Entry Form that will allow the numbers to be entered and then stored in one large table that is then used for reporting on the materials. Once a user enters all of the counts in the Count Entry Form and clicks on a button, the information should then be sent to the Count Table. I want this Count Table to have the following fields: Date, Material, and Quantity. The Count Entry Form and the Count Table are UNBOUNDED and not even connected (Count Entry Form is to draw NO initial info from Count Table).

The materials requiring counts vary based on the category that the material falls into and whether or not the material has been discontinued. My hope is to create the Count Entry Form based off of these criteria first, and then use it to enter the counts as mentioned above.

I have the queries in place to create the lists of materials to be counted. However, I am now unable to create a Count Entry Form that uses an UNBOUND text box for entering the counts. Any method that I have tried so far has failed when I try entering a value into the box (i.e. entering a 1 into one box results in a 1 being automatically populated in all the other boxes as they are all "linked together" as one).

Any suggestions on getting something like this to work?

I have also tried creating a temporary table based on the materials to be counted query that could be linked to the Count Entry Form and be used to temporarily hold the count data prior to moving it to the Count Table. I have not figured out how to add an additional field (count quantity) to this tempory table prior to using it in the Count Entry Form, let alone how to then link this information to my main Counts Table.

Am I going about this in the totally wrong way?? I DO NOT want to create a table that uses Count Date, Material 1 Count, Material 2 Count, etc. as it's fields to keep track of counts as we are constantly adding and deleting materials used. Also, I don't want to have to split the counts into individual material count entries (I already have another portion of the database setup like this, but don't want to use it hear as I feel it would increase the time and effort in entering counts).

Sorry for such a long explanation, just trying to cover everything that I can think of for right now.

Please help!!

 

[RuralGuy]

See if this link gives you any ideas. Inventory Control: Quantity on Hand

 

[feathers212]

I have seen this link on several occassions, but I'm not sure what information in the link is supposed to help me.

I did forget to mention that we do not currently have a way to monitor "disposals" from our inventory stock. The only inputs are counts, orders, and receipts. I realize that this is not the way to look at inventory in the long run, but for now, management wants at least some way to keep track of the information that we do collect. They are aware that we do not have a way to look at current up-to-date quantities on hand, but they at least want a way to look at our usage within certain time periods.

I will be providing queries that will determine an estimated usage based on:
previous count + receipts - current count

That is why I was trying to make my captured information in as usable of a format as possible for querying. I already have a quick system that looks at orders and receipts by material (all captured in a Materials Transaction Table with data, material, transaction, and quantity fields) and I was hoping to do something similar with the counts data until we have the capability to keep a tighter control on our materials used.

 

[RuralGuy]

It sounded to me like you were doing what Allen calls a StockTake. Sorry but I thought you were working toward a complete inventory system. Maybe someone else will drop by with some useful suggestions. Good luck with the project.

 

[feathers212]

It sounded to me like you were doing what Allen calls a StockTake. Sorry but I thought you were working toward a complete inventory system. Maybe someone else will drop by with some useful suggestions. Good luck with the project.

Believe me, I wish we were ready for the complete inventory system. That I think I could handle! Unfortunately, my boss wants me to put something together for right now. It may be several months until we are able to totally implement a real inventory system (hopefully sooner if I have my way )

Thanks anyways for trying to help!

 

[feathers212]

I figured it out. From a main counts screen button click, I started by running a query that pulls the materials for the category that I want to count. This query information was then appended into a temporary table that I setup (any leftover information in the temporary table was first deleted using a delete query). This temporary table was then used to create a subform for an overall counts form. Once information was entered in the counts form (and ultimately in the temporary table) the user can press another button. This button then appends the information from the temporary table into the main table.

Not exactly a direct solution, but I have it doing exactly what I want. Even got around all the query warning screens by using "DoCmd.SetWarnings False" immediately before and "DoCmd.SetWarnings True" immediately after calling the queries in my coding.

 

[RuralGuy]

Great news! Thanks for posting back with your solution.