Run Popup Form under only under certain circumstances

[Ashton12]

I want to a popup form to automatically open onOpen of my main form but only if dates in an "expiration" field in the main form are within the next thirty days. Does any one have any ideas?

[missinglinq]

Do you mean only if any expiration dates occur between today and 30 days from today, or do you mean if there's any expiration dates on or before 30 days from today?

The second would also include expiration dates that fall prior to today's date. This code will do that:

Private Sub Form_Open(Cancel As Integer)
 MinExpDate = DMin("ExpDate", "ExpDateTable")
 If MinExpDate <= (Date + 30) Then MsgBox "Due within next 30 days"
End Sub
 

On Line # 3 you would just replace

MsgBox "Due within next 30 days"

with your code to open your popup form.

Welcome to TheScripts!

Linq ;)>

[Ashton12]

This code seems to work except there are still expiration dates in the field that have already occurred in the past, thus the <= does not work because it will run if there are any dates before today. How would I write the function to exclude these dates?

[nico5038]

I hate popup forms as they force the user to click them away and then it's "forgotten".
I just add a "To do" subform to my frmMain showing the outstanding rows of the table. Thus the user will always be aware that work still has to be done...

Idea ?

Nic;o)

[missinglinq]

Okay, this code only checks for expiration dates between today and today + 30 days. It doesn't include dates BEFORE today.

Private Sub Form_Open(Cancel As Integer)
MinExpDate = DMin("ExpDate", "ExpDateTable", "[ExpDate] >= #" & Date & "#")
If (MinExpDate >= Date) And (MinExpDate <= (Date + 30))
  Then MsgBox "Due within next 30 days"
End Sub
 

Is this what you need?

Linq :)>

[Ashton12]

Sounds like a good idea too. I got this code to work so thanks to both of you guys.

[missinglinq]

Glad we could help!

Linq ;0)>