PopUps

[Peter Simpson]

Hi Guys

Please can you help with adding a popup In my DB

GOAL
I need a simple popup that appears when I open the DB to remind the user to process / capture the salaries on the last day of every month.

I have a PopUpDateTbl table with fields PopUpID_Fk and Date

I have a PopUpTaskTbl table with fields PopUpID_Pk and TaskDateOrigin and Task and DueDate

I Have a PopUpTaskQry Query with Fields Task and Date with a report that matches

DIFFICULTIES



1. What code do I use to have the popup apper when the DB is opened on the last day of the month?
2. Where do I put the code? Query/report/form/switchboard

[ranman256]

on the switchboard,in the form LOAD event, check the popup table:

you could open a form (pop up modal mode) to show the items in the table that meet the date.


for last day of month:


LastDayOMonth: dateadd("d",-1,dateadd("m",1,month(date()) & "/1/" & year(date())))


if Date()=dateadd("d",-1,dateadd("m",1,Date())) then msgBox "Run Salaries"

[Peter Simpson]

Thanks ranman256

Do I open the switchboard in design view then create an event in the event tab under "on load"?

[ranman256]

yes, then write in the end-o-month code.

If your queries can determine date they too can open as a 'datasheet' to show whats needed.
if Dcount("*","qsDatesDue")>0 then docmd.openquery "qsDatesDue"

or like that.

[Peter Simpson]

Ok I used the code if Date()=dateadd("d",-1,dateadd("m",1,Date())) then msgBox "Run Salaries"
as you suggested in the On Load event of the switchboard. I did not get any errors but it does not seem to do anything. What am I missing and doing wrong?

[June7]

I tested the expression: DateAdd("d",-1,DateAdd("m",1,Date()))

Today's date is 11/21/2017, the calc returns 12/20/2017.

Simpler expression to return last day of month: DateSerial(Year(Date), Month(Date) + 1, 0)

[Peter Simpson]

Hi June7

I don't understand

[June7]

Edited my previous post, possibly after you read it.

Ranman is suggesting code that will popup a message box on the last day of the month.

[Peter Simpson]

Like this?

[June7]

Yes, so nothing will 'happen' until the last day of the month when user should get the MsgBox popup. The code runs every time the form opens, just no message unless it is the last day of the month.

[Peter Simpson]

thank you very much. I don't want to be annoying but is there a way for me to test it now?

[June7]

Test what? Whether the procedure runs? Or if the DateSerial correctly calculates? Or if Date = the calc.

Set a breakpoint on the If line in the procedure and execution will stop there and you can step through to see how it progresses.

Use VBA Intermediate Window to test code. Type in the window and press Enter after each line:

x = #11/30/2017#
If x = DateSerial(Year(Date()), Month(Date()) + 1, 0) Then MsgBox "Run Salaries"