Using a form to populate a range of records into a table

[emjlr3]

I would like to create a form that would allow me to add a range of records into a table.



For instance, when I have chemicals delivered, they are typically barcoded in successive order (something like 4B008200-4B008211). Everything else about the container and its storage location are the same, so the only field in my record for the containers that would change is the barcode. If I receive 15 of this particular chemical, I would like to be able to use a form, input the barcode range (seen above), and have it create a record for each barcode, using a static set of information for the other fields in the table (such as room number, supplier, purchase order, date added, etc.), which I would like to be able to set in this same form.

I found this, which I will try to make work.

Thoughts?

-Thanks

[John_G]

Hi -

You might want to rethink your database structure - it is not properly normalized. You should have separate tables for the delivery orders and the details (separate containers), with a one-to-many relationship between them. You dont want to repeat the delivery information in each container record. It is analogous to the classic purchase order - order details arrangement that is used in teaching MS Access. In your case, a small bit of VBA code should do what you need once the barcode range is entered.

I assume that each chemical container will have other fields specific to it, such as dates or users etc? For updates to these, you will want to look at a form-subform arrangement.

HTH

John

[emjlr3]

I see. Part of the problem is that I was asked to implement a solution once a large inventory was already established. Tracking down shipment information for each container would have been painfully laborious. And starting with only new shipments was not an option.

To be honest, the real purpose of the database is not as extensive as you may think, its simply to: a. know our current inventory, and b. attach MSDSs to containers for easy access to such information. Everything else you see is just fluff really.

But creating this form would almost act like the Delivery table you suggested, without actually having one.

[John_G]

Hi -

Well, no it's not all just fluff (IMO!). From a business and safety point of view, I would say it is vital that you be able to quickly and accurately identify the supplier of any chemical container - you will need that in the event of a problem, such as mislabeling, contamination or shortages.

Putting on my teaching hat for a bit - you used said that you were "...asked to implement a solution...". OK - but what was the problem that was to be solved? When developing a database, that is always the first question to be asked and answered. Define the problem (management is notorious for not being able to do that properly), then determine how the problems can be solved (i.e. with what information). Only then are you able to properly design your data structures and database.

Here endeth today's lesson

John

[emjlr3]

really, from a site wide inventory perspective, we had no real control over if and when it was ever updated or the continual accuracy of updates, so in my group, we wanted something we had control of for reference by us. we wanted to have access to what our inventory was, and the msds associated with the chemical, and that was really the extent of it. everything else I added was just nice to have stuff, because that information ultimately exists elsewhere, at the site wide level.