Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Automatic birthday warning prompt! (Access 2000 >)

    Hi all. I have a major question to ask!

    I am working on a DB that contains a Contacts List of policy holders.
    I am needing the DB to inform me of up and coming birthdays (3 days in advance) of policy holders in the Contacts table. The table does contain birthdates of the contacts.

    I was hoping to have it work something like this:
    I open the DB which loads the Switchboard form.
    After the Switchboard loads, a prompt form must activate only if there are birthdays in 3 days time, otherwise no prompt at all.
    I will need to design the prompt form. If there are birthdays I will click on a command button on the prompt form called VIEW BIRTHDAY LIST, which opens a datasheet list (query result) of all contacts who have a birthday in 3 days!

    It is easy to create the form and the command button, and also a query to extract the birthday list. But can someone help me with code to have the form evaluate the query recordset and determine if there are birthdays in 3 days, and then prompt me when the DB opens!

    It's a lot to ask for, but any guidance is helpful!

    Thanx
    Regards,
    Rudi

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Automatic birthday warning prompt! (Access 200

    Assuming your query is called qrybirthdays then, in the onopen event of your switchboard put this code:

    If nz(dcount("*",qrybirthdays),0)>0 then
    msgbox("Birthdays coming up!')
    end if
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic birthday warning prompt! (Access 200

    Thanx 4 your reply John.
    <pre>Private Sub Form_Open(Cancel As Integer)
    If Nz(DCount("*", "qryBDay"), 0) > 0 Then
    DoCmd.OpenForm "frmPromptBDay"
    End If
    End Sub
    </pre>

    Here is the code you gave me modified. I didn't need a msgbox, as I designed a form to act as a prompt!
    It is working well now, but the OnOpen event of the Switchboard opens my form and then puts the Switchboard over the prompt form. How can I get the prompt form to open above the Switchboard.?
    Regards,
    Rudi

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Automatic birthday warning prompt! (Access 200

    Would hiding the switchboard be OK ?

    Private Sub Form_Open(Cancel As Integer)
    If Nz(DCount("*", "qryBDay"), 0) > 0 Then
    DoCmd.OpenForm "frmPromptBDay"
    me.visible =false
    End If
    End Sub


    Then when you close frmPrompBDay you would need to show the switchboard again

    foms!frmswitchboard.visible =true
    Regards
    John



  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic birthday warning prompt! (Access 200

    Hi John.
    Its a good suggestion, but it aint workin'.
    See the code:
    <pre>Private Sub Form_Open(Cancel As Integer)
    If Nz(DCount("*", "qryBDay"), 0) > 0 Then
    DoCmd.OpenForm "frmPromptBDay"
    'Me.Visible = False
    DoCmd.SelectObject acForm, "frmPromptBDay"
    End If
    End Sub
    </pre>

    I even tried SelectObject to bring it to the front. This too is not working. Is it the OnOpen event that dis-allows this you think. I'm not sure???
    Does it work on your side?
    My switchboard stays in front if I use me.visible = false (does not hide) or ObjectSelect!!!
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic birthday warning prompt! (Access 200

    Rudy,
    use this to open your form :
    DoCmd.OpenForm "frmPromptBDay", , , , , acDialog
    and it will always be on top.
    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic birthday warning prompt! (Access 200

    Hi Francois.
    I works but!
    My BDay prompt form has a VIEW button. If clicked, it opens the query so I can view the list of contacts who have birthdays.
    The events occur in this order when I click the view button:
    1. I open the DB
    2. The Prompt now displays on top as it is a dialog.
    3. if I close it itr shows the switchboard ---- GREAT
    4. If I click the VIEW button, it opens the query to display the list and then my switchboard opens over the query.

    I'm back to the same problem as before.

    Johns suggestion will work well to hide...but why doen't "me.visible=false" work?

    Suggestions???
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatic birthday warning prompt! (Access 200

    Why don't you put the query in the frmPromptBDay as a subform ? No click to view, it's there right away.
    Francois

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Automatic birthday warning prompt! (Access 200

    You are right me.visible = false does not work! I tested it now. I don't know why not.

    If you open the prompt form as ACDialog, then it will itself hide the query results when you click the command button.
    Regards
    John



  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic birthday warning prompt! (Access 200

    That is a good suggestion too.
    I will do this and see if all your suggestions and advice get me to where I wanna be. I think this will just work Francois.

    Francois, John --- thanx for all your assistance. Been a great help today!!! <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    Regards,
    Rudi

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Automatic birthday warning prompt! (Access 200

    I have another idea!

    I have been looking at something similar I did.

    Instead of opening the switchboard by setting it as the startup form, have an autoexec macro that runs some code in a function.

    The function can open the switchboard, then test for birthdays, then open the prompt form if needed. This prompt form will then open in front of the switchboard.

    the query results if called will then open in front of that.
    Regards
    John



  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatic birthday warning prompt! (Access 200

    John, you are over-whelming me with info now!!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Let me try ALL the advice you have given, and if still not working well, i'll try this too!

    Many, many thnx!
    Regards,
    Rudi

Posting Permissions

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