detail formatting on Forms?

[Michelle K.]

I'm working on a project that requires an Excel-like ability to change the
background color for an individual record while it is displayed. In Excel,
the user clicks anywhere in the row of the record they want to change, then
clicks a button that changes the background color. Works great -- except
that Excel can't handle the multi-user situation. I have Access working well
in this project for all except this one function. I have used the Format
event in the Reports Detail area to generate reports that show color-coded
records -- but the user can't change those directly. I'm not finding that
same ability to format the detail area on forms. I have considered sending
data out to Excel just for this function, but am concerned about record
change conflicts while the data is "out". Any suggestions?

 

[John Spencer]

I you want to highlight the entire current row in a continuous form, it
can be done. Check Stephen Lebans site for an example

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

If that one doesn't do it for you - post back and IF I see your post I
will describe how I have done this. It may be very similar to Stephen's
solution - I haven't looked at how he did this.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Michelle K.]

Thanks for your response, John. I've looked at the Lebans site and find that
information a bit complicated -- I could probably muddle through it, but
don't have the time to spend there right now so I'm looking for a simpler
solution if it exists. I stumbled on a possibility and am looking for
feedback on whether or not it should work. I've added an OLE field to the
database and loaded it with a bmp file that is a simple rectangle of the
color I want for the background for each record. Then I loaded that field
into the form, placing it behind all of the other data in the detail section
and stretching it the entire length. I set the Size Mode to Stretch and the
Background for the other controls to Transparent. It seems to work just like
I want. Now I need to figure out how to use VB to load the OLE field when
the user clicks the appropriate color command button. Attempts at this point
to "use existing" file instead of creating a new bitmap file have resulted in
"Package" in the OLE field in the table and a black block in form view
instead of the background color. Any help in that area would be greatly
appreciated, as would comments on whether or not this looks like a good plan.

Thanks again -
Michelle K.

 

[John Spencer]

If it works, it is a good plan.

My version uses an invisible control (txtCurrentRecord) in the header, a
control in the detail section that is the size of the detail section
(txtHighlight), setting all the other controls in the detail section to
transparent, and conditional formatting. It only works in continuous
form view (not in datasheet view).

Conditional formatting for txtHighlight is
Expression is [txtCurrentRecord] = fID

VBA in the Form's Current Event
Private Sub Form_Current()
Me.txtCurrentRecord = Me!fID
End Sub

Make sure you move txtHighlight behind all the other controls (Format
menu) and also set txtHighlight tabStop property to No.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Michelle K.]

I have too many possibilities for conditional formatting in Access 2003, but
used a similiar approach that seems to be working. I created a separate
table (Colors), 2 fields - color code, and an OLE bitmap (tiny file created
by Paintbrush) - one record for each color I needed. Then used a query to
match the correct bitmap to each record via the color code, and displayed the
bitmap as you displayed your txtHighlight field.

I still don't know how to use VB to fill the OLE bitmap field, but at this
point I don't need it so I can save that for another day!

Thanks again for your response, John.

If it works, it is a good plan.

My version uses an invisible control (txtCurrentRecord) in the header, a
control in the detail section that is the size of the detail section
(txtHighlight), setting all the other controls in the detail section to
transparent, and conditional formatting. It only works in continuous
form view (not in datasheet view).

Conditional formatting for txtHighlight is
Expression is [txtCurrentRecord] = fID

VBA in the Form's Current Event
Private Sub Form_Current()
Me.txtCurrentRecord = Me!fID
End Sub

Make sure you move txtHighlight behind all the other controls (Format
menu) and also set txtHighlight tabStop property to No.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for your response, John. I've looked at the Lebans site and find that
information a bit complicated -- I could probably muddle through it, but
don't have the time to spend there right now so I'm looking for a simpler
solution if it exists. I stumbled on a possibility and am looking for
feedback on whether or not it should work. I've added an OLE field to the
database and loaded it with a bmp file that is a simple rectangle of the
color I want for the background for each record. Then I loaded that field
into the form, placing it behind all of the other data in the detail section
and stretching it the entire length. I set the Size Mode to Stretch and the
Background for the other controls to Transparent. It seems to work just like
I want. Now I need to figure out how to use VB to load the OLE field when
the user clicks the appropriate color command button. Attempts at this point
to "use existing" file instead of creating a new bitmap file have resulted in
"Package" in the OLE field in the table and a black block in form view
instead of the background color. Any help in that area would be greatly
appreciated, as would comments on whether or not this looks like a good plan.

Thanks again -
Michelle K.