How to auto populate a bound control within subform with sequential numbers.

Hello,

I created a database to record laboratory freezer inventory. The freezer is divided by shelves. On each shelf is a rack, which holds boxes. Each box contains 81 different specimens. In this database there is a form titled, "Test," that is bound to a table titled, "Molecular Freezer Inventory Mapping." Within the main form is a subform titled, "TestSubform," that is bound to a table titled, "PHIMS/OpenELIS Clinical Specimen Numbers." The Molecular Freezer Inventory Mapping table is mainly used to document: BoxID, Year, Analyte, SpecimenMatrix, Freezer, Shelf, Rack and Box. The PHIMS/OpenELIS Clinical Specimen Numbers table documents: PHIMS/OpenELISNo, MatrixVolume, BoxPosition and BoxID. BoxID is used to link these two tables together.

Here is my problem...
My form is working correctly; however, I would like to have Access automatically populate the BoxPosition number for me upon creation of a new record within the subform. I found a bit of code, provided by Stephen Lebans, but I don't know how to alter it for bound controls. Any help would be greatly appreciated! I have attached a version of my database for reference.

Thank you very much!
Test-Molecular-Inventory-Copy83115.accdb
ajcostelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
1. your column and table names are riddled with spaces and special characters.  Getting rid of all of them now will save you a great deal of trouble.
2. Creating names that start with spaces/special characters is about the worst thing you can do.  Those fields MUST be changed before proceeding.  So [= RowNum] will most definitely cause a problem.  In fact I just spent an hour fighting with a spreadsheet I was trying to import that caused functions to fail because field names started with spaces (yes, more than one).  I ended up changing all the column headers before importing the file.
3. What you have done with [= rowNum] is completely mystifying and it is hard to baffle me but you've done it!!!  PS - it doesn't work.  Add a few rows and then delete one.  See what happens when you start adding again.
4. Your relationships are incorrect.  Why do you have join lines to four separate instances of the Mapping table.  If you want to join on four fields, just draw four join lines between the two tables.  However, in this case, I suspect that Freezer, shelf, rack, and box don't belong in the inventory table at all.  Instead, you should include the ID field from the Mapping table as the FK.  And that brings me to a pet peeve.  Why name every primary key "ID"?  It does nothing except cause confusion.  They should be SpecimenID, MappingID, InventoryID.  That way when you use them as foreign keys, you have a name that makes sense.
5. I'm going to guess that instead of having a bunch of unbound controls that you are using to copy data from, you really need to use the Mapping table as the parent table.  Pick a record in it, then in a subform add the inventory details.  All the common fields are in the mapping table and only the unique fields are in the inventory table.

Please do some reading about database design and normalization to clear up some fuzzy thinking.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ajcostelAuthor Commented:
Thank you for your helpful comments Pat! I am new to Access, which you probably gathered, but trying/willing to learn. I believe that I have corrected all of your points:

1) Spaces and special characters have been removed from column and table names.

2) The "[=RowNum]" was an attempt to have Access automatically populate my box positions. I have attached a brief work flow to help explain my predicament.

2) I addressed my relationship issues and hope that you will approve. I now have one parent table, InventoryMapping, and two child tables, SpecimenInventory and ReagentInventory. The primary key for the parent table is BoxID which links to the child tables via one-to-many relationships. Each child table has its own unique autonumber primary key (SpecimenID or ReagentID). Please find attached the new and improved database. By the way...The new data entry form is titled, "InventoryMapping" and the respective subform is titled, "SpecimenNumbers subform."

Again, I can't thank you enough for addressing my data integrity issues.

Do you have any idea how I can have Access populate the box positions for me?

Thank you,
Andrew
Work-Flow.docx
Test-Molecular-Inventory-Copy83115.accdb
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.