Color Rows On Form

I have a form with 3 List Boxes on it. One of these list boxes has 3 columns of which I would like to either have the entire line or one of the columns seperately display different colors.

I have read through many posts so I am familiar with the fact that this can not be done with a list box directly.

I was hoping for something without having to resort to either a 3rd party control or building an atom bomb to squash an ant. I have looked at the control at http://www.lebans.com but I would prefer to stick to standard controls if possible.

My first thought was to hide one column that I do not have any events triggered out of and use a subform to display the information on top of the hidden column and hopefully that way I could use the conditional formatting thing to individually set up the colors of the lines in the one column.

Maybe it is because I have not dealt much with subforms but initially Access 2000 complained you could not have a subform on a form set to continuous so I set the main form to single view and was planning to set the subform to continuous so I could use it but I do not see anywhere I can do that and the conditional formatting is greyed out if I try selecting the subform and choosing it.

The column I want to color is text, and all I want to do is change the background color of each record in that column individually....What a hassle this has been for something that seems so simple on the surface...

Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.

>if I try selecting the subform and choosing it....
Try right-clicking on the edge of your subform and selecting "Subform In New Window"

You should be able to set properties such as continuous form view from here. You can also apply conditional formatting byselecting the specific control that you want to format and go to Format -> Conditional formatting...

Private Type BITMAPINFOHEADER '40 bytes
biSize As Long
biWidth As Long
biHeight As Long
biPlanes As Integer
biBitCount As Integer
biCompression As Long
biSizeImage As Long
biXPelsPerMeter As Long
biYPelsPerMeter As Long
biClrUsed As Long
biClrImportant As Long
End Type

' Need this to optimize code
' Very expensive any time we have to access PictureData prop
Private lngLenPictureData As Long

' Total Rows displayed in Detail Section
' Includes any partially visible rows
Private lngNumRows As Long

' Height of Detail Section in Pixels
Private lngDetailHeightPixels As Long

' Height of last Detail Row. May be a
' partially displayed Row.
Private lngLastDetailRowHeight As Long

' Height of Header if any
Private lngHeaderHeightPixels As Long

' Height of Footer if any
Private lngFooterHeightPixels As Long

' These properties may or may not exist.
' Store values for use in different functions.
Private lngTwipsHeader As Long
Private lngTwipsFooter As Long

'Purpose : This function clears the PictureData property of our Form. It resets the background picture
' of our Form to the Background Color we specified in the FCreateDib function.
'Calls :
'Called by :
'Parameters:
Public Function fClearDetail(frm As Form)
On Error GoTo Clear_Error

'Purpose : This is the main function. Here we ascertain the size of the current Detail section and the Form Header if any.
' We create an array of bytes to hold the information required to build a compatible PictureData property.
' We then copy this array into the Form's PictureData property.
'Calls :
'Called by :
'Parameters:
Public Function fCreateDib(frm As Form) As Boolean
Dim bh As BITMAPINFOHEADER

Dim sngTemp2 As Single
Dim sngTemp As Single
Dim lngTemp As Long
Dim lngRet As Long
Dim strTemp As String

' Height is more complicated.
' We need to take the overall Window Height and subtract the height of the Footer.
' We need to leave the Header in the calculation because we can only specify the position of
' the bitmap indirectly. In our case we are using TOP LEFT as the Picture Alignment Prop.

' Initialize all of the Form's Picture Properties required to specify that we have loaded a picture
' as the Form's background.

' Our Constructed packed DIB
frm.PictureData = aPic

' Top Left = 0
frm.PictureAlignment = 0

' Clip = 0
frm.PictureSizeMode = 0

' Embedded = 0
frm.PictureType = 0

' No Tiling
frm.PictureTiling = False

' Return total length of PictureData prop
' Trying to optimize code so we don't have to continually calculate this value
lngLenPictureData = lngSizeImage + LENDIBHEADER

fCreateDib = True

' We want to make sure our function is called the first time the Form is displayed. AbsolutePosition must not be equal to zero.
' Need error checking if no records.
frm.RecordsetClone.MoveFirst
frm.RecordsetClone.AbsolutePosition = frm.RecordsetClone.RecordCount

