Update field automatically when certain value is entered in form

[jgremlin]

Hello all. I'm hoping for some guidance as I'm really stuck for a solution. I'm a maintenance coordinator for a small trucking company and I'm putting together an access database to track and manage all of our fleet maintenance. I'm rank amateur when it comes to access but I've been doing ok with it and so far I've been able to get it to do almost everything I set out to have it do.



But I'm having a hard time figuring out how to make this trick work. Here's what I want it to do. We have two groups of trailers, working trailers and storage unit trailers. We do preventative maintenance every six months on road trailers and once a year on storage units. I have a field in tblEquipmentMaster which shows the last PM date. I have a query and an report which use that LastPMDate field as well as the value of a StorageUnit checkbox to calculate what units are due/overdue for PM.

In addition to all this, I've got tables and forms set up to track maintenance records. I'm tracking the maintenance invoices as well as the details of each line item on the invoices (changed water pump, replaced tail light, PM service etc). So what I'd like to have happen is for the LastPMDate field in tblEquipmentMaster to update to the value in InvoiceDate in tblMaintenance anytime the field Description in tblMaintenanceDetails contains the text 'PM' for the unit number in question. Alternatively tblMaintenanceDetails also has a lookup field which contains maintenance codes. So the field VMRSCode could also be used as the trigger anytime the value 'PM' is selected.

I know there are probably a dozen ways to get this done, either via a set of queries or VBA or a combination of both most likely. But I'm really struggling with finding anything that can do this reliably. Appreciate any insight that might get me pointed in the right direction to get this working.

[June7]

So LastPMDate would be a calculated value. Saving calculated data is usually not advised. The LastPMDate can be calculated from the invoices data when needed.

The key word from your post is 'reliably'. Saving data dependent on other data has risks.

[jgremlin]

I hadn't thought of making the last pm date a calculated value. I guess because it isn't really 'calculated' i.e. there isn't really a calculation involved. I currently have it set up so the next PM due date is a calculated value based on the last PM date but I just hadn't really considered making the last pm date a calculated value as well. If that's the way to get this done, I've got some rethinking to do.

[June7]

It would be a very simple calculation. Assuming the destination field is not included in the form RecordSource, an expression in an SQL UPDATE action could save the date. In VBA something like:

CurrentDb.Execute "UPDATE tblEquipmentMaster SET LastPMDate=#" & Me.InvoiceDate & "# WHERE EquipmentID=" & Me.EquipmentID

See the calc in red? Very simple. The real trick is figuring out what event to put code into.

Alternatively, calculate the LastPMDate from tblMaintenance when needed. An aggregate GROUP BY (Totals) query could return the most recent date for each EquipmentID in tblMaintenance.