Prompt for a tick box to be ticked

[Haynesey]

Hi,

I am designing a procurement database. When we receive a quote for a piece of equipment, we have 30 days in which to order it. I have a field that counts down the number of days remaining that we have to order. Is there any way that after the 30 days are up, the database can prompt the user (if the equipment has not been ordered), asking them if the quote can be closed (a tick box)?

Also, should we order the equipment, is there any way of stopping the count down. The following is the code that is entered into the control source of the count down field:

=IIf(30+(DateDiff("d",Date(),[QuoteReceivedDate]))<=0,"Quote Expired",30+(DateDiff("d",Date(),[QuoteReceivedDate])))

Thanks in advance

 

[doulostheou]

I'm assuming that each piece of equipment is a new record. If so, you would write this in the On Current Event:

'Instead of setting the control source for CountDown at the properties level, set it in the on current event

If isnull(Order) Then
CountDown = IIf(30+(DateDiff("d",Date(),[QuoteReceivedDate]))<=0,"Quote Expired",30+(DateDiff("d",Date(),[QuoteReceivedDate])))

'Also, write a new if statement within the other to evaluate whether or not to send the message

If CountDown <=0 Then
Dim YesNo as String
YesNo = msgbox("The 30 days are up, can this order be closed?",vbYesNo)
If YesNo = vbYes Then
'I'm assuming there is a checkbox to close the order
CloseOrder = -1
End If
End If
End If

 

[Haynesey]

Thanks, that works fine.

cheers
Lee

 

[Haynesey]

Hi, thanks for helping. Wonder if you can help me with this similar problem.

Should I want to order from a quote, I would enter an order date into a field. However, the countdown date would continue to count down. is there anyway of freezing the field so that it always contains the number of days left when ordered.

Also, should I want to cancel an order manually, say after 5 days, I would tick a tickbox. Is there again anyway of freezing the contents of the countdown field so that it always contains the number of days left when ordered??

Please help, im really struggling.

Thanks
Lee

 

[TeaLeaF]

I need help with something similar to this but I'm a total newbie at Access! In my table Items_On_Loan I have the fields Item_Code, National_Insurance (Both key fields), Date_Hired, Date_Due and Renewal which is a Yes/No Data type. I need it to count down 6 weeks after the Date_Hired and ask me if I want to renew, and display a check box. What code do I enter! I tried modifiying the code that the other guy was having trouble with, but sadly, no luck. Thanks in advance!

 

[Rich]

Are there 33 items?

 

[EwoK]

Mmhmm. Hospital Equipment Database. Lol. BAH.

 

[doulostheou]

The only way to store the number of days left (the count down) would be to build it into the table. Just make it a number field storing integers. You will still set the value of the field On Current of each new record, but be sure to condition the statement further. For instance:

If isnull(Order) And CloseOrder = 0 Then
CountDown = IIf(30+(DateDiff("d",Date(),[QuoteReceivedDate]))<=0,"Quote Expired",30+(DateDiff("d",Date(),[QuoteReceivedDate])))
End If

The above example will make sure an order has not already been placed and that the order has not been closed (manually or otherwise). If both of those conditions are true, it will change the value of CountDown. Otherwise, barring CountDown is a field in your table storing integers, the data will be "froze".