Highlight a row in a cont form 4

[kennetha]

hi everyone...it's me again with problems
I would like to highlight a row in a cont. form. This would help to select an item from the form. Once the row is selected a command buttom will open a new form with rel. details
thx in advance
Kenneth

 

[BillPower]

Place the code below in the On Current event of your form (Works for Access 2000+):

Dim ctl As Control, strForm As String, varCondition As Variant
On Error Resume Next
varCondition = Me!EmployeeID
For Each ctl In Me.Controls
With ctl
If .Tag = "ConditionalFormat" Then
With Me.Controls(ctl.Name).FormatConditions _
.Delete 'Deletes any other Conditional Formatting, limit is 3
End With

With Me.Controls(ctl.Name).FormatConditions _
.Add(acExpression, , "EmployeeID=" & varCondition)
.BackColor = 14024661 'Light Green
.ForeColor = 0 'Black
End With
End If
End With
Next ctl

You will need to change EmployeeID to your own ID field name.

In each control's Tag property that you want to highlight, set the Tag as "ConditionalFormat".

I have a working example of this at:

http://www.wpower.fsbusiness.co.uk/

The file to look at is: Change Background Colour of the Selected Record. Included is an Access 97 version which is too difficult to explain, but hopefully the example will be self-explanatory.

Let me know if you have any problems with either version.

Bill

 

[kennetha]

billpower,
thx for the code
it does'n seem to work; all is happening that the edit boxes flash on selection. Am i'm doing something wrong. I'm using access 2000
thx
kenenth

 

[BillPower]

Have you made the changes I highlighted.

Is your ID unique.

varCondition = Me!EmployeeID 'this must be the name of the ID control as it appears in the control on your form.

.Add(acExpression, , "EmployeeID=" & varCondition) 'this must be the name of the Control Source of the ID control as it appears in the control on your form.

If this doesn't work, you are welcome to send me a copy of your DB, removing any sensitive records first. billpower@cwcom.net

I will post any suggestions ASAP later in this thread.

Bill

 

[kennetha]

hi billpower
did you receive my db...any comments
thx
kenneth

 

[TPetersonFlorida]

Kennetha: do you want the row to be highlighted when the user clicks on the record? if so there is a much easier way to do this:

1) place a hidden text box on the form in the detail section and give it a name such as "SelText"
2) in the OnCurrent event place the following code:
me.seltext = me.uniqueID(replace this with your uniqueID for that table)
3) now select all controls in the detail section.
4) now go up to the menu at the top and click on "Format" then click on conditional formatting.
5) under condition 1 put these values:
Expression Is
me![SelText]=me![UniqueID]
6) then below to the right where you can change colors choose the icon that allows you to change the background color, just place your mouse over them and a tip will pop up telling you what they do. choose Fill/Back color to whatever you want, say Yellow. When you do that, the Preview to the right will change.
6) save the form and then open it up and select a record. that row should be highlighted in yellow.

 

[BillPower]

Hi TPetersonFlorida,

The method you've suggested in the short term is definitely simple and absolutely spot on.

The VBA method demonstrated above is quite simple if someone can edit a field and control name. I think kennetha may have forgotten to Tag his controls "ConditionalFormat", I included this method as I usually write accountany apps where my Budget, Forecast, Actual and Total columns are different colours. The Tag property is useful for managing this, having a different Tag value for each group of columns or column.

I also suggest this method because the colours available using the standard method are limited to 40, where as with VBA there are literally millions.

The standard method also limits the user to just 3 conditions, using VBA the amount of conditions are only limited by available disc space, hence the Delete method demonstrated above. However many conditions, 1 or say a 1000 the previous condition is always deleted making way for the new condition.

Anyway, just thought I'd try to explain the benefits of going down the VBA road.

Keep up the good work.

Bill

 

[cehepker]

Hello,

I am not sure what I am missing. I tried both methods listed above, and both of them did the same thing that Kenneth mentioned - the screen flashes, but the formatting does not change.

Any ideas?

Carla

Documentation: A Shaft of light into a Coded world

 

[cehepker]

Hello,

This is Carla again...Never mind the above question. I got it to work.

Carla

Documentation: A Shaft of light into a Coded world

 

[ptpaton]

Bill,

As per usual, you da' man. Have a star. I've tried four different methods of getting this to work, and yours is by far the simplest and most fool proof.

One mistake I initially made...make sure that "ConditionalFormat" is not in quotes when you type it into the tag.

I've merged both yours and other methods which suggest placing a text box under your visible controls by only placing the ConditionalFormat tag in a hidden control that spans the entire detail section. Also, I've set the back color of all of the visible controls equal to the backcolor set in the Form_current code. This combination of the methods has gotten me exactly what I need, and has solved 6 months of attempting and abandoning this cause.

Great work, and thanks everyone!

-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[nicktred]

HI

i know this tread is from a while ago but was wanderign if is is possible to do the same (Highlight a row)not based on the unique id. In a form i am displaying it doesnt have a unique id of such. It has a cust_id but this occurs many times for each customer and i am displaying all the records for that customer and want to be able to highlight any of them. I am only be able to get it working if each record has a unique id else it just selects all the records for that customer

Any Ideas

Cheers

Nick