Code strategy (1 Viewer)

vassa

Registered User.
Local time
Today, 05:23
Joined
Jan 26, 2010
Messages
23
c_smithwick,

thank you for the code, it's great for the second part of my task! But is there any way to automate "collecting data into tblData"? I'd like the code to collect that data by itself. Thanks again for your time.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:23
Joined
Sep 12, 2006
Messages
15,613
I'm positive gemma-the-husky would have read your comments before making those suggestions :)

I will try out a possibe solution later.



no, i hadnt to be honest

but in principle having multiple tables for what is effectivelt the same thing just feels wrong

ok - so you have different test types - but everything is just a reading.

its far easier to search ine table, than 12 different tables.

and equally importantly, if you only have one table, you are future proofed - extending the app to cover additional tests doesnt mean a dbs redesign.
 

vassa

Registered User.
Local time
Today, 05:23
Joined
Jan 26, 2010
Messages
23
Here is how I wanted it. User can toggle on/off different Cal_IDs. With one table I will need to set some type of filters on these forms so when the user flips thru records he could see just units from this particular equipment category (in this case consistometer). I am not very concerned with the future expansion. Redoing this whole thing over again will take me 2 weeks, I really want to explore options with VBA before I go to the liquor store, buy a bottle of scotch and redo this whole damn thing.
 
Last edited:

vassa

Registered User.
Local time
Today, 05:23
Joined
Jan 26, 2010
Messages
23
For some equipment categories with just one Calibration Type (or ID) I have forms with continuous view like this. My idea was to create a nice switchboard which user would use to navigate to all these different Equipment category forms
 

c_smithwick

Underpaid Programmer
Local time
Today, 05:23
Joined
Jan 8, 2010
Messages
102
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 recordset. It would take a bit of coding behind your forms, and I don't know how well versed you are with coding, but a bit of normalization of your tables and establishing logical relationships to tie them together should make this a do-able task. Your forms look very professional - did you inherit this task or write it all from scratch? With as complex a table and data structure as you require, you are going to have to do some coding to make it work seamlessly and be user friendly. Access is really very powerful, but if have to stick to macros and all the wizards to write your programs you are missing out on some of the great things you can do. I have a few database "programs" I have written and implemented at work that look really simple through the user interface, but have several thousand lines of code in the modules behind it all. Learn your VBA well and hone your coding skills and it will make your data collection and manipulation MUCH easier.
 

vassa

Registered User.
Local time
Today, 05:23
Joined
Jan 26, 2010
Messages
23
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"?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:23
Joined
Jan 20, 2009
Messages
12,849
I have just looked at this thread so I am not up to speed. It looks very interesting and I will certainly read it all on the weekend. Please excuse if my post now doesn't quite reflect what has been said. I also have to go soon so sorry this description is somewhat garbled.

Add a table say ReadingType with RTypeID, RTypeName and RTypeUnit.

In the actual Readings table along with the EquipmentID include an FK field to the RTypeID. Instead of a field for each reading, separate records are stored against an FK of the equipment being calibrated. Just three fields instead of hundreds.

The readings are displayed in a Subform linked in the usual way by EquipmentID. The Readings Subform RecordSource query joins the Readings to the ReadingType table. Its RecordSource query then has fields including the RTypeName and RTypeUnit.

Instead of the Subform having multiple fixed controls with static labels for reading name and unit it uses bound textbox controls for everything. These display the matching values from ReadingType records as a continuous form.

Any number of new types of readings can be added at any time without changing the table structure or the form design. Extra records just appear on the continuous form when added.

Readings can also be assigned a SortOrder field value in ReadingType table so that they will appear in a standard order if say alphabetical by RTypeName is not desired.

In its simplest form this approach uses a single column of readings but multiple columns can be achieved with multiple subforms. The ReadingType table then includes a field for subform column which is used by the subform RecordSource query to pull the right readings into their column in the SortOrder specified.

This approach can be further extended into a multilanguge application which automatically uses the correct language for the reading name and unit display based on the user's language setting without a single line of VBA code.
 

Guus2005

AWF VIP
Local time
Today, 13:23
Joined
Jun 26, 2007
Messages
2,645
FWIW:

In addtition to what gemma-the-husky (and others) wrote, you have to think about management reports. Management would want to know i.e.

When did the pressure exceed the reference pressure.

To answer this simple question, having all those tables, you will have an enormous query or several queries to get to the bottom of it.

Having records instead of columns your queries will be easier to write, maintain and are faster.

HTH:D

BTW, your forms look impressive!
 

c_smithwick

Underpaid Programmer
Local time
Today, 05:23
Joined
Jan 8, 2010
Messages
102
You asked (in email):
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"?

1. Filtering is easy - you base your form on a query instead of a table and only query for the data you need. If you are good, you can even build the sql statement "on the fly" in vba based on responses gathered (i.e. checkboxes checked or unchecked). The key is to building easy sql statements is your table structures and relationships - hence the push to "normalize" your data tables.
2. You can easily resolve this through normalization as well. Anytime you have data that will repeat multiple times (i.e. same machine, same calibration cycle, multiple readings) you pull off the repeating data and use an ID field to relate it to a "lookup" table. Read everything you can on normalization and table design. This is the one first step you should concentrate most on when designing a database app. The interface is important for usability, but manipulating the data can be eased enormously through good table design.
 

Users who are viewing this thread

Top Bottom