Duplicate msgbox pop-up

Hi all,

I have created a form which contains many records. There is a valid date in this form. I want to add a validation checking on the valid date such that if the valid date is earlier than current date, a msgbox will be pop-up to alert the user. I add the following code:

Private Sub Form_Current()

Dim a As Variant
If ExpiryDate < Date Then
a = MsgBox("This Quotation Has Expired.", vbOKOnly, "Quotation Expired")
End If
End Sub

When I open this form, when the first record is already expired, the msgbox will pop-up before the form is opened. How can I open the form first then pop-up the msgbox?

Another problem is when i filter the form with a record that is expired, the msgbox will pop-up twice. Is there another way to do the same thing without these problems?

Thanks and Regards,
Gloria

Hi,

First problem:

Write a sub called CheckDate and include it into your form's code:

'**************************************
Private Sub CheckDate()
If ExpiryDate < Date Then
MsgBox("This Quotation Has Expired.", vbOKOnly, "Quotation Expired")
End If
End Sub
'**************************************

In design view, set the Form's timer property to 10 millseconds.

Inclute the following code into the OnTimer event handler of your form:

'**************************************
Me.TimerInterval=0
CheckDate
'**************************************

And include the following into the OnCurrent event handler of your form:

'**************************************
CheckDate
'**************************************

Hope this helps.

Hi TBÁrpi,

I have tried to follow your steps and made a macro and set the timer. But the second problem still occur, that is when i filter the form, the msgbox will pop-up twice. How to solve this problem?
Many thanks!

Gloria

SmileGloria,
It seems that OnCurrent occurs twice for a reason. Send me the code that filters the form.

Hi TBÁrpi,

I just created a query button on the form. when the query button is clicked, it will call a macro and the action is FilterByForm. No code is written for the filtering! Is it work? Cos for now, the msgbox still pop-up before the form load for the first expired record...

Many Thanks!

Best Regards,
Gloria

Gloria,

In the OnCurrent event handler use
'**************************************
Me.TimerInterval = 10
'**************************************
instead of
'**************************************
CheckDate
'**************************************
Thus, when you step to a record, the timer will be 10 and the sub CheckDate will run. OnTimer sets the TimerInterval to 0 so no action will be taken again unless you step to an other record when the TimerINterval will be 10 again, and CheckDate will run again... and so on.

However, I created a small database and tried to follow your steps of filtering the form using a macro, but the MsgBox did not come twice.

There must be a difference...

Anyway, I enclose the DB and hope it makes sense.

TBÁrpi,

I can solve the two problems by following your steps. It works well! Thanks alot!

BRgds,
Gloria

I am glad, good luck with the development.