Birthdays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there anyway with vb code to send a popup message to my desktop to inform
me of a soon to be birthday without having opened my database.
 
Something has to be running. If it's not your database, then you'll need
some other program running that can read your database file to get the
information. There is no way to have things just popup on their own when
they aren't in use.
 
As Wayne says, you can't do this without running a program to do it.

If you use Outlook (as opposed to Outlook Express), the simplest thing
might be just to add a repeating appointment for each birthday, with a
reminder a few days beforehand. To do this under program control from
the database, try looking at www.outlookcode.com or www.slipstick.com.

Witout Outlook, one approach would be to create a script (in VBS or
another scripting language) that queries the database and pops up the
message you need. You could then set this script to run as you log in
(1) or as a scheduled task that runs daily (2).

Another approach (assuming that you're sure to open the database at
least once a month) would be to add VBA code that runs when the database
opens. This would query the database for forthcoming birthdays and
create a scheduled task for each of them (to run just once, however far
beforehand you want). (3)


1) e.g. by including a shortcut to it in your Program Files\Startup
folder.

2) Control Panel|Scheduled Tasks.

3) See the recent thread "Re: How to schedule an access application by
code in Access 2000/2002" in this group for information on creating
scheduled tasks under program control.
 
Is there anyway with vb code to send a popup message to my desktop to
inform me of a soon to be birthday without having opened my database.
Click to expand...

It's easy in VBS:

' ***** start of script *****
Option Explicit

' VBS Script to look up upcoming birthday

Const pathMDBFile = "c:\temp\birthdays.mdb"
Const qdfBirthdaysDue = "BirthdaysDue"

' dao constants
Const dbOpenSnapshot = 4
Const dbForwardOnly = 8

Dim dbe, db, ss ' as DBEngine, as Database, as Recordset
Dim displayText ' as string

' create a db engine
Set dbe = CreateObject("DAO.DBEngine.36")

' open the database readonly
Set db = dbe.OpenDatabase(pathMDBFile, False, True)

' get a snapshot of the data
set ss = db.OpenRecordset(qdfBirthdaysDue, dbOpenSnapshot, dbForwardOnly)

' run through any records that may have been returned
Do While Not ss.EOF

' create a simple string, just concatenate the names
' blank lines are ugly
If Len(displayText)>0 Then displayText = displayText + vbNewLine
displayText = displayText & ss.Fields(0).Value

' next record
ss.MoveNext

Loop

' an empty MsgBox is so unhelpful: use some default text instead!
If Len(displayText) = 0 Then displayText = "No birthdays due"

' tell the user
WScript.Echo displayText

' arrange for a neat closedown
ss.Close
db.Close

' ***** End of script *****


If you save the script to a .vbs file, then you can place a link to it in
your Programs/ StartUp menu and it will display every time you start your
PC. An alternative would be to use the Task Scheduler.

By the way, the text of the "BirthdaysDue" query is as follows:


SELECT FName & " " & LName AS FullName

FROM People

WHERE DATESERIAL(
YEAR(DATE()) + IIF(
DATESERIAL(YEAR(DATE()),MONTH(BirthDt),DAY(BirthDt))>=DATE(),0,1),
MONTH(BirthDt),
DAY(BirthDt)
) - DATE() < 21

ORDER BY DATESERIAL(
YEAR(DATE()) + IIF(
DATESERIAL(YEAR(DATE()),MONTH(BirthDt),DAY(BirthDt))>=DATE(),0,1),
MONTH(BirthDt),
DAY(BirthDt));

It just calculates the date of the next birthday given a date of birth in
the field BirthDt, and selects those where it is less than 21 days away.

Hope that helps



Tim F
 
Thank you for your response,
I built somewhat of an .exe in vb and set the task schedual. it works ok
but, I don't know how to write code so it will check to see if there are any
birthdays and if not it will not open but if there are it will popup. If My
database is opened it will let me know with a popup but I would like the .exe
to check and notify me only if there are birthdays within the next 30 days. I
have 2 querys. How can I get it to check the following querys?
(All Birthday) & (Birthdays)
Here is my .exe code
---------------------------------------------
Private Sub Command1_Click()
A = MsgBox("Do you want to check for upcoming Birthdays", vbQuestion +
vbYesNo, "Birthday")
If A = vbYes Then
Shell """C:\Program Files\Microsoft Office\Office10\MSAccess.exe"" " & _
"""C:\Documents and Settings\Owner\Desktop\Family
Database\Family.mdb""", vbMaximizedFocus

End
Else
End
End If

End Sub
Private Sub Label1_Click()
A = MsgBox("This Program was built to help" & Chr(13) & Chr(10) & Chr(10) &
" you keep up with your Birthday list", vbInformation, "Birthday")
End Sub

---------------------------------------------
 
Hi Alvin,

I'd do it by using a VBScript like the one Tim provided. This can easily
be modified to use your query instead of the one Tim put together, and
with a little more work can check two queries instead of one.

If you prefer to work in VB, you can still use the same technique Tim
used (i.e. open the database using a DatabaseEngine object instead of
Access itself). His code will work in VB with little or no modification.
 
Back
Top