Invalid use of Null when trying to calculate a date based on a date in a previous record on a table.

[drunyan0824]

My guess is that you've confused yourself by binding a control to equipmentid and naming it maintenanceid. Your subform has no control by the name equipmentid.

Yeah, I noticed that yesterday. I am going to try to fix that and a couple of other typos in the names of the forms and hope I don't break my database

[drunyan0824]

The dates of the prior records don't change and I don't get any error messages when I click on those dates. However, I am getting an Invalid use of Null error when I try to add a new record to my Equipment_Tracking_Form and then try to create a new record on the Maintenance_FRM_SUB. But I think that I can fix that by first checking to see if I am at the beginning of a new record set for that equipment_ID and setting the Next_Maintence_Due date to the current date or something.

To test what happens to the calculated date if someone enters the wrong date for the time maintenance date, I set the last maintenance date for a date in December when the equipment was due for PM in January and the next record had a maintenance due date in a month later in January. After fixing the last maintenance date, the next maintenance due date didn't change until I clicked on that field. Which I am assuming is because I have the code to calculate the date in the On Click event. A though that occured to me was to set up an error check in case a user tries to enter a date that is before the next maintenance due date or if the date entered is beyond the PM frequency of the equipment.

[mike60smart]

Have you not looked at my example that I posted?

[Micron]

Equipment ID	Maintenance Due	Last Maintenance	Maintenance Preformed	Cost	Engineer_ID
44	9/01/2022	10/01/2022	test	$12.00	dar
44	1/01/2023	12/01/2022	test	$0.00	dar
44	1/01/2023			$0.00

The dates of the prior records don't change

Strange, they do for me. All went to 1/27/2023 from what they were. What's not strange is why - that's an issue with your design, assuming it also really does happen to you. If not, there seems to be a difference between what you're using and what I have.

Equipment ID	Maintenance Due	Last Maintenance	Maintenance Preformed	Cost	Engineer_ID
44	1/27/2023	10/01/2022	test	$12.00	dar
44	1/27/2023	12/01/2022	test	$0.00	dar
44	1/27/2023			$0.00

[drunyan0824]

Thank you for the tip. I really need to work on learning how to debug my VBA code. And lucky for me, you have a link for a debugging reasource

[drunyan0824]

Equipment ID	Maintenance Due	Last Maintenance	Maintenance Preformed	Cost	Engineer_ID
44	9/01/2022	10/01/2022	test	$12.00	dar
44	1/01/2023	12/01/2022	test	$0.00	dar
44	1/01/2023			$0.00

Strange, they do for me. All went to 1/27/2023 from what they were. What's not strange is why - that's an issue with your design, assuming it also really does happen to you. If not, there seems to be a difference between what you're using and what I have.

Equipment ID	Maintenance Due	Last Maintenance	Maintenance Preformed	Cost	Engineer_ID
44	1/27/2023	10/01/2022	test	$12.00	dar
44	1/27/2023	12/01/2022	test	$0.00	dar
44	1/27/2023			$0.00

Yes, there appears to be a design issue. If I click on a previous maintenance due date the date is recalculated. I don't know why I didn't catch that the first time.

[Micron]

Simple fix - check for NewRecord property and only calculate then. Better fix - dump the whole idea of storing the next date?

[drunyan0824]

Yeah, I am thinking that not storing the next date would be a better course of action.

[Micron]

Post 4 and 7 if that helps. Don't know why would you run into the same problem but I guess review first.

[drunyan0824]

Post 4 and 7 if that helps. Don't know why would you run into the same problem but I guess review first.

I guess I am not really sure what is going on in the background when I store a calculated value to a table. To me it seems that I would be just doing the same thing but not writing the calculated value to the table. So when if a changed was made to date when the maintenance was preformed, the next maintenance date would still change on the form.

[drunyan0824]

Am I supposed to put the =LastPMdate +DateAdd("d",30,LastPMdate) in the expression builder or VBA code?

[Micron]

I think I posted that before I saw your db so just using my own terminology/names. I'll take a look at your form and see what I can do (or someone will beat me to it).

