Re: Conditional Formatting

The answer will depend on how your tables are set up.

You should have 2 tables here:
a) Staff table (one record for each person), with StaffID primary key;

b) Calls table with fields:
- CallID primary key
- date call received Date/Time
- StaffID Number who the work is assigned to
- sched complete Date/Time
You will use a combo for the StaffID field, where the combo's RowSource is the Staff table.

If you've done that, you could add a field to the Staff table to indicate which staff have the requirement to add 14 days. That's currently John, James, and Bob, but of course these could change in the future. So, the Staff table will have fields like this:
- StaffID AutoNumber primary key
- Surname Text
- FirstName Text
- Days2Complete Number

Now create this query, and save as (say) qryStaff4Combo:
SELECT StaffID,
Surname & ", " + FirstName AS FullName,
Days2Complete
FROM Staff
ORDER BY Surname, FirstName;

Now, you can set these properties for the StaffID combo on the Calls form:
Control Source StaffID
Row Source qryStaff4Combo
Column Count 3
Column Widths 0";2";0"
After Update [Event Procedure]

Click the Build button (...) beside the AfterUpdate property.
Access opens the code window.
Set up the code like this:

Private Sub StaffID_AfterUpdate
With Me.StaffID
If Not (IsNull(.Value) OR IsNull(Me.[date call received])) Then
If IsNumeric(.Column(2) Then
Me.[sched complete] = DateAdd("d", .Column(2), Me.[date call received])
End If
End If
End With
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CarlaInJax" <CarlaInJax@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CCCFC5E7-82A2-44B3-AE80-FD6C1E2C509F@xxxxxxxxxxxxxxxx
I have the following fields in a form:
[date call received]
[work assigned to] (this is a drop down combo box)
[scheduled completion date]
If [date call received] is today, and [work assigned to] is John, James, Bob
or Chuck, I want the [scheduled completion date] field to auto populate with
a date that is 14 days from [date call received], but if [work assigned to]
is anyone other than John, James, Bob or Chuck, I do not want the [scheduled
completion date] field to be populated.
Can anyone tell me how I would write that into my form (not using VBA)?
Thanks.

.

Loading