Prev: Save a record when switching tabs or clicking button
Next: Set Value Macro
From: JOSELUIS via AccessMonster.com on 17 Mar 2010 19:03
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

From: Paul Shapiro on 17 Mar 2010 19:24
"JOSELUIS via AccessMonster.com" <u58123(a)uwe> wrote in message
news:a52a03c5a85cf(a)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.

From: John W. Vinson on 17 Mar 2010 19:37
On Wed, 17 Mar 2010 23:03:19 GMT, "JOSELUIS via AccessMonster.com"
<u58123(a)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]
From: JOSELUIS via AccessMonster.com on 19 Mar 2010 08:08
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

From: John W. Vinson on 19 Mar 2010 11:59
On Fri, 19 Mar 2010 12:08:50 GMT, "JOSELUIS via AccessMonster.com"
<u58123(a)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]
 |  Next  |  Last
Pages: 1 2
Prev: Save a record when switching tabs or clicking button
Next: Set Value Macro