[AccessD] [SPAM SUSPECT] Check box field on continuous form

David,

Why not run an update query when opening the form (or even once when the
program starts) that updates this field based upon the current date.  That
way, you can just display it as is.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David & Joanne
Gould
Sent: Wednesday, July 19, 2006 12:57 AM
To: Access Developers discussion and problem solving
Subject: [SPAM SUSPECT] [AccessD] Check box field on continuous form
Importance: Low


I am working on a database for a video library. I am having some difficulty 
with a form for recording over-the-phone movie renewals. i have included a 
field in the table (tblHires) to show if the movie is to be renewed (this 
is a Yes/No field type) and the form has a subform set as a continuous form.

The subform is based on the following query:

SELECT tblHires.HireID, tblHires.member, tblHires.title, tblHires.outDate, 
tblHires.dueDate, tblHires.over, tblHires.RentalFee, tblTitles.name, 
IIf((Date()-tblHires!dueDate)>1,(Date()-tblHires!dueDate)*tblRentalType!Late
FeeRate,0) 
AS ChargesDue, tblHires.DateReturned, 
IIf(tblHires!dueDate>Date(),False,True) AS RenewStatus, tblHires.Renew, 
tblRentalType.DaysDueBack, tblRentalType.NewRelease, 
tblRentalType.RentalFee AS DefaultRentalFee
FROM (tblRentalType INNER JOIN tblTitles ON tblRentalType.RentalTypeID = 
tblTitles.RentalTypeID) INNER JOIN tblHires ON tblTitles.tapeNumber = 
tblHires.title
WHERE 
(((tblHires.member)=[Forms]![frmHire_New_Members_Extend]![cboSelectMember]) 
AND ((tblHires.DateReturned) Is Null));

I want the Renew field to be true for all titles that may need to be 
renewed (that is: not yet returned and dueDate<date()) while still showing 
all movies on hire.

I have managed to show the field as described above but it is then not 
updatable. Alternatively, I can show the field as it is in the table and 
then update it.

Is it possible to have it work both ways? The status of this field is then 
to be used to record the new hire transactions.

TIA

David Gould