Conditional Formatting from hidden columns

[NeedsHelp101]

Hi,

I'm working with a combo box on a form that draws its values (EDates and Events) from a table. Currently, it stores the Event name. I would like to set it up so that if the Event has already passed ( EDate >Date() ), then the Event name turns red. Because the EDate of the event isn't the "field value," I can't use Access's built in formatting tool.
How would I do this?

Thanks in advance!

 

[Remou]

If I understand you, you can use both the Current event for the form and the After Update event of the combo. I guess you have set the combo up with two columns, edate=column 1 and events=column 2, so your code would be something like:

If Me.cboCombo.column(0) < Date() Then
'Counting starts from 0
   Me.cboCombo.Backcolor=vbRed
Else
   Me.cboCombo.Backcolor=vbWhite
End If

 

[MajP]

I can't use Access's built in formatting tool.

Actually you can.

Expression is: [cmboName].[column](1)> Date

remember columns are zero indexed, the 2nd column is (1)

 

[NeedsHelp101]

Thanks for the advice. Something's still not working though -

Private Sub EVENTBOX_GotFocus()

Dim lngBlue As Long
Dim lngBlack As Long

lngBlue = RGB(0, 0, 255)
lngBlack = RGB(0, 0, 0)

If Not IsNull(Me.EVENTBOX.Column(6)) And (Me.EVENTBOX.Column(6) < Date) = True Then
    Me.EVENTBOX.BackColor = vbRed
Else: Me.EVENTBOX.BackColor = vbWhite
End If

If Me.EVENTBOX.Column(4) = True Then
    Me.EVENTBOX.ForeColor = lngBlue
    Me.EVENTBOX.FontBold = True
Else: Me.EVENTBOX.ForeColor = lngBlack
End If

End Sub

I'm trying to get it so that when you click on the drop down list, the entries show up in colors, so you know which ones you shouldn't select, based on the color codes.

Can you see my error?

 

[NeedsHelp101]

That might be even easier, thanks MajP!
This means, though, that in the row source area of the properties box for the combobox, I need to include those other rows in the query, right? Or can column() reference directly from the original table?

 

[Remou]

You cannot change the color of the rows in a combo box. You will need a ListView for that:
How can I format my ListBox?
faq702-5329

 

[MajP]

http://www.lebans.com/vb_listbox.htm

I have not tried this code, but it does provide conditional formatting per row.

 

[MajP]

I took a look at this code, (most of his stuff is real good). I would stick with a ListView. It is good practice to work with them, they are very robust.

 

[TheAceMan1]

[blue]I'm trying to get it so that when you click on the drop down list, the entries show up in colors, so you know which ones you shouldn't select, based on the color codes.[/blue]

Just a thought . . . why not base the [blue]RowSource[/blue] of the combo on a query that includes your criteria and only shows [blue]Column(6)FieldName < Date[/blue]! User can't make mistake here . . . no chance of error presented!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[NeedsHelp101]

That wouldn't really work for my list, because I need users to be able to see expired events, in case they want to renew them.

But - do you think it's possible to include a button on the form (separate from the combo box) that you can click to change the row source for the combo box?

For example, clicking one button will show only events that expire in May, clicking another will return the list to normal. Would that work?

 

[MajP]

Many ways to do this.
I made two queries:

qryAllDates
qryAvailableDates

qryAvailableDates has the criteria in it to return only the available dates

At the bottom of the combo I used an option group with two radio buttons.

Private Sub Frame5_AfterUpdate()
  If Frame5 = 1 Then
    Me.Combo0.RowSource = "qryAllDates"
  Else
    Me.Combo0.RowSource = "qryAvailableDates"
  End If
End Sub

 

[NeedsHelp101]

Thanks MajP!
It's still frustrating that I can't get the Format->Conditional Formatting option on Access to work.

I'm choosing "Expression Is" and I need it to turn red if the Expiration date has passed.

My list box is called Event_1, its control source is called Event1, the table from which it is populated (through a query) is called tblEventNames, and the form is called frmTraffic. The ExpirationDate is the 4th column in the query (I know it's indexed from 0), but the 10th column in the table.

Any suggestions on how to write the expression?

 

[MajP]

The column refers to the column of the recordset in the listbox. This is a property of the listbox so does not matter the tables field order.

in the listbox's con formatting

expression is:
[Event_1].[column](3) <= date()

click on Font red