[Micron]

Well, there's lots I would change so I'll try to simplify as much as I can.
This date really belongs on the main form since on a per equipment basis, there is only one true value. It shouldn't be repeated over an over on a subform of past maintenance records if it is to be calculated on the fly. Which brings up another point: your current db shows a date for every maintenance record and defines it as the next due date, when in reality, most of those dates are already in the past, so the field name just doesn't work for me.

I'd put a control in the Main form and use its current event because you want to see the next date for each piece as user navigates through records. It can be calculated as

Me.txtMaintDue = DateAdd("m", 1, DMax("Last_Maintenance_Date", "Maintenance_TBL", "Equipment_ID=" & Me.Equipment_ID))

However, your main form is missing data that would be useful. Instead of just "1" for a monthly interval, your form should have the value of "m" in its recordset (not just the PK value from the frequency table), but your table is missing info that's useful for the DateAdd function. You could avoid coding for this if your table was like

Maintenance_Schedule_ID	Description	Unit	Factor
1	Monthly	m	1
2	Bi-Monthly	m	2
3	Quarterly	m	3
4	Annually	d	365

where you swap the interval parameter with Unit and the number with the Factor value in code, such as

Me.txtMaintDue = DateAdd(Me.Unit, Me.Factor, DMax("Last_Maintenance_Date", "Maintenance_TBL", "Equipment_ID=" & Me.Equipment_ID))

Last but not least, your main form should be based on a query that provides all the required fields. Forms like this that are based on tables are seldom adequate. Not sure if I'm helping or not.

[drunyan0824]

Well, there's lots I would change so I'll try to simplify as much as I can.
This date really belongs on the main form since on a per equipment basis, there is only one true value. It shouldn't be repeated over an over on a subform of past maintenance records if it is to be calculated on the fly. Which brings up another point: your current db shows a date for every maintenance record and defines it as the next due date, when in reality, most of those dates are already in the past, so the field name just doesn't work for me.

You make a very good point about having the date on the main form and not repeating have the date repeating on the sub form. That will clean up the look of everything and make it easier to quickly see the when equipment is due for maintenance. I see what you are saying about the field names for the maintenance date. Could you give me advice on what would be better way to name the fields to distinguish between the date when a piece of equipment is due to undergo maintenance versus the date when the maintenance was preformed on the equipment?

I'd put a control in the Main form and use its current event because you want to see the next date for each piece as user navigates through records. It can be calculated as

Me.txtMaintDue = DateAdd("m", 1, DMax("Last_Maintenance_Date", "Maintenance_TBL", "Equipment_ID=" & Me.Equipment_ID))

However, your main form is missing data that would be useful. Instead of just "1" for a monthly interval, your form should have the value of "m" in its recordset (not just the PK value from the frequency table), but your table is missing info that's useful for the DateAdd function. You could avoid coding for this if your table was like

Maintenance_Schedule_ID	Description	Unit	Factor
1	Monthly	m	1
2	Bi-Monthly	m	2
3	Quarterly	m	3
4	Annually	d	365

where you swap the interval parameter with Unit and the number with the Factor value in code, such as

Me.txtMaintDue = DateAdd(Me.Unit, Me.Factor, DMax("Last_Maintenance_Date", "Maintenance_TBL", "Equipment_ID=" & Me.Equipment_ID))

Yeah, I realized that mistake as soon as I first tried to make the changes you suggested. I added a unit field to my Mainten_Schedule_ID table but I didn't think of adding the factor to the table.

Last but not least, your main form should be based on a query that provides all the required fields. Forms like this that are based on tables are seldom adequate. Not sure if I'm helping or not.

I have seen conflicting schools of thoughts on forms based on queries and I guess it is probably a two ways to skin a cat scenario. I've read some posts by people who say that the best practice is to not base a form of queries because it limits the functionality of the form. What do I stand to gain by basing the main form on a query versus a table. Would you base the sub forms on queries as well?

[CJ_London]

just a wild idea - how about

MaintenanceDueDate
MaintenanceCompletedDate