Popup Reminder

A

atticus1802

Registered User.
Local time
Today, 07:55
Joined
May 5, 2010
Messages
43
Hi

I have created a large database for my place of work detailing employees details this includes two renewal dates. I am quite new to access and learning as I go through forums.

I have setup two forms which appear when the database is opened. These are done through an autoexec macro tied to a query and the form. At the moment if there are no records present, the form will still open and be blank, I also have a message box tied to this. I would like the forms to only be present if there is a record due for renewal, how would I go about this.
 
atticus1802 said:
Hi

I have created a large database for my place of work detailing employees details this includes two renewal dates. I am quite new to access and learning as I go through forums.

I have setup two forms which appear when the database is opened. These are done through an autoexec macro tied to a query and the form. At the moment if there are no records present, the form will still open and be blank, I also have a message box tied to this. I would like the forms to only be present if there is a record due for renewal, how would I go about this.
Click to expand...

Here is a working example:

Reminders

Before you open the form, test to see if the form's record source will have any records. If it does, then open the form.



Hope this helps ...
 
Last edited:
Not sure if im using yours correctly, mine will automatically filter any overdue accounts on startup, as soon as the database in open the switchboard will open up as will 2 smaller forms for overdue accounts. If the accounts are overdue the details will be shown, if their are none overdue for that day the form will still appear but blank. Basically I want the form to automatically appear on startup only if their are records present.
 
atticus1802 said:
Not sure if im using yours correctly, mine will automatically filter any overdue accounts on startup, as soon as the database in open the switchboard will open up as will 2 smaller forms for overdue accounts. If the accounts are overdue the details will be shown, if their are none overdue for that day the form will still appear but blank. Basically I want the form to automatically appear on startup only if their are records present.
Click to expand...

The example does exactly what you have described. Try changing the data to have future dates in the example. You will see that nothing displays is there are no records that will appear. No blank for will be displayed

Look at the code in the switchboard's On Load event to see how it works.
Code: 
Private Sub Form_Load()

'On Load of the switchboard check Jobs table for any uncompleted jobs

Dim intStore As Integer

'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[JobNumber]", "[tblJobs]", "[ExpectedCompletionDate] <=(DateAdd(""yyyy"",-2,Date())+14) AND [Complete] =0")

'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
         Exit Sub
    Else
         If MsgBox("There are " & intStore & " jobs needing a 2 year review " & _
           vbCrLf & vbCrLf & "Would you like to see these now?", _
           vbYesNo, "You Have Jobs needing reviewed...") = vbYes Then
              DoCmd.Minimize
              DoCmd.OpenForm "frmReminders", acNormal
         Else
              Exit Sub
         End If
    End If
End Sub
 
maybe I am doing something wrong with your database, whenever it loads the switchboard appears but nothing else does. If i change the dates nothing happens, the query works but it seems there is no popup.
 
atticus1802 said:
maybe I am doing something wrong with your database, whenever it loads the switchboard appears but nothing else does. If i change the dates nothing happens, the query works but it seems there is no popup.
Click to expand...

:confused:
I thought that is what you wanted since in your origianl; post you said:

I would like the forms to only be present if there is a record due for renewal, how would I go about this.
Click to expand...

In the example I gave you it looks for datas over 2 years old. If there are any, then is popup up otherwise nothing happens. You will need to modify this part to meet your needs and to match the design of your databases.

In the example try set some of the dates to be over 2 years old from today. Do they show uo in the popup?
 
Well there are dates set 01/11/2003, 01/11/2004 these appear in the query.

When the database starts up the switchbaord appears but nothing else does. If i double click on the form Reminders the items are shown but this is not automatically displayed.
 
atticus1802 said:
Well there are dates set 01/11/2003, 01/11/2004 these appear in the query.

When the database starts up the switchbaord appears but nothing else does. If i double click on the form Reminders the items are shown but this is not automatically displayed.
Click to expand...

What version of Access are you using?
 
access 2007, i can post a blank version of my database of you want to look
 
atticus1802 said:
access 2007, i can post a blank version of my database of you want to look
Click to expand...

I was think you must be using access 2007. My other suspension is that you have not enabled the VBA code to run for the example database.

To insure that the VBA code runs properly at start up, I would place the reminders example database in a folder that you have added to your trusted locations.

Note: I had a few users that have 2007 download the example and try it and it works fine from a trusted location.
 
