Question Calculate a best before date with code

[Nic]

Hi,
Iv got a form with various text boxes and cmd buttons on it. Im using it for a stock/inventory tracking purposes. I would like some sort of visual prompt for when a stored item is nearing its best before date or when an item gets x% through its shelf life.

Therefore, i have a text box with the products BBD in it ie. Sep13. I also have a text box with the products total shelf life (ie. 12months). Calculating this is easy, as its simply 'now' date add 12months etc. However i when the item gets eg. 50% of the way through its shelf life i would like the text box backcolour to turn red.
So i would like to put some code behind a cmd, which when clicked this operation is carried out.

Hope that makes sense,
Any suggestions greatly appreciated,

 

[vbaInet]

Few questions:
1. Is the data type of the date field Date/Time?
2. Do you need the time part to your date field?
3. Do you know about Conditional Formatting?

 

[Nic]

Hi,
1. Yes, data type is Date/Time
2. No, dont need the time part.
3. Yes, aware of conditional formatting but dont think it would work in this instance.

Awaiting your suggestions with interest!
Thanks

 

[vbaInet]

If you don't need the time part then change Now() to Date()
Why would Conditional Formatting not work?

 

[Nic]

Hi,
Im not sure about the conditional formatting cos my text box contains a product code (4numbers) and also the best before date ie. Sep13. As far as i understand, conditional formatting would be for the data in the actual text box. Where as, actually, my text box is only there for 'user' conveinance and easy of info being seen by an operator. Therefore, i need some code (i think) which on the click of a button, will go off, look at data in a table sitting in the background and from this will calculate the number of months left and dependent on set criterial will set the backcolour of the text box.

Hope this helps clarify my dialemme!
Thanks.

 

[vbaInet]

Conditional Formatting works off the value in a field or the value in an unbound control or whatever value you give it.

So go ahead and try conditional formatting before dismissing the idea. Use the name of the unbound textbox if you want to refer to it. If you want to pull data from a completely different table, you can use a DLookup fuction as part of the expression.