auto populate a date field based on two other fields

[macabee]

I have a database that tracks when I ordered Durable Medical Equipment. I have the following fields:

Replacement frequency (i.e 6 months)
Last Replacement Date (1/1/2012)
Refill Date

All fields come from the table named combo box for CPAP
What I want to do is to have the field “Refill Date” to be automatically filled to show the increase from Last Replacement Date and Replacement frequency. (1/1/2012 + 6 months = 6/1/2012
How would that be coded?

[jzwp11]

Welcome to the forum!

In most cases, calculated values should not be stored in a table; they should be calculated on the fly when needed. Your refill date is a calculated value. You would, of course, store the last replacement date and the frequency. You can do the calculation in a queries, reports or forms. I would recommend using the dateadd() function:

dateadd("m",[Frequency],[Last Replacement Date])

[ssanfu]

In a query: (I would use this method)
RefillDate: DateAdd("m",[Replacement frequency], [Last Replacement Date])


In code (a module):
Me.[Refill Date] = DateAdd("m",Me.[Replacement frequency], Me.[Last Replacement Date])

As a control source: (not saved - it is a calculation)
= DateAdd("m",[Replacement frequency], [Last Replacement Date])




FYI: it is not a good idea to use spaces in object names (for many reasons).
Instead of "Last Replacement Date", two better examples are:

LastReplacementDate
Last_Replacement_Date

(See: "The Ten Commandments of Access" http://access.mvps.org/access/tencommandments.htm

[macabee]

Where would i put the code " dateadd("m",[Frequency],[Last Replacement Date])" in a query?

[jzwp11]

From the design grid view, you would just put in the dateadd("m",[Frequency],[Last Replacement Date]) as a field (it will be a calculated field). You can give it the alias RefillDate.

In terms of the SQL text, it would look like this

SELECT fieldname1, dateadd("m",[Frequency],[Last Replacement Date]) as RefillDate, fieldname2
FROM tablename

[macabee]

I used the following in a form
Me.[Refill Date] = DateAdd("m",Me.[Replacement frequency], Me.[Last Replacement Date])
Iran this after update on last replacement date. The problem is I get a compile error = Invalid Outside Procedure. Not sure why.

this is what I coded in my module:
Private Sub dteLastReplacementDate_Change()
Me.[RefillDate] = DateAdd("d", Me.[ReplacementFrequency], Me.[LastReplacementDate])
End Sub

[jzwp11]

Me.[Refill Date] = DateAdd("m",Me.[Replacement frequency], Me.[Last Replacement Date])
Iran this after update on last replacement date. The problem is I get a compile error = Invalid Outside Procedure. Not sure why.

I tried the above in a sample database and it worked fine. Your code in the on change event worked OK too, so I cannot explain why the code in the after update event did not work for you.

[ssanfu]

As a control source: (not saved - it is a calculation)
= DateAdd("m",[Replacement frequency], [Last Replacement Date])

This means there should be a text box control on the form named "Refill Date"
Open the properties dialog box for the control.
On the data tab, in the control source line, paste "= DateAdd("m",[Replacement frequency], [Last Replacement Date])" (without quotes).
Note the absence of "Me."
View the form.


Again, the best place to do this calculation is in the query for the form.
Reason: you are using the dteLastReplacementDate.Change event to calculate the refill date. If someone changes the "Replacement frequency" but does not change "Last Replacement Date", the refill date will be wrong. The new refill date will not be recalculated.
If the calculation is in the query, any change in any control/field will force a recalc. And the refill date will be correct.