Assist to create Data Macro

[rkalapura]

Hai everybody,
I am a new access 2010 user, but has some programing experience in other languages. I am having problem to create a data macro. I have a tooling table with tool size (numeric, 0.00000) and tool serial no (text) fields. I have another table "Job Register" which has several job related infos., which includes the serial no and size of highly precised tooling using for each job. Now I have almost 280 records in "tooling" and 1000+ records in "Job Register". We need to calibrate each tool after it is used certain number of times. For that I need to count the number of times each tool used and want to store this information in a new field in "tooing" table. "Serial no" of each tool is unique but size is not. I should also like to know the count till a certain date. Actually I have managed to get the counts in the form in the field "Usage" with Dcount(), but it is not storing data in the table. Also whenever I add tool details to the "Job Register", it is not changing the counts in form. I need a data macro because first I have to store the present counts of each tools and also any new entry, update or delete event in my "Job Register" should reflect and update in the "tooling" count field "usage". I realy appreciate if someone help me to sort this out with detail steps in data macro (not with VBA code).



Thank you for helping and spending your time for my issue. Best regards

[June7]

Don't see need to store the count total in a 'usage' field and strongly advise against trying to store cumulative data. Generate summary data as it is needed.

You have a table that documents tool usage? Fields something like:
ToolSN
DateUsed
UsedBy

You are using DCount in a Calculation type field to summarize data from the table? Unless you put filter criteria into the DCount, it will return count of all records. If you want to filter for a specific period, build a report and pass this criteria to the report.

[rkalapura]

Thanks for your reply. As you said, I have a criteria in Dcount() to get the total number of times a particular block (in this case each block in the "Jo Block" form) used. A sample of control source property set to "usage" field is given below:

=DCount("[JOB REGISTER].[BLOCK1]","JOB REGISTER"," [BLK1SN] = '" & [Forms]![JO BLOCK]![SERNO] & " ' ")+DCount("[JOB REGISTER].[BLOCK1]","JOB REGISTER"," [BLK2SN] = '" & [Forms]![JO BLOCK]![SERNO] & " ' ")+DCount("[JOB REGISTER].[BLOCK1]","JOB REGISTER"," [BLK3SN] = '" & [Forms]![JO BLOCK]![SERNO] & " ' ")+DCount("[JOB REGISTER].[BLOCK1]","JOB REGISTER"," [BLK4SN] = '" & [Forms]![JO BLOCK]![SERNO] & " ' ")

You can see that its add up the count from 4 fields of table "JobRegister". The "block1" field will never be empty (null) that is why when criteria is true it takes the count of that field. Let me explain little more about the jobs here, to get the size of each job, we use maximum 4 different precise blocks which adds upto the exact size of the finish work. So I have 4 fields in the JobRegister to store the size of these blocks and another 4 fields for their serial nos. I use "Jo Block" form which created from "Jo Block" table to update block records only. When I enter the block size in JobRegister, its fill the rspective serial number to serial number field automaticaly through Dlookup(). These blocks are very costly to buy new and even to recalibrate. So if I could track down the number of times each block used, I can arrange to re-calibrate only those used more than a cetain time and not to send the whole set for re-certification. This is the main idea behind recording the counts of each block usage. I need to have a date range because, once these blocks re-calibrated, it should start counting from one.
The above Dcount() gives me the total number of each block used from my Jobregister, but as I said before, it never updates the new, delete and updated records from JobRegister.
Also, for the audit purpose, it is mandatory to keep the usage and calibration records of each precise tools using for the jobs. That is why the details are stored in a filed "usage" so that any time it can be retrieved.
Hope I have explained the situation and being the programing experts, you all have dealt with such situations many times. Your valuable help will save me a lot. Thanks for helping, in advance. Good day!
Best regards

[June7]

I am not sure what your issue is, no explicit question provided. Do you need to know how to save/update the calculated 'usage' value?

Also, for the audit purpose, it is mandatory to keep the usage and calibration records of each precise tools using for the jobs. That is why the details are stored in a filed "usage" so that any time it can be retrieved.

This can be accomplished by query and calculating the data whenever needed. This is actually better for audit purpose because you can easily provide detail of records that contribute to the calculation.