Message of the day pop-up?

[A Paid Observer]

I have looked through the posts thoroughly, and couldn't find one that would
help me to do what I want. If someone has answered this previously, my
apologies.
I want to create a "procedure of the day" message feature. I have a table
full of hundreds of written policies for various departments in my company
(tblPolicies,) and I would like to highlight a different policy each day in a
pop-up form for my Surveillance department employees to read. I have used a
randomizing query to pull up a random policy from the table on demand, but I
would like for just one policy to be the pop-up all day today, and for a
different policy to pop up all day tomorrow, etc.
I'm guessing the way to do this would be to program the query to rearrange
the records at say, midnight each day, then select the TOP record for my
message. However, I have no idea how to do this. Can anyone help me?

Thanks in advance!

 

[John Spencer]

If I were doing this, I would have a utility table that had two fields
LastDate
ProcedureID

Then in the startup procedures of my database:
-- Check the last date field.
-- If it is today's date then show the procedure associated with the
ProcedureId
-- If LastDate is some other value, then update the values (last date =
Date() and ProcedureID = the next randomId generated by your randomizing
query. And then show the procedure associated with the procedureId

If you wanted to, you could just add a last shown date field to your
table of policies and check for today's date being = to the max date
stored in the policies table. If it isn't, select a record from
policies that does not have a date (is Null), set its date to Date() and
show the newly selected policy.

You would probably need some additional checking to make sure that at
least one policy had the last shown date as null and if none did, you
would need to update the last shown date to Null for all the records.

Do you understand how to do the above or do you need someone to write
the VBA procedure?

--

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[A Paid Observer]

Thank you for your interest, John.
How would I go about populating this LastDate field, and making it hold a
value from a previous day, rather than the current date? Would I have to
assign a specific date to each Procedure, or would dates be assigned
randomly? I guess my answer to your last question would be no, I don't know
how to do it.

Alternatively, is it possible to write code that would just rearrange the
records in a table or query, perhaps in the OnTimer event of a hidden form?
If I could do that, then I could just have the records shuffled daily, and
pull the top record from the table or query. If not possible, then would you
be kind enough to give me some guidance for your idea?

 

[John Spencer]

Create your table (tblMessages) with three fields
PKID - Autonumber Field
TheMessage - text or memo field
LastShown - Date field

Create a form (ShowMessage) based on a query against the table that selects
the record where the date in LastShown matches today's date
SELECT TheMessage FROM tblMessages Where LastShown = Date()

In your start up routine call the following code.

Paste the following code into a VBA module and save the code
'========== Code Starts ============
Public Function fMessageOfTheDay()
Dim strSQL As String
Dim LReset As Long
Dim dbAny As DAO.Database

Set dbAny = CurrentDb()

'If all messages have been used then initialize them
LReset = DCount("*", "TblMessages", "LastShown is Null")

If LReset = 0 Then
strSQL = "UPDATE tblMessages SET LastShown = Null"
dbAny.Execute strSQL, dbFailOnError
End If

'If there is no message for today then get one
LReset = Nz(DLookup("PKID", "tblMessages", _
"LastShown=" & Format(Date, "\#mm\/dd\/yyyy\#")), 0)

If LReset = 0 Then
strSQL = "SELECT TOP 1 PKID, RndNum(pkid)" & _
" FROM tblMessages " & _
" WHERE LastShown is Null" & _
" ORDER BY RndNum(PKid)"
LReset = dbAny.OpenRecordset(strSQL).Fields(0)

strSQL = "UPDATE tblMessages" & _
" SET LASTSHOWN = Date() WHERE PKID =" & LReset
dbAny.Execute strSQL
End If

'Open the form and show the message
DoCmd.OpenForm "ShowMessage"
dbAny.Close

End Function


Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function
'================== End Code ====================

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[A Paid Observer]

John, I'll try that out and see if it changes the message tomorrow. Thanks
very much! I'll let you know how it worked out.

 

[A Paid Observer]

Sorry for not responding earlier. This worked rather well, resulting in one
specific message being selected from the table each day, and popping up
whenever an operator calls it. Thanks very much for the code and your help
John.

Cheers!