Thanks I have sorted it now, the content wasnt enabled now it works and does exactly what I would like it to do, however I am not experienced with code and I have tried to replicated for my own use however not having much luck.


Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between Date()-7 And Date()+7
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore = 0 Then
Exit Sub
Else
If MsgBox("There are " & intStore & " jobs needing a 2 year review " & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "You Have Jobs needing reviewed...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmReminders", acNormal
Else
Exit Sub
End If
End If
End Sub


This is what I have tried howver I am getting errors. Basically I want it to search the table named "contacts" to search for "card expiration date" column. Any dates in this column which are a week before todays date and a week after should be shown in the pop up.

I would also like it to search a second date field if that was possible

Apologises about the question think I have biten off more than I can chew with this.
 
atticus1802 said:
Thanks I have sorted it now, the content wasnt enabled now it works and does exactly what I would like it to do, however I am not experienced with code and I have tried to replicated for my own use however not having much luck.


Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between Date()-7 And Date()+7
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
If intStore = 0 Then
Exit Sub
Else
If MsgBox("There are " & intStore & " jobs needing a 2 year review " & _
vbCrLf & vbCrLf & "Would you like to see these now?", _
vbYesNo, "You Have Jobs needing reviewed...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmReminders", acNormal
Else
Exit Sub
End If
End If
End Sub


This is what I have tried howver I am getting errors. Basically I want it to search the table named "contacts" to search for "card expiration date" column. Any dates in this column which are a week before todays date and a week after should be shown in the pop up.

I would also like it to search a second date field if that was possible

Apologises about the question think I have biten off more than I can chew with this.
Click to expand...

Tip: When posting code use the code tab button ( the #) instead of the Bold ( B ) in the tool bar above this box.

Example:

Code: 
Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between Date()-7 And Date()+7
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
         Exit Sub
    Else
         If MsgBox("There are " & intStore & " jobs needing a 2 year review " & _
           vbCrLf & vbCrLf & "Would you like to see these now?", _
           vbYesNo, "You Have Jobs needing reviewed...") = vbYes Then
              DoCmd.Minimize
              DoCmd.OpenForm "frmReminders", acNormal
         Else
              Exit Sub
         End If
    End If
End Sub

To help you learn Access, I will be glad to work through the errors and help you fix them.

What is the first error message you get and on whick line generates the error?


If you rather post a sample of your databases with some sample data, without any confidential data, I would be glad to look at it.
 
I got a

Compile Error
Syntax error

intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between Date()-7 And Date()+3

Thanks for your help
 
atticus1802 said:
I got a

Compile Error
Syntax error

intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between Date()-7 And Date()+3

Thanks for your help
Click to expand...

I forgot to mention that it is possible to use multiple fields in the criteria for the DCount

It look like you are missing the ending " and )

Try:

Code: 
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between Date()-7 And Date()+3[b]")[/b]

Or try:

Code: 
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between #" & Date()-7 & "# And #" & Date()+3 & "#" )
 
Ok sorted my problems over the weekend. I am now looking to add a second renewal date. In the same table is a renewal date for another item, at the moment this popup is in a seperate form however combining them into the same form is fine.

How would I get the popup to check for the renewal date of "VDU Renewal Date" as well as "Card Expiration Date"


The code looks like this at the moment

Code: 
Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between #" & Date - 7 & "# And #" & Date + 3 & "#")
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
         Exit Sub
    Else
         If MsgBox("There are " & intStore & " records due for renewal " & _
           vbCrLf & vbCrLf & "Would you like to see these now?", _
           vbYesNo, "You Have records due for renewal...") = vbYes Then
              DoCmd.Minimize
              DoCmd.OpenForm "renewal", acNormal
         Else
              Exit Sub
         End If
    End If
End Sub
 
Last edited:
Thanks for all your help I have solved my problems, anything else I need I know where to come :)
 
Actually it started working for a while but now whenever I startup the popup box appears saying there are entrys for renewal followed by a blank form. I havent changed anything within the code the only thing I think I have done is add new records. There are no records due for renewal.


Code: 
Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between #" & Date - 7 & "# And #" & Date + 3 & "#")
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
         Exit Sub
    Else
         If MsgBox("There are records due for renewal " & _
           vbCrLf & vbCrLf & "Would you like to see these now?", _
           vbYesNo, "You Have records due for renewal...") = vbYes Then
              DoCmd.Minimize
              DoCmd.OpenForm "renewal"
         Else
              Exit Sub
         End If
    End If
End Sub
 
Try changing your code to this:

'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[Employee Number]", "[contacts]", "[Card Expiration Date] Between " & DateAdd("d",-7, Date) & " And " & DateAdd("d", 3, Date))
'If count of uncomplete jobs is zero display switchboard
 
I tried changing the code to this however this does not seem to work, the box does not appear if records are due for renewal.
 
You must log in or register to reply here.

Similar threads

J
Solved popup & modal
2
Replies
31
Views
1,921
John Sh
J
Z
Continuous form scrolls to top. Can I prevent this?
2
Replies
31
Views
786
zebrafoot
Z
B
Access subform - Current event does not fire on first record selected
Replies
16
Views
1,564
arnelgp
arnelgp
MajP
Form Instance Example
Replies
2
Views
185
MajP
MajP
J
Unable to reduce form's detail size
Replies
2
Views
201
John Sh
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom