I receive shipments of research medication. Depending on the study each "kit" can have a different number of medication bottles ranging from typically 1 to 12. Each kit has a unique number and within the kit each bottle has a unique number. One or more bottles from a single or multiple kits may be dispensed to a research person.
Here is reason I am looking for a better solution: the person brings back the bottle/s and I need to put it back into the kit it came from. Since the bottle does not state which kit it came from I had to do extra work to track this info down.
I built a very simple table=tblContents with two fields: KitNum and BottleNum which has made it easy to find the kit based on the bottle when the person returned it. The problem is entering the data. Today I got in 10 boxes with 12 bottles each which means 120 entries.
What I would like to do is enter the KitNum once and then have multiple fields on the same form, so in todays case this would append 12 records at one time to tblContents. If one box had only 6 bottles then it would append six records. The number of bottle fields would either have to be able to expand or not add a record if the field was Null.
I tried some Cartesian product queries, but got not no where.
Any thoughts would be appreciated.