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/For...ccess/201003/1

"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.

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]

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

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]

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/For...ccess/201003/1