Solved: 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).:banghead:

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!!

[OBP]

Hello feathers, I am not sure what you have against a bound form or putting the stock data directly in to a table.
I would use your "Count Criteria" to produce an Inventorying List first so that the stock check matches your data entry form input.
You have come across an Access bug (snag, oddity etc) that with a form in "continuous form" mode you cannot have a "record dependent" unbound field. The field has to be bound to the Recordset, it is possible that this field could be in a seperate "temporary" table, but why bother.
I don't quite understand this
"I don't want to have to split the counts into individual material count entries"
You say that you don't want to create a table that "uses Count Date, Material 1 Count, Material 2 Count, etc."
You are right, that is not the correct format, ideally I think that you would have a "static" Materials Table and linked to it by Product ID a second table that holds the Product ID, Date and count.
If you could post or email me a zipped copy of the database I could probably help you more

[feathers212]

I don't really think that I could use a bound table to enter these counts. We constantly have materials added or deleted from our inventory list. I don't want to have to update a form each time the material list changes. This is what I meant by not wanting to have a table with multiple material field names. I don't want to have to add or delete a field name each time the list changes.


You are right, that is not the correct format, ideally I think that you would have a "static" Materials Table and linked to it by Product ID a second table that holds the Product ID, Date and count.

This is kinda the way that I want to set it up. But rather than one count table per product, I want a count table for all that holds date, count, and material in each recordset. But this is where I am having problems......I want to be able to enter in multiple counts at one time. This again brings me back to the inability to use an unbounded record in a continuous form.

I would zip it up and send to you, but right now the whole thing is in shambles (I have tables, forms, and queries galore as I've been trying out different possibilities). I will try to take a look at the relevant information tomorrow and figure out what items would be necessary for you to understand what I am doing.

Thanks so far!
~Heather


As a side note:
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.

[OBP]

Heather, that is why the "Static" table holds the Product data, to be added, edited as products (not stock levels) change.
All the "Inventory" work is done with the other table, which holds the history of each product audit.
All I would need is your Product table.

[feathers212]

Attached is my "Materials Master Sheet" with some fake filler data. I want to perform counts based on materials that are NOT discontinued, breaking the counts into "Main Category" groupings (counts may happen at different times due to the category that the material falls in to).

Depending on the Main Cateogry chosen, I want to create a form that is specific to that category to enter in the counts. For example, when someone chooses the Main Category 1 grouping, a form is displayed that asks for the numbers for Materials 1-20 (except for materials 9 and 12 which have been discontinued). The same goes for any main category grouping.

Once the numbers have been entered into the form (would like to see in a table like format), the user would press a button and the values for ALL materials would be sent to the "Material Counts" table. Each material count (material, data, count quantity) would be entered in as it's own record.

Any suggestions on how to accomplish this?:think:

Thanks!
~Heather

[OBP]

Heather, you appear to be using Access 97 for this database, this means that I will have to convert it to Access 2000 and then back again, so it will take me a little longer to accomplish this.

Will the same Category count be done on the same day, or different days?
i.e. Do you want to just enter the date once for all records or a different date for each record, or default to the first date for all records, but have them editable?

[feathers212]

Sorry, forgot to mention that I am building this with Access 97.


Will the same Category count be done on the same day, or different days?
i.e. Do you want to just enter the date once for all records or a different date for each record, or default to the first date for all records, but have them editable?

I want the date to be entered just once on the form. Actually, I plan on having the date value for the form default to the current date (although it can be changed if the entries are not made on the exact count date). The date value can then be referenced multiple times when the information is being automatically sent to the table.

[feathers212]

Oh, all counts for one category will be performed on the same day. Counts for different categories could be done on different days. It would be up to the user to determine which count(s) they want to perform and then enter.

[OBP]

Heather, I have finished creating a form that Creates the records for your counting checklist.
Because of the conversion back to Access 97 when you first try to use it the datbase will "Crash" because the VBA Editor will have "Missing" Reference Library Links.
When it crashes click "Debug" and it will take you to the VBA Editor.
On the Main Menu is a small Black Square (like the Stop button on a Video", click that and then on the Editor's Main Menu select
Tools>References.
The editor will list all the Library References, with the "Ticked" ones at the top and show those that are missing. You need to go down your list and tick the equivelant Reference.
i.e. The Microsoft Access Object Library will be missing because I am using Version 10, you need to click whatever version you have.

The new Form will also Print Preview a Report that I have created so that you can either list the Items to be Checked or the Items after they have been checked.
I have also made some minor modifications to the tables and their setup, first of all I have returned the "Lookup" fields i the Materials table as they create a lot of problems in Queries, Forms & Reports. It is far better to have the Combo Boxes on your input Forms based on queries to your List Tables.
I have also changed the Materials Main Category text field to a Number field and changed the words to the Main Category ID (Autonumber) field which I have added to the Main Category table.
I have set "One To Many" Relationships between the Category, Materials and Count Tables.
I have thrown in a Simple Autoexec macro that opens a "Splash" Screen and Main Menu.
The form for viewing and creating the Check lists can be accessed from the first Command Button on the Main Menu.
The actual Check list is shown on this form as a Subform in "Continuous Form" mode.
I hope it does what you want, the way that it works is :-
You select the Main Category from the Combo, Change the Date if Necessary (defaults to today). Click the "Create Check List" Command Button. The records are created in the Table and then displayed on the Subform.
It will obviously display previously entered data by changing the Check Date on the Form to a Date that Counts were made and entered.
The Create Check List" Command Button is not absolutely necessary as the code could run from the Combo Box "After Update" event procedure, but this way is much safer and more versatile because you can change the Category and list a different one without creating any records.

If you need any more help on this you can email me directly.

[feathers212]

I have also made some minor modifications to the tables and their setup, first of all I have returned the "Lookup" fields i the Materials table as they create a lot of problems in Queries, Forms & Reports. It is far better to have the Combo Boxes on your input Forms based on queries to your List Tables.
I am going to have to remember this trick for any future projects. I have had some trouble in other parts of my database, and this quickly cleared it up.

As for the database programming that you posted, thanks. It gave me some more ideas as to how I could approach my problem and get things to work exactly how we needed them.

Here is what I ended up doing:
I created the following tables:
Temporary Counts table - with name and quantity
Main Counts table - with name, date, and quantity

I created the following queries:
Delete Temporary Counts - deletes old information in Temporary Counts Table (while still preserving field labels in table)
Materials to Count by Category - returns material name based on material continuation and category and appends to Temporary Counts table
Add Counts to Main - after counts are added to Temporary Counts, this query appends these counts to the Main Table, along with the count date


From a Main Counts form, a category is chosen from a dropdown and an "Enter Counts" button is clicked. This button runs the the Delete Temporary Counts and Append Materials by Category queries as mentioned below. After that, a Enter Counts form is displayed, pulling up the temporary counts table (which has been done using a subform).

The user is now able to enter in the counts for the category (ultimately entering them into the Temporary Counts table). When the submit counts button is pressed, the coding calls on the Add Counts to Main query, thus appending the newest counts to the table.

From all of this, I also came up with a way to create a manual count sheet that can be used to collect data on the floor prior to entry in the system.

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.

Anyways, thanks again OBP for your time and help. You helped me get my brain thinking outside of the box and in the right direction:thumb