Populating a field based on the value in another

[randle]

Hi

I have an inventory database main form and for each item on this another form can be opened via a button providing more detailed information.

I have a "Warranty Period (Yrs)" field on this form which is just a "Number" data type field and a "Warranty Expiry" field which is a "Date/Time" data type field.

I want the latter field to be auto populated when adding the warranty period value which is entered in years. Any ideas how I do this?

TIA

 

[missinglinq]

Assuming you want the warranty expiry date to be X number of years from the date the WarrantyPeriod is entered and that the number of years is always a whole number

Private Sub WarrantyPeriod_AfterUpdate()
 If Nz(Me.WarrantyPeriod, "") <> "" Then
  Me.WarrantyExpiry = DateAdd("m", Me.WarrantyPeriod * 12, Date)
 Else
  Me.WarrantyExpiry = Null
 End If
End Sub

Linq ;0)>

 

[randle]

Bah sorry I forgot to add I have a "Date of Acquisition" field which is also a "Date/Time" data type field and would be from this date that the warranty period in x years would take it's source from.

 

[randle]

Ignore my last update as it's not relelvant and what you mention is exactly what I'm after.

I'm adding the provided code to the "Warranty Period (Yrs)" field AfterUpdate property on the form. Is this right?

Sorry for being a bit dumb here but should the "Me.WarrantyExpiry" be Me.Warranty Expiry" with the space like the actual field name? Same for the "Me.WarrantyPeriod", Shouldn't this be "Me.Warranty Period (Yrs)"?

I mention this because when I enter a warranty period of say "2" I get a compile error, method or data member not found

 

[missinglinq]

If you have spaces in your names, which is considered a poor practice, by the way, you need to enclose the name in square brackets, in order to let Access recognize this fact. So change

Me.Warranty Expiry

to

Me.[Warranty Expiry]

 

[randle]

You are a star. Works very nicely cheers. Slapped wrist for spaces in my field names though. I didn't realise this was poor practice but am more the wiser now thanks

One other thing. Should I want it to take the source date from the "Date of Acquisition" field which is a date/time data type field rather than the date of actually entering the warranty period how would I go about this?

 

[missinglinq]

Me.WarrantyExpiry = DateAdd("m", Me.WarrantyPeriod * 12, Me.[Date of Acquisition])

 

[randle]

Again thank you very much for your help.

Is there anything I can add to the code to make the "Warranty Expiry" field auto update from the "Warranty Period" field should the "Date of Acquisition" field be changed for any reason?

I'm not trying to confuse you honest

 

[missinglinq]

If you want to base the Expiry date on the "Date of Acquisition" field place the code, modified to use "Date of Acquisition," as discussed above, in the AfterUpdate event for "Date of Acquisition."

 

[randle]

I've just done that but now no Expiry date is added when the Warranty Period is populated. I've removed the code from the "Warranty Period (Yrs)" After Update property and placed it in the "Date of Acquisition" After Update property

Private Sub Text86_AfterUpdate()
If Nz(Me.[Warranty Period (Yrs)], "") <> "" Then
Me.[Warranty Expiry] = DateAdd("m", Me.[Warranty Period (Yrs)] * 12, Me.[Date of Acquisition])
Else
Me.[Warranty Expiry] = Null
End If
End Sub

 

[randle]

Hold fire. I've added the code to both fields and is now working as expected. I thought you meant move it there!!

Can I set this to auto-refresh the form as for some reason the "Warranty Expiry" field doesn't auto update when the "Date of Aqcuisition" is changed whereas it does when the "Warranty Period (Yrs)" is modified.

 

[randle]

Got it "Me.refresh"

Thanks again for all your help Missinlinq