' For this example we are always using a 1 bit DIB.
' As we are simply drawing a rectangle we do
' not need to use the API Graphics Library.
' We'll do it the old fashioned way by
' setting the bytes/bits/pixles directly.

' This current version is not using the X parameter.
' It is not required since we are always
' drawing a rectangle from the start of the left
' edge of the Detail Section. I have included the beginnings
' of the logic required to allow for variable X parameters
' so that we can highlight individual controls.
' We would need starting and ending X coordinates.
' Additionally, the routines that currently draw a simple
' rectangle would have to be enhanced to recognize
' variable starting and ending points.
' It won't take much more coding to do these things! :-)

Dim bh As BITMAPINFOHEADER

' Bunch of temporary variables
Dim lngX As Long
Dim lngY As Long
Dim lngTempX As Long
Dim lngTempY As Long
Dim lngRet As Long
Dim lngPixel As Long

' Fill in our temp BITMAPINFOHEADER struct so we can get at the dimensions and size of our bitmap
apiCopyMemory bh, lngBuff(0), 40

' Calling program sends array of literal row numbers from 1 to lngNumRows that we are to print.
' Here is the original logic to calculate the Y offsets. Simply add the Form Header(if any)
' plus the DetailHeight times the row number

' Reverse it by subtracting from end of variant
lngPixel = lngLenPictureData - lngPixel

' We are always subtracting since DIB is upside down.
' If Total bytes that we are going to process is greater than the starting position then there is an error
If lngPixel < (LENDIBHEADER + (lngDetailWidthPadded * lngDetailHeightPixels)) Then
lngPixel = (LENDIBHEADER + (lngDetailWidthPadded * lngDetailHeightPixels))

End If
' We are always subtracting since DIB is upside down.
' If starting position is greater than the total length then we have an error
If lngPixel > lngLenPictureData - 1 Then
lngPixel = lngLenPictureData - 1
End If

'Purpose :
'Calls :
'Called by :
'Parameters:
Public Function fCallGetScrollInfo(frm As Form) As Boolean
' This is the function called directly by the Form's Timer event.
' We make our 1 quick function call to fScrollTheForm and exit if:
' The Top Row of the Form and the CurrentRecord have not changed since the last Timer event.

' Junk temp variables
Dim strTemp As String, strTemp1 As String
Dim X As Long, Y As Long
Dim lngTemp As Long

' added by Bill Murphy
Dim strTest As String ' test added 2/22/01
Dim lngSaveAbsolutePosition As Long ' added 2/24/01 to restore position to top row

' The Row that is currently displayed as the top row of our Form.
Dim lngTopRow As Long

' Holds which Rows we want to print
Dim arrWhichRows() As Long

' Make our API calls to get the current position of the Form's ScrollBar which yields the Top Row currently displayed in the Form.
lngTopRow = fScrollTheForm(frm)

' Check and see if Top Row has changed since the last call!
If lngTopRow = frm.RecordsetClone.AbsolutePosition Then
' No Change. Let's exit function so we are not tying up system resources when the user is not moving around in the Form.
Exit Function
End If

' temporarily disable scrolling if number of records less than number of rows on form - added by Bill Murphy
If .RecordsetClone.RecordCount < lngNumRows Then
.ScrollBars = 0 ' neither
Else
.ScrollBars = 3 ' both
End If

' restore absolute position in recordset to top row of form
.RecordsetClone.AbsolutePosition = lngSaveAbsolutePosition

' Verify there is at least one entry in the Array
On Error Resume Next
X = -1
X = (UBound(arrWhichRows))
If X >= 0 Then
Call DrawBackGround(frm, 0, arrWhichRows())
End If

' Turn ON Form Painting
.Painting = True

' Update our Display on Form
.txtPosition = strTemp1
End With

' Return TRUE for Success!
fCallGetScrollInfo = True

' Uhh. where's the error handler?
End Function

'Purpose : This function is basically the same as the fCallGetScrollInfo function above.
' We are using this function to check and see what the CurrentRecord is and if the
' Form has been scrolled so we need to Repaint the CurrentRecord as it scrolls Up/Down.
' This is the function called directly by the Form's Timer event.
' We make our 1 quick function call to fScrollTheForm and exit if:
' The Top Row of the Form and the CurrentRecord have not changed since the last Timer event.
'Calls :
'Called by :
'Parameters:
Public Function fScrollInfoCurrentRecord(frm As Form, blReset As Boolean) As Boolean

