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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial