display a message when a date expire

My database Members has a frmMembers and a subform Drivinglicence, both are
conencted by MemberID. The subform has got these fields: MemberID,
DrivingLicenceType, StartDate and EndDate. How  would I set this up so when
the database is opened or when i open the subform a message appears reporting
about the Enddate regarding  the driving licence of that Member  is about to
expire in 6 months or less so this way I can inform them that they should
renew their driving licence.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
JOSELUIS
3/17/2010 11:03:19 PM
access 16762 articles. 3 followers. Follow

5 Replies
1431 Views

Similar Articles

[PageSpeed] 23

"JOSELUIS via AccessMonster.com" <u58123@uwe> wrote in message 
news:a52a03c5a85cf@uwe...
> My database Members has a frmMembers and a subform Drivinglicence, both 
> are
> conencted by MemberID. The subform has got these fields: MemberID,
> DrivingLicenceType, StartDate and EndDate. How  would I set this up so 
> when
> the database is opened or when i open the subform a message appears 
> reporting
> about the Enddate regarding  the driving licence of that Member  is about 
> to
> expire in 6 months or less so this way I can inform them that they should
> renew their driving licence.

You could add a command button to the form to filter the form's record 
source to just display members who meet your criteria. You could also add a 
feature to automatically email everyone who meets your criteria to automate 
the notifications. 

0
Paul
3/17/2010 11:24:01 PM
On Wed, 17 Mar 2010 23:03:19 GMT, "JOSELUIS via AccessMonster.com"
<u58123@uwe> wrote:

>My database Members has a frmMembers and a subform Drivinglicence, both are
>conencted by MemberID. The subform has got these fields: MemberID,
>DrivingLicenceType, StartDate and EndDate. How  would I set this up so when
>the database is opened or when i open the subform a message appears reporting
>about the Enddate regarding  the driving licence of that Member  is about to
>expire in 6 months or less so this way I can inform them that they should
>renew their driving licence.

Well... when you "open the database" you're not necessarily opening this form;
and you can't "open a subform".

I'd suggest putting code in the Current event of frmMembers, which executes
whenever you navigate to a record on the main form. Rather than an intrusive
message box ("Look, dammit, I know, I saw that message yesterday and the day
before!!!") I'd put a label control lblExpiring on the form. The code would be
something like:

Private Sub Form_Current(Cancel as Integer)
If Me.NewRecord = False Then
   If Not IsNull(DLookUp("MemberID", "DrivingLicenses", _
        "[MemberID] = " & Me.MemberID & _
        " AND EndDate < DateAdd('m', 6, Date())") Then
         Me!lblExipring.Caption = "LICENSE EXPIRING SOON"
         Me!lblExpiriing.ForeColor = vbRed
   Else
         Me!lblExpiring.Caption = "License OK"
         Me!lblExpiring.ForeColor = vbBlack
   End If
End If
End Sub

-- 

             John W. Vinson [MVP]
0
John
3/17/2010 11:37:02 PM
I´m really sorry but maybe I don´t explain myself correctly and therefore an
error message is displayed in the code and in english is more or less that
Msaccessjet can not  find the tbl or entryquery "drivingID".
FrmMembers is based on qryMembers which is connected with tblMembers(MemberID,
FirstName...) and fsubdrivinglicense are based in tblDrivingMembers (MemberID,
DrivingID,StartDate,EndDate) both are connected by MemberID so I wriote the
code changing only "DrivingLicenses" by "DrivingID" but It could be that i
have to explain a little more in the expression DLookUp( "MemberID",
"DrivingID"...
Thank you for your time.

John W. Vinson wrote:
>>My database Members has a frmMembers and a subform Drivinglicence, both are
>>conencted by MemberID. The subform has got these fields: MemberID,
>[quoted text clipped - 3 lines]
>>expire in 6 months or less so this way I can inform them that they should
>>renew their driving licence.
>
>Well... when you "open the database" you're not necessarily opening this form;
>and you can't "open a subform".
>
>I'd suggest putting code in the Current event of frmMembers, which executes
>whenever you navigate to a record on the main form. Rather than an intrusive
>message box ("Look, dammit, I know, I saw that message yesterday and the day
>before!!!") I'd put a label control lblExpiring on the form. The code would be
>something like:
>
>Private Sub Form_Current(Cancel as Integer)
>If Me.NewRecord = False Then
>   If Not IsNull(DLookUp("MemberID", "DrivingLicenses", _
>        "[MemberID] = " & Me.MemberID & _
>        " AND EndDate < DateAdd('m', 6, Date())") Then
>         Me!lblExipring.Caption = "LICENSE EXPIRING SOON"
>         Me!lblExpiriing.ForeColor = vbRed
>   Else
>         Me!lblExpiring.Caption = "License OK"
>         Me!lblExpiring.ForeColor = vbBlack
>   End If
>End If
>End Sub
>

-- 
Message posted via http://www.accessmonster.com

1
JOSELUIS
3/19/2010 12:08:50 PM
On Fri, 19 Mar 2010 12:08:50 GMT, "JOSELUIS via AccessMonster.com"
<u58123@uwe> wrote:

>I�m really sorry but maybe I don�t explain myself correctly and therefore an
>error message is displayed in the code and in english is more or less that
>Msaccessjet can not  find the tbl or entryquery "drivingID".
>FrmMembers is based on qryMembers which is connected with tblMembers(MemberID,
>FirstName...) and fsubdrivinglicense are based in tblDrivingMembers (MemberID,
>DrivingID,StartDate,EndDate) both are connected by MemberID so I wriote the
>code changing only "DrivingLicenses" by "DrivingID" but It could be that i
>have to explain a little more in the expression DLookUp( "MemberID",
>"DrivingID"...

The second argument of DLookUp should not be DrivingID, but should instead be
the name of a table. 

You chose not to post your actual code so obviously I can't tell you what you
did wrong. If you would copy and paste the code to a message here someone
should be able to help correct it.
-- 

             John W. Vinson [MVP]
0
John
3/19/2010 3:59:37 PM
This is the code:
Private Sub Form_Current(Cancel as Integer)
If Me.NewRecord = False Then
  If Not IsNull(DLookUp("MemberID", "DrivingLicenses", _
       "[MemberID] = " & Me.MemberID & _
       " AND EndDate < DateAdd('m', 6, Date())") Then
        Me!lblExipring.Caption = "LICENSE EXPIRING SOON"
        Me!lblExpiriing.ForeColor = vbRed
  Else
        Me!lblExpiring.Caption = "License OK"
        Me!lblExpiring.ForeColor = vbBlack
  End If
End If
End Sub
I made a mistake becuse i thought that "DrivingLicenses"  was refered to a
field of the subform not a table . My table was named tblDrivingMembers so I
corrected the name of the table and now it works perfectly.Thank you very
much for your time again.


John W. Vinson wrote:
>>I´m really sorry but maybe I don´t explain myself correctly and therefore an
>>error message is displayed in the code and in english is more or less that
>[quoted text clipped - 5 lines]
>>have to explain a little more in the expression DLookUp( "MemberID",
>>"DrivingID"...
>
>The second argument of DLookUp should not be DrivingID, but should instead be
>the name of a table. 
>
>You chose not to post your actual code so obviously I can't tell you what you
>did wrong. If you would copy and paste the code to a message here someone
>should be able to help correct it.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

0
JOSELUIS
3/19/2010 6:46:36 PM
Reply:

Similar Artilces:

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Messages Cut off
A recipient receives forwarded mail messages that are cut off in random locations throughout the email. Outlook 2000 with latest SP. Any ideas? ...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Interface not registered message
when i try to send a e-mail message through outlook, i get the message interface not registered ? and then can't even save message. can anyone tell me why ? ...

Outlook Hangs Reading Messages
I am running Windows XP Pro & Outlook 2003 via POP3 & Symantec AV I had to reinstall everything on my pc and now find when I double click an email outlook hangs for roughly a minute and then it finally opens, I have tried a data compact, reinstalling outlook and creating a new pst then importing but it still happens Does anyone have any ideas? It's driving me mad Thanks Daz Try deleting and recreating the profile as one of your steps in troubleshooting: http://www.howto-outlook.com/Faq/newprofile.htm -- K. Orland Thoughts lead on to purposes; purposes go forth in action; ...

Display of UML State Transition Event
Dear Group, I am attempting to use the UML Statechart with a couple of states and a transition between them. I select properties, Events, Change Event type, say, and can see ChangeEvent1 in the property window but... ....when I Ok back to the drawing the ChangeEvent1 is not displayed by the transition. I can enter actions in a similar way and they are displayed. How cna I get the event visible too in the drawing? Surely this is the most important aspect of a transition i.e. what caused it, regards, Colin Smith ...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Outlook rule to move read message from Blackberry
I have Outlook configured with a Blackberry server and want to set up a rule to mange messages marked as read: For example. 1. I'm away from the office, Outlook is closed. 2. I read an email on my Blackberry, which marks the message "read" 3. When I return to the office and open Outlook I want to: a. Apply a rule to my inbox that states: If message is marked as read, move to folder "read messages" b. All unread messages will remain in the inbox. I'm a heavy user of rules but I can't find a way to identify "read" messages. Any thoughts? view360@gm...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Duplicate Messages
I have just upgraded from Office 2000 to Office XP. Ever since, i have been receiving duplicate email messages. Every time i do a send/receive, about 80% of my emails arrive twice (exactly the same ID, time, date etc.) I have got some rules running but these are necessary. Any ideas anyone. Thanks ...

Forwarded messages are not being sent
One of our users is trying to setup his account to forward all messages to his yahoo account. We setup a rule that says keep a copy in his inbox and forward a copy to his yahoo account. The yahoo copy never gets out and message tracking says it is submitted to the categorizer but never gets any farther. What do we need to do? By default rule-based forwards to external recipients are disabled. To change this, go to Exchange System Manager, Global Settings, Internet Message Formats.. There, you can either enable this for all domains (*), or be specific and create a separate policy f...

Ctrl+D not working on outlook messages
Any idea why? alltimefav wrote: > Any idea why? And what are you expecting to happen? "alltimefav" wrote in message news:a95c52df-a387-4a8f-bf9d-269ff485451f@i29g2000prf.googlegroups.com... > Any idea why? So instead of hitting Ctrl+D to delete an item, what happens when the item is selected and you hit the Del key, or the "X" toolbar button, or right-click on the item and select Delete? Did you really expect a detailed response for such a vague question? You didn't even bother to say what "not working" means, like the item does not get delete...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

SUTA state message on Pay Code Integration
I'm having trouble importing pay codes that are based on another code, such as Sick and Vacation. These records get rejected with the error message "pay code 'V' requires a SUTA state". I have a SUTA state in the Emp Maint window and the HOURLY pay code already exists and contains a SUTA state. I tried mapping the SUTA State code in my file and even tried using a Constant value in Destination mapping but these codes will not import. I can go into the Pay Code Maintenance window and add these codes manually with no problem. What am I missing? ...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

CRM Email Displays Size=2>
One of my users is experiencing an issue when they save an email that is tracked within CRM the email displays in CRM with "Size=2>" directly in front of certain lines of the email. So for example "Size=2>" will appear in front of someone's comments in the email or in front of their name. Does anyone know why "Size=2>" is displaying in front of the lines of an email? Thanks. Mike H. "Mike H." wrote: > One of my users is experiencing an issue when they save an email that is > tracked within CRM the email displays in CRM wit...

Problem displaying expected results with CString
I am writing a MFC program to import data from a single table database into a normalized database with numerous tables. The first component opens a recordset object to the database and performs some basic tests on the field value, and the plan is to write the records out to a spreadsheet that fail to meet any of the criteria defined for the field. Along with writing out the record, I want to populate a comments field describing what failed. I have tried to implement this with a CString variable; I initialize it to a blank string each time a new record is examined, and then as each field is che...

Entourage (allegedly) cannot see message body sent by OWA
We have a external company receiving emails from us and claim they cannot see the message body when the email is sent from OWA. We are on 2003 SP2 btw. The external party claims that the following patch for OWA fixes the problem http://www.microsoft.com/downloads/details.aspx?familyid=5BC06E8A-08EB-4976-BC68-A03EBE3A2552&displaylang=en I am sceptical! Any thoughts Ignore!! I just saw another thread wit hthe details for this one. Thread is "OWA message text disappears" "Peter T" wrote: > We have a external company receiving emails from us and claim they cannot...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...

Outlook 2003
Hi Is it possible to order the deleted items by date of deletion in outlook 2003? Many thanks Lee Lee Atkinson <leeatkinsonlincs@hotmail.com> wrote: > Is it possible to order the deleted items by date of deletion in > outlook 2003? Use the Field Chooser to add the Modified time to the Deleted Items folder header bar (the one that says "From", "Subject", "Received", etc. and sort on that time. Deleting an item is a modification, so I would expect the modified date to be affected, giving you the most accurate value of the item's deleted time....