' Junk temporary variables
Dim strTemp As String, strTemp1 As String
Dim X As Long, Y As Long
Dim lngTemp As Long

' Top Row currently displayed in the Form.
Dim lngTopRow As Long

' Holds which single Current Row we want to print
Dim arrWhichRows(0) As Long

' We use this variables to test and see if we need to update our display or quickly exit this Timer event!
Static lngPrevTopRow As Long
Static lngPrevCurrentRecord As Long

' blReset was needed to FLAG that the user has selected HighLight CurrentRecord from the multiple choice "Format Type" Frame control.
If blReset Then
' We are switching from Format.Criteria or Format.Alternate
' Initialize Static vars
lngPrevTopRow = -1
lngPrevCurrentRecord = -1
End If

' We only want to clear bitmap once after we scroll our current row/record off the screen
Static blFlagClearBitmap As Boolean

' Make our API calls to get the current position of the Form's ScrollBar
' which yields the Top Row currently displayed in the Form.
lngTopRow = fScrollTheForm(frm)

If lngPrevTopRow = lngTopRow Then
If lngPrevCurrentRecord = frm.CurrentRecord Then
Exit Function
End If
End If

' Update our Static variables so that we can use our testing logic on
' entry to this function to see if an update is required or not
lngPrevTopRow = lngTopRow
lngPrevCurrentRecord = frm.CurrentRecord

' AbsolutePosition is ZERO based index.
lngTopRow = lngTopRow + 1

' If Current Row is not currently displayed then exit
' Is the CurrentRecord above the Record in the Top Row
If frm.CurrentRecord < lngTopRow Then
If blFlagClearBitmap = True Then
Call fClearDetail(frm)
blFlagClearBitmap = False
End If
Exit Function

End If

' If Current Row is not currently displayed then exit.
' Is the CurrentRecord Below the Record at the Bottom Row
If frm.CurrentRecord >= lngTopRow + lngNumRows Then
If blFlagClearBitmap = True Then
Call fClearDetail(frm)
blFlagClearBitmap = False
End If
Exit Function

mbizup - In order to use the conditional formatting I have read that it requires the subform to be set to continous mode. Sooo my main form is now set to single view, it was previously set to continous. This allows me to now drop the subform on to the form but there is still no way to set the subform to continous, so the conditional formatting is still greyed out. I can not get the open the subform in a new window menu buy right clicking anywhere on any edge so I am not sure what is up with that. Am I missing something here? I would think a subform is a new form dropped onto an existing form right? I am selecting subform/subreport from the toolbox and drawing a box on my main form and dropping it down. The main form has the three list boxes on it.

thenelson- Thanks for the info but I do not see how that helps me?

RonOsborne- Well I don't know where to even start with that one...I did mention atom bomb I would say that qualifies here...

Our community of experts have been thoroughly vetted for their expertise and industry experience. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.

Another approach would be to open your subform in design view from the database window (on its own, not with the main form) and setting it's default view property to "Continuous Forms". Also set up the conditional formatting at this time. Get these properties set prior to "dropping" it on the main form.

Yep, why use a hammer when you can use an atom bomb
It may be old code, but it works, just plug it in and use it, 1 module and 3 lines of code on any continuious form
It hasn't failed since 1995 in thousands of users sites and thier various configurations of Windows OS and Access (just had to toot my own whistle)
Good luck with which ever way you go

ronosborne - I certainly don't have anything against old code... I am just not sure I understand how to apply it to my case. Can I somehow use this to repaint the background color of any single line in the form HTList0 which is a sample of my form.

I created a subform with conditional formatting for you at www.thenelson.name/ListBoxForms.mdb
Not knowing the criteria you wanted for the conditional formatting, I just made it based on HighTickCount > 10

thenelson - Thanks so much for providing me with that form that was all I needed...spent the entire day redoing my trading screen to make the entire thing a subform. I think I got it all working....we will see tomorrow!