AutoFill null date

[Tara]

I have a form used for entering client case data (frmCaseInfo). This form
has the field StartDate on it. There are times this field is left blank due
to the fact that the initial record is sometimes added weeks or even months
before a start date is decided upon. Users often forget to go back and fill
this in later. In cases such as this, when the field in inadvertently left
blank, I would like the StartDate field to automatically fill in with the
date of the first contact with that client. All contacts are held in another
table, tblContacts.

Any ideas on how to go about accomplishing that?

Thanks!

 

[John Spencer]

Use an update query that looks roughly like the following.
Assumptions: There is a field (caseID) that can be used to relate
tableCaseInfo to tblContacts.

UPDATE TABLECaseInfo
SET TableCaseInfo.StartDate =DMin("ContactDate","tblContacts","CaseID = " &
CaseID)
WHERE TableCaseInfo.StartDate is Null

If CaseID is not a number field, but is a text field then you would need to
modify the DMIN
UPDATE TABLECaseInfo
SET TableCaseInfo.StartDate =DMin("ContactDate","tblContacts","CaseID =""" &
CaseID & """")
WHERE TableCaseInfo.StartDate is Null

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..