Equipment Calibration Database - Logic (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 01:56
Joined
Apr 1, 2019
Messages
712
Hi, I'm working on My calibration database. I have a main form containing a ComboBox "Frequency" within it is FrequencyID, Frequency_Desc. (Lookup contains "Annual", "Monthly",bla bla...On the mainform is a linked subform for each Equipment Inspection record. It contains a field "Anniversary" & "Actual_Date". On the first record, "Anniversary" will be the issue date & "Actual_date" will be the actual date that the calibration was conducted (we have a 15 day grace on the date).Subsequent records will be populated with "Anniversary" from the previous record + "Frequency" using the DateAddFunction & a case statement to select correct frequency. I do his because, under certain circumstances the user may be required to select a different "Frequency" for a period, rather that the alternative of working from the original date. Following this, my intention is to export that record as a task to my outlook Calendar. I've never 'Carried forward' a record before, nor exported to outlook. Does this sound a reasonable approach? Appreciate any feedback before I get in too deep!!.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:56
Joined
Feb 19, 2013
Messages
16,553
from your description, sounds reasonable to me. However suggest your frequency should be in the first record, not the second e.g.

tblDates
ID...AnniversaryDate...ActualDate...Frequency
1....1/1/2019.............7/1/2019.....3 Months
2....1/3/2019.............2/3/2019.....2 Months
3....1/5/2019

depending on how your actual process works, perhaps you don't need to store the frequency - just determine it when the actual date is completed?
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 01:56
Joined
Apr 1, 2019
Messages
712
CJ_London, Thanks for the prompt response. Maybe I'd been unclear. My intention is to determine the next 'Anniversary' based upon the sum of the previous "Anniversary_Date" & the selected frequency. That way, if a different frequency is required it can be changed prior to generating a new record. I'm just trying to cover all bases!
 

Micron

AWF VIP
Local time
Today, 08:56
Joined
Oct 20, 2018
Messages
3,476
just determine it when the actual date is completed?
I tend to agree with that premise. The frequency could be a table field with a number based on months/weeks/etc. whose ID should be the frequency value for the tool table inspection frequency field (it is an attribute of the tool). The inspection table should record the last date of inspection only regarding date data. The inspection form shows (or allows entry of) inspection data and calculates the next inspection date based on the frequency value for the tool ID. This suggests a floating next inspection date approach but there are also cases where the frequency is fixed and is not based on the last date.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 01:56
Joined
Apr 1, 2019
Messages
712
Micron, you got it!. I have it working. Got a couple more fields to add (seems to be usual once i start testing). I could not find anything to copy off that did what i need. I'd be happy to post my work, but be aware, it is very much a work in progress. Cheers. What's the best method of carrying forward the 'anniversary' date field to a new record?. Got any ideas?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:56
Joined
Feb 19, 2013
Messages
16,553
What's the best method of carrying forward the 'anniversary' date field to a new record?. Got any ideas?
that's my point. You create a new record from the old record - find the last record, create a new record. It just seemed to me the logical time to do that would be either when the first record is created or when it is completed with the actual date.
 

Micron

AWF VIP
Local time
Today, 08:56
Joined
Oct 20, 2018
Messages
3,476
Loading a form with values from a prior record is a common topic. There's several ways to do this so I'd recommend you research to find one that you like. In this case, I'd do it when calibration was/is just performed rather than have it sitting in waiting. If a tool is removed from service you don't want incomplete records that will be of no use.
Got a couple more fields to add (seems to be usual once i start testing)
This implies your design phase starts with a db and some thoughts. You ought to be designing first on paper. If you use large sheets (like flip chart or packing paper) you can "map out" the whole db in front of you. As you progress, one feature dictates another and/or its supporting table and fields (for which I'll note the data type, indexed and required properties and so on). When happy with the outline I start with queries (seldom basing a form on a table) and if they need to be updatable but are not, there is a flaw somewhere. A form that needs to be updatable but isn't is of no value so why build a form first only to find that it doesn't work?

That's just my take on design.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 01:56
Joined
Apr 1, 2019
Messages
712
Micron, will do some further research. Cheers.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 01:56
Joined
Apr 1, 2019
Messages
712
All, I'm happy!. See my simple code attached. I have it on the update event of the 'Frequency' combobox. Seems to work fine. I'll add in error trapping soon. Thanks to everyone who assisted.

Code:
Private Sub Calibration_Frequency_AfterUpdate()

Dim ID As Long
Dim Temp_Date As Date

   ID = DMax("InspectID", "Qry_Inspect_Equipment")
      
   Temp_Date = DLookup("[Anniversary_Date]", "TBL_Inspect_Record", "InspectID=" & ID)

    Select Case [Calibration_Frequency]

    Case 1 ' Annually
        Me.[Anniversary_Date] = DateAdd("yyyy", [Calibration_Unit], [Temp_Date])

    Case 2 'Monthly
        Me.[Anniversary_Date] = DateAdd("m", [Calibration_Unit], [Temp_Date])

    Case 3 'Weekly (Weekdays)
        Me.[Anniversary_Date] = DateAdd("ww", [Calibration_Unit], [Temp_Date])

    Case 4 ' Daily
        Me.[Anniversary_Date] = DateAdd("d", [Calibration_Unit], [Temp_Date])

Case Else

End Select

End Sub
 

Users who are viewing this thread

Top Bottom