Conditional Formatting

[CarlaInJax]

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.

 

[Allen Browne]

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

 

[CarlaInJax]

Allen,

I do not have two tables here, I'm just using one and [work assigned to] is
a drop down box. Is there any way to do it using it in the format I've got?
I thought perhaps an Iif statement might work, but I cannot figure out the
syntax.

Thanks.

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Allen Browne]

Honestly, it's not worth the effort to do it the wrong way.

There are so many benefits to using a correctly normalized design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I do not have two tables here, I'm just using one and [work assigned to]
is
a drop down box. Is there any way to do it using it in the format I've
got?
I thought perhaps an Iif statement might work, but I cannot figure out the
syntax.

Thanks.

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

Reply to group, rather than allenbrowne at mvps dot org.

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.