Set field value only if record count equals 1

[NickyG]

I have a db that records clients and calls. Most clients only have one call
-- but sometimes a client may have multiple calls.

The tricky part is that for my Calls record, I need to record the date of
the call (CallDate), and for the Client record, I record the First Contact
Date, and for the majority of calls those two dates will be equal. Rather
than have the users enter the same date twice for most records, I would like
to automatically populate the CallDate with the FirstContactDate if the total
number of calls equals 1.

I set up an unbound control that calculates the number of calls (TotalCalls)
and now I'm trying to set the CallDate field to automatically check the value
of TotalCalls and if it equals one, set CallDate equal to the
DateOfFirstContact field.

I tried using the following:
If Me.TotalCalls = 1 Then Me.CallDate = Me![ClientFile]![DateOfFirstContact]

I've tried using the above in just about every option for both the field and
the form (BeforeUpdate, OnCurrent, OnLoad, etc.) What am I doing wrong? Any
help appreciated!

 

[Steve]

You need to redesign your tables:
TblClientID
ClientID
<Client contact fields>

TblClientCall
ClientCallID
ClientID
CallDate
CallNote

You can use a form/subform to add, edit, delete and just view Clients and to
add, edit, delete and just view calls to a selected client. Base the main
form on TblClient and the subform on TblClientCall. You can use the form
wizard to create the form/subform.

The above tables eliminates your problem. Note that there is no
FirstContactDate. You can get the first contact date for a selected client
by looking for the earliest CallDate for the client in TblClientCall.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)