Link to home
Create AccountLog in
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!
Avatar of PatHartman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of ajcostel


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,
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.