Updating one date field from another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My database has 3 tables: customer info; a contact log; and a delivery log.
By using subforms, to the user they appear as one form with 3 different
sections to it.

In the section (subform) with the customers' names, addresses, etc., there
is an activity summary area that shows the date of the last contact and the
last contact type.
There is also a contact-log section (subform) with fields for recording the
contact date and contact type among other contact info.
Finally there is a delivery-log section (subform) which includes a field for
the date of the last delivery.

As things stand, we have to enter the date in each date field separately.
I'm sure there must be a way to write an "If...then" statement to
automatically update one date field from another. What I basically want it
to do is: "IF the date in the contact log is more recent than the date in the
activity summary THEN the date in the activity summary will update with the
date in the contact log." But so far, I haven't found that way - there's
always something wrong with my formulas.

I'd appreciate any help you can give me.
 
Ruth,

Here's one approach... First of all, make a query to identify the last
Contact. The SQL of such a query will look something like this...
SELECT ContactType, ContactDate
FROM Contacts
WHERE ((ContactDate In (SELECT Max([ContactDate]) FROM Contacts)) AND
(CustomerID=[Forms]![YourForm]![CustomerID]))

Then you can put two *unbound* textboxes on the form (I am not sure
whether you meant that you have fields for date of last contact and last
contact type in the Customers table, but if so I would strongly suggest
you remove these!). In the Control Source of these textboxes, you can
put the equivalent of these...
=DLookup("[ContactType]","NameOfYourQuery")
=DLookup("[ContactDate]","NameOfYourQuery")

Here's another approach (since you seem to enjoy subforms :-) ...
Make a copy of your ContactLog subform, sort it Descending by date,
change it so it is a single view rather than continuous view, put it
onto the Customers section of the form, and format it so it doesn't look
like a subform. Thus it will show the most recent contact.
 
Back
Top