Results 1 to 27 of 27

Thread: [RESOLVED] Alert by showing the Expiry date of a License on a pop up message box ....

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Resolved [RESOLVED] Alert by showing the Expiry date of a License on a pop up message box ....

    Hi,

    I have a database (MS Access) to store Driving License details. I need to remind the user of the License which are going to expire in next month. So, when I start the program it should check the expiry date of license and if it finds any license which is going to expire in a month it should show the employee name and the license number by popping up with an alert message.

    txtEmployeeName
    txtLicenseExpiryDate

    The above two boxes hold the data which is required by a pop up message.

    Regards.


    Seema_S

  2. #2
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Now what u need help?. Are u asking what should be the query to check the expiry date, i think expiration alert is concerned with number of days, add 30 days in current date and check it with license expiray date. If any licesence holder lies in this check print name and other information.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by zubairkhan
    Now what u need help?. Are u asking what should be the query to check the expiry date, i think expiration alert is concerned with number of days, add 30 days in current date and check it with license expiray date. If any licesence holder lies in this check print name and other information.
    Yes, Mr. Zubair. you got the point which I could not explain you clrearly. How it can be done?

    Employee.mdb
    Table = Employees
    Adodb connection = db
    Adodb Recordset = rs

    Fields are:

    EmployeeName
    LicenseNumber
    ExpiryDate

    Please help me out.

    Seema_S

  4. #4
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    If you are using SQL query language, you should apply this

    rs.open "select empname,licNo,ExpDate from employees where expdate-30 = date"


    this will filter all employees those license expires withen thirty days.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by zubairkhan
    If you are using SQL query language, you should apply this

    rs.open "select empname,licNo,ExpDate from employees where expdate-30 = date"


    this will filter all employees those license expires withen thirty days.
    Getting the following error on trying the above querry:

    "No value given for one or more required parameter"

    VB Code:
    1. rs.Open "Select * from Employees where ExpiryDate - 30 [B]= date[/B]", db, adOpenDynamic, adLockOptimistic

    Could you please let me know in the above code "= date" which is not available in the Employees table, from where did we get this one?

    Seema_S

  6. #6
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    try Date() it is system date

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by zubairkhan
    try Date() it is system date
    Thanks for your help, Zubair. Now, I have two new problems.

    1. When there is no ExpiryDate that matches the querry getting this error: "Either BOF or EOF is true....."

    2. It shows the records only when it is exactly 30 days from the date of ExpiryDate. But what I need is something like this: 30,29,28,27,,,,1 (It should show even it is 30 days left for the expiry or 1 day left).

    Seema_S

  8. #8
    Hyperactive Member nagasrikanth's Avatar
    Join Date
    Nov 2004
    Location
    India,Hyderabad.
    Posts
    420

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    after opening the recordset try to check wether it is EOF or BOF...

    VB Code:
    1. rs.open(sql statement)
    2. if not rs.eof or not rs.bof
    3. msgbox "expired"
    4. end if

    and instead of "=date()" use "<=date()"

    hope this will wipes of the problem..
    The Difference between a Successful person and others is not a Lack of Knowledge,
    But rather a Lack of WILL

  9. #9
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    try this
    select empname,licNo,ExpDate from employees where expdate-date()<=30 and expdate-date() > 0)

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Thanks a lot to both of you (Mr. Zubair Khan & Mr. Naga Srikanth).

    In the following statement the First part of statement is working fine while the Second part shows an error:

    First part : select empname,licNo,ExpDate from employees where expdate-date()<=30

    Second part : and expdate-date() > 0)

    Seema_S

  11. #11
    Hyperactive Member nagasrikanth's Avatar
    Join Date
    Nov 2004
    Location
    India,Hyderabad.
    Posts
    420

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    U r most welcome..

    but i too didn't get the logic of the secondpart...

    Khan can explain this a bit better...
    The Difference between a Successful person and others is not a Lack of Knowledge,
    But rather a Lack of WILL

  12. #12
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    The second part is to control dont display expired licenses other withwise query will always display all expired licenses which is useless and bothring.

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,691

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Code:
    "select empname,licNo,ExpDate from employees where expdate - #" & date() & "# <= 30 and expdate - #" & date() & "# > 0"
    The most difficult part of developing a program is understanding the problem.
    The second most difficult part is deciding how you're going to solve the problem.
    Actually writing the program (translating your solution into some computer language) is the easiest part.

    Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read.

    Please Help Us To Save Ana

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Hi,

    I have another querry as follows:

    I want to put two textboxes:

    1. txtTotalDaysLeftForExpiry
    2. txtTotalDaysAfterExpiry

    First txtbox will start counting days as soon as the expiry date will be exact 30 days and daily updates and shows how many days left for expiry...29 days, 28 days, 27 days ..........1 day.

    While the other textbox will start counting days as soon as the date expired and counts like .....1 day, 2 days, 3 days ........so on....

    How to write an sql statements for both?

    Thanks in advance.

    Seema_S
    Last edited by seema_s; May 15th, 2006 at 11:55 PM.

  15. #15
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Pakistan
    Posts
    436

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    well u started a good quiz!

    its not clear, pleas explain it.

  16. #16
    Hyperactive Member nagasrikanth's Avatar
    Join Date
    Nov 2004
    Location
    India,Hyderabad.
    Posts
    420

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    well Mr..zubairkhan, You are right..seems a bit heavy...

    If Im right,What seema_s want is to display no.of days laps by expiry date..After Selecting the Licence No....

    Is it Seema_s???
    The Difference between a Successful person and others is not a Lack of Knowledge,
    But rather a Lack of WILL

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by nagasrikanth
    well Mr..zubairkhan, You are right..seems a bit heavy...

    If Im right,What seema_s want is to display no.of days laps by expiry date..After Selecting the Licence No....

    Is it Seema_s???
    Hi, guys!

    Just we will change the first textbox to work like this:

    First textbox (txtTotalDaysLeftForExpiry) shows the total days remaining for expiry.

    Ex: ExpiryDate - CurrentDate = txtTotalDaysLeftForExpiry

    Second textbox (txtTotalDaysAfterExpiry) shows the total days after the license has expired.

    Ex: It should start when ExpiryDate=CurrentDate and start showing the days that "Your License has expired and it is the 5 th day now" something like this..

    Regards.

    Seema_S

  18. #18
    Hyperactive Member nagasrikanth's Avatar
    Join Date
    Nov 2004
    Location
    India,Hyderabad.
    Posts
    420

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    come-on seema...

    when would u like 2 see the textboxes updated...like by pressing the button or by updating the licence no like that..

    or u would like to see for all the records...

    sorry 2 say that "Im not getting u r exact idea "
    The Difference between a Successful person and others is not a Lack of Knowledge,
    But rather a Lack of WILL

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by nagasrikanth
    come-on seema...

    when would u like 2 see the textboxes updated...like by pressing the button or by updating the licence no like that..

    or u would like to see for all the records...

    sorry 2 say that "Im not getting u r exact idea "
    Whenever I open the project it should show the updated data.

    Seema_S

  20. #20
    Hyperactive Member
    Join Date
    May 2005
    Posts
    307

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    actually easy:
    VB Code:
    1. Dim days as integer

  21. #21
    Hyperactive Member
    Join Date
    May 2005
    Posts
    307

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    VB Code:
    1. Dim days as integer
    2.  
    3. days = datediff("d", ExpiryDate, Date)
    4. txtTotalDaysLeftForExpiry.text = days
    or is it days = datediff("d", Date, ExpiryDate)

    anyway modify it to your needs.....same basic principal txtTotalDaysAfterExpiry.text

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by whythetorment
    VB Code:
    1. Dim days as integer
    2.  
    3. days = datediff("d", ExpiryDate, Date)
    4. txtTotalDaysLeftForExpiry.text = days
    or is it days = datediff("d", Date, ExpiryDate)

    anyway modify it to your needs.....same basic principal txtTotalDaysAfterExpiry.text
    Hi, Getting error: Variable not defined and it highlights the following:
    ExpiryDate

    Seema_S

  23. #23
    Hyperactive Member
    Join Date
    May 2005
    Posts
    307

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    how do you call ExpiryDate from your database? thats what you should put in there....normally i call my fields as rsx!ExpiryDate....depending on what you use to call queries. how do you assign database fields to textboxes?
    e.g. txt1.text = rsx!ExpiryDate?

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by whythetorment
    how do you call ExpiryDate from your database? thats what you should put in there....normally i call my fields as rsx!ExpiryDate....depending on what you use to call queries. how do you assign database fields to textboxes?
    e.g. txt1.text = rsx!ExpiryDate?
    It is also not working.

    VB Code:
    1. Private Sub cmdCalculateDays_Click()
    2. Dim days As Integer
    3. ExpiryDate.Text = rs!ExpiryDate & ""
    4. days = DateDiff("d", ExpiryDate, Date)
    5. txtTotalDaysLeftForExpiry.Text = days
    6. End Sub

  25. #25
    Hyperactive Member
    Join Date
    May 2005
    Posts
    307

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    VB Code:
    1. Private Sub cmdCalculateDays_Click()
    2.       Dim days As Integer
    3.       days = DateDiff("d", rs!ExpiryDate, Date)
    4.       txtTotalDaysLeftForExpiry.Text = days
    5. End Sub

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Quote Originally Posted by whythetorment
    VB Code:
    1. Private Sub cmdCalculateDays_Click()
    2.       Dim days As Integer
    3.       days = DateDiff("d", rs!ExpiryDate, Date)
    4.       txtTotalDaysLeftForExpiry.Text = days
    5. End Sub

    Thanks for the help.

  27. #27

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2005
    Posts
    570

    Re: Alert by showing the Expiry date of a License on a pop up message box ....

    Hi guys!

    Thanks to all of you for the help.

    Regards.

    Seema_S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width