473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to Throw an Alert 1 Month before License Expiry Using VBA Code

Can some one help me on how to write vba code which gives me alert in massage box which shows total number of expired licence one month before expiry date stored in field "expiry_date" in table "licence", and then gives user with an option to view these licence? I have codes which gives expired licence from now not 1 month before.
Below are my code:-
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. 'On Load of the switchboard check Jobs table for any uncompleted jobs
  4.  
  5. Dim intStore As Integer
  6.  
  7. 'Count of expected expired licence
  8. intStore = DCount("[licence_no]", "licence", "[expiry_date] <=Now()")
  9.  
  10. 'If count of expired licence is zero display switchboard
  11. 'Else display message box detailing amount of expired licence
  12. 'and give the user the option as to whether to view these or not.
  13.     If intStore = 0 Then
  14.             Exit Sub
  15.                 Else
  16.                     If MsgBox("There are " & intStore & " licence expired or close to expire " & _
  17.                     vbCrLf & vbCrLf & "Would you like to see these now?", _
  18.                     vbYesNo, "You Have Unexpired licence...") = vbYes Then
  19.                    DoCmd.Close acForm, "frmSwitchboard", acSaveNo
  20.                     DoCmd.OpenForm "expiredlicence_frm", acNormal
  21.                 Else
  22.             Exit Sub
  23.         End If
  24.     End If
  25.  
  26. End Sub
Apr 9 '11 #1
3 3609
RuralGuy
375 Expert 256MB
It seems to me that a WhereCriteria of:
"[expiry_date] > DateAdd("m",-1,Date())")
...would give you the number for which you are looking.
Apr 9 '11 #2
Thanks RuralGuy! Using your code with slight changes it works. I created a query named ExpiredLicenceQuery with criteria [exipired_licence]<=DateAdd("m",1,Date()) and the VBA code chaged to:
Expand|Select|Wrap|Line Numbers
  1. intStore = DCount("[licence_no]", "ExpiredLicenceQuery")
Apr 11 '11 #3
RuralGuy
375 Expert 256MB
Excellent! Thanks for post back with your success and glad we could help.
Apr 12 '11 #4

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

Similar topics

5
by: elyob | last post by:
I've got a number of domains, and want to check the expiry date automatically. There's plenty of whois scripts, but the output from the different whois servers are not always the same. Does...
2
by: Vince C. | last post by:
Hi all. I'm trying to set a cookie expiry date but my script is JS (JavaScript). I've tried Response.Cookies("Test").Expires = Date(); Response.Cookies("Test").Expires =...
6
by: Ray | last post by:
Below is the code to save the cookie and set the expiry date of the cooki Response.Cookies.Value ="Hello" DateTime dt = DateTime.Now TimeSpan ts = new TimeSpan(0,0,10,0) Response.Cookies...
2
by: Dave | last post by:
Hi, Can anyone suggest a way to generate a licence key from C/C++ program? I want to create a licence file include an encrypted text and an expiry date. The private key is hard coded in the...
4
by: William Bradley | last post by:
I have two cells on a form. One of them is the "Production Date" and the other is the "Expiry Date". The "Expiry Date" is 183 days after the "Production Date." On an Excel spreadsheet, the...
2
by: William Bradley | last post by:
"Marshall Barton" <marshbarton@wowway.com> wrote in message news:9as9lvgpnp783kogctb88c8giaepb5uf6g@4ax.com... > William Bradley wrote: > >I have two cells on a form. One of them is the...
3
by: hasanainf | last post by:
Hi all, What will be the best database design for an inventory control that uses expiry date for its products. Over a period of time, a particular product will have many expiry date and that...
0
by: mplpost | last post by:
We have created a Certificate for the purpose of symmetric encryption. We did not mention the expiry date explicitly, only later we found out that, if expiry date not mentioned then the default...
5
by: neelsfer | last post by:
Can one add an expiry date/code of say 12 months to an access application, after which the user must add a new code to continue to use application?
1
by: tutusaint | last post by:
I have a user login authentication scrip which validates users emailid and password. I want it to also check and validate users expiry date in the database. I have user details like name,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.