By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Run Popup Form under only under certain circumstances

P: 8
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?
Oct 8 '07 #1
Share this Question
Share on Google+
6 Replies

Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  MinExpDate = DMin("ExpDate", "ExpDateTable")
  3.  If MinExpDate <= (Date + 30) Then MsgBox "Due within next 30 days"
  4. 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 ;)>
Oct 8 '07 #2

P: 8
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?
Oct 8 '07 #3

Expert 2.5K+
P: 3,072
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 ?

Oct 8 '07 #4

Expert 2.5K+
P: 3,532
Okay, this code only checks for expiration dates between today and today + 30 days. It doesn't include dates BEFORE today.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. MinExpDate = DMin("ExpDate", "ExpDateTable", "[ExpDate] >= #" & Date & "#")
  3. If (MinExpDate >= Date) And (MinExpDate <= (Date + 30))
  4.   Then MsgBox "Due within next 30 days"
  5. End Sub
Is this what you need?

Linq :)>
Oct 8 '07 #5

P: 8
Sounds like a good idea too. I got this code to work so thanks to both of you guys.
Oct 8 '07 #6

Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Oct 9 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.