Decided to redo the db structure
In answer to your question as to how to get data into "tblData", you simply have to capture the appropriate Cal_ID , Equip_ID and date from your data collection forms, open a recordset against tblData (easiest to do from VBA on an afterUpdate event) and then do an Add/Update against the ...
Thanks a lot for the compliment. I built everything myself from my idea. To be honest I knew nothing about Access and VBA until about 2 months ago. But I wanted to learn a new skill and read 2 books: one on Access 2003 and another one "Access VBA Programming for Dummies" and went through 1 dvd of video lessons on Access 2007. I built my first database to track inventory of chemicals in the lab and it works really good. I decided to take on a much more difficult task to build a database to track calibration and maintenance for all lab equipment to replace an old and very messy Excel spreadsheet. I got it done, but I wanted it to function as application which let people know when and what is due for next calibration or scheduled maintenance.
I consider myself a beginner in VBA, but I managed to wrote all the code for highlighting readings in/out of specs, in some cases it's just simple conditional formatting, but in some it is more complex. I have understanding of recordsets, but certanly not enough for creating special SQL queries for pulling data out of different tables, etc.
Long story short I decided to do it right and redo my db structure and to compile all claibration data in one table, then I'll try to use the code you have provided to do the rest.
A few last question though for you and for Dave with husky. I want to keep the forms I designed for each category and base all 12 on one table. Is that a good approach? if it is then:
1. How do I get them to filter data so the user could see only units from that category for which the form was created (some sort of filter for form "On load" event)?
2. And from Dave's comment "ok - so you have different test types - but everything is just a reading", does that mean I have to name columns in calibration table something like reading1, reading2, etc instead of meaningfull names I have right now, say "RecPres1, GaugeTemp..3"?