Avatar of ajcostel
 asked on

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


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!
Visual Basic ClassicDatabasesMicrosoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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,
Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.