Updating field in parent table

[Bill Ruting]

I am creating a database to schedule fire inspections for our Department. In the parent table (property information) there is a date field for the scheduled inspection date and a shift field (N2) for the shift which is to perform the inspection. Is there a way to create a link so when the inspection is completed and an entry made in the "inspection" table which is a child to the property info, to update these fields - the date should be advanced by one year, the shift advanced to the next shift (i.e., from "1" to "2", "2" to "3", and "3" back to "1". Can this be done when the monthly report is printed?

Bill Ruting
La Grange, IL, Fire Department

[whanigsberg]

RE: Updating field in parent table

Bill,

You need to open the property table, find the appropriate record, change the date to current date plus a year and close the parent table.

However there are issues which will crop up:
-what if one year later falls on a Sunday?
-do you always reschedule the next inspection when saving the last one?

I would consider not making the change totally automatic by attaching the script to the inspection table's on save event or some such. Instead I might provide a button to reschedule the inspection forward a year. The code could check for a Sunday or Saturday and advance the next date by 1 or 2 days respectively.

If you post an example with your actual table and field names I and many others could provide more detailed help.

Bill

[Bill Ruting]

RE: Updating field in parent table

Bill - thanks. Actually, the date for the new inspection will be shown only as a MM/YYYY - the inspection is scheduled by the inspector anytime during that month.

What I am trying now is to import the scheduled month/year from the parent table into the inspection record, then define a calculated field which will add 365 days to that date, then store that date back in the parent table. See if that will work!

Thanks again,
Bill

[whanigsberg]

RE: Updating field in parent table

That simplifies matters.

Your design works but let me propose another approach if only for theoretical consideration.

If the date of the current inspection (in the inspection table) was stored in a date type field, then you would have a variety of functions designed for date manipulation at your disposal:
-addyears
-addmonths
etc.

At least you should check these out so you know they exist.

Bill

[Bill Ruting]

RE: Updating field in parent table

Bill -

I ended up using the posting properties - first, I imported the current inspection due date into the child table. The fact that an initial inspection record is created satisfies the need for an initial inspection to be done before the date is advanced. In addition, some properties are inspected more than once each year. So, I added a value equal to 365.25 divided by the number of inspections, and added that value to the due date for the current record. The resulting value is then posted to the parent table using the "replace" function. This appears to work well.

After years of struggling with RBase, Paradox and Access, Alpha 5 is a real breeze!