subform control change

G

Guest

Hi i'm trying to change one of the controls (Follow_Up_Date) on my subform
when the value is less than today's date. I would like to change the field to
a different colour. The subform is filtered and sorted by another group
control. When i hit option 4, the colour does not change in Form View and
that is what i'm after. When i look at the subform in Design View, is see the
contol has changed to yellow. Please help. Code below:

Private Sub Filter_Click()
Dim FrmS As Access.Form
Dim Magenta As Long
Dim Yellow As Long
Yellow = RGB(255, 255, 0)
Magenta = RGB(255, 0, 255)

Set SF = Me.[subfrmProspectStatus].Form ' Reference the Subform through
it's *control*

Select Case Me.[ProFilterOpt].Value
Case 1 ' Show all Records
DoCmd.ShowAllRecords
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True
Case 2 ' FollowUp not checked and No Interview booked
SF.Filter = "[FollowUp_Req] = No and [Interview_Date] is Null"
SF.FilterOn = True
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True
Case 3 'Filter on Interview Date
SF.Filter = "[Interview_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Interview_Date] ASC"
SF.OrderByOn = True
Case 4 'Filter on Call Back Date
SF.Filter = "[Follow_Up_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Follow_Up_Date] ASC"
SF.OrderByOn = True

If SF.[Follow_Up_Date] < Date Then
SF.[Follow_Up_Date].BackStyle = 1
SF.[Follow_Up_Date].BackColor = Yellow
Else
End If

Case 5 'Sort Days Since Initial Call
DoCmd.ShowAllRecords
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True

End Select
End Sub
 
G

Guest

Sorry, corection - i was mistaken re:design view, the control DOES NOT change
in Form View or Design View.
 
G

Guest

I found Conditional Formating on Tools Menu, works great - no VBA needed.

Chuck said:
Sorry, corection - i was mistaken re:design view, the control DOES NOT change
in Form View or Design View.

Chuck said:
Hi i'm trying to change one of the controls (Follow_Up_Date) on my subform
when the value is less than today's date. I would like to change the field to
a different colour. The subform is filtered and sorted by another group
control. When i hit option 4, the colour does not change in Form View and
that is what i'm after. When i look at the subform in Design View, is see the
contol has changed to yellow. Please help. Code below:

Private Sub Filter_Click()
Dim FrmS As Access.Form
Dim Magenta As Long
Dim Yellow As Long
Yellow = RGB(255, 255, 0)
Magenta = RGB(255, 0, 255)

Set SF = Me.[subfrmProspectStatus].Form ' Reference the Subform through
it's *control*

Select Case Me.[ProFilterOpt].Value
Case 1 ' Show all Records
DoCmd.ShowAllRecords
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True
Case 2 ' FollowUp not checked and No Interview booked
SF.Filter = "[FollowUp_Req] = No and [Interview_Date] is Null"
SF.FilterOn = True
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True
Case 3 'Filter on Interview Date
SF.Filter = "[Interview_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Interview_Date] ASC"
SF.OrderByOn = True
Case 4 'Filter on Call Back Date
SF.Filter = "[Follow_Up_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Follow_Up_Date] ASC"
SF.OrderByOn = True

If SF.[Follow_Up_Date] < Date Then
SF.[Follow_Up_Date].BackStyle = 1
SF.[Follow_Up_Date].BackColor = Yellow
Else
End If

Case 5 'Sort Days Since Initial Call
DoCmd.ShowAllRecords
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True

End Select
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting date in subform 15
How to refer to a control in Sub 1
How to refer to a control in a Sub 1
Trying to change ForeColor accordingly 5
alter properties of subForm control (KBase Article 209099] 8
Control on Subform works sometimes 3

Top