Highlighting Datasheet Row (1 Viewer)

[cosmarchy]

I'm trying to create a dynamic way of highlighting an entire row when one of the textboxes is clicked.

The form consists of three textboxes PN, DE and MC and is configured for datasheet view.

The idea is that every control on the form is looped and checked to see if it is a textbox. If it is a textbox then a formatting condition is added to highlight the contents of the control based on the value of the textbox in the clicked row.

Here is the code so far and is triggered from the click event of any of the textboxes:

Private Function click()

    Dim ctrl As Control
    Dim txtbox As TextBox
    
    For Each ctrl In Me.Controls
        If (StrComp(TypeName(ctrl), "textBox", vbTextCompare) = 0) Then
            Set txtbox = ctrl
            txtbox.FormatConditions.Delete
            Call txtbox.FormatConditions.Add(acFieldValue, acEqual, Form(ctrl.Name).Value)
            With txtbox.FormatConditions(0)
                .BackColor = vbGreen
            End With
        End If
    Next

End Function

This is missing a lot of error capturing but I only wanted to get started!!

The trouble is that this only half works in that every time a row is clicked somewhere, only the PN row highlights.

Can anyone see why the other two cells are not highlighting. They all share the same code but for some reason once the first cell is highlighted, the others in the same row don't highlight.

Thanks

 

[MajP]

It seems to only work on numeric fields. I will see if there is a work around.

 

[MajP]

This works for me. Handles, Text, Dates, Booleans,

Private Function click()
    Dim ctrl As Control
    Dim tb As Access.TextBox
    Dim val As Variant
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acTextBox Then
            Set tb = ctrl
            If Not IsNull(tb) Then
                Select Case VarType(tb.Value)
                Case vbString
                  val = "'" & CStr(tb.Value) & "'"
                Case vbDate
                  val = "#" & tb.Value & "#"
                Case Else
                  val = tb.Value
                End Select
                With tb
                 .FormatConditions.Delete
                 .FormatConditions.Add acFieldValue, acEqual, val
                 .FormatConditions(0).BackColor = vbGreen
                End With
            End If
       End If
    Next ctrl
End Function

 

[CJ_London]

Also take a look at this thread

multi select form

Responding to a thread where I suggest using a multiselect listbox, I thought it would be useful to upload this simple example where you can turn a form into a multi select form. It only does a 'simple' version but with more work could probably also be extended to mimic the extended form. The...

www.access-programmers.co.uk

 

[MajP]

I guess I need to ask what is the goal. If the goal is to highlight the current row then this can be done far easier. If it is to multiselect then this code does not do that and see @CJ_London . This however shows similar values in other records compared to the selected record. Could be useful. There is a glitch I need to look at. Notice 15.11 in the numeric field, this is incorrect.

 

[CJ_London]

just a note to say you can modify my multi select code to just replace the ID value in the hidden text box rather than add to it

 

[MajP]

If you simply want to highlight a single row, see demo.

Solved - Resizeable column width in listbox in access

Looking for a way to have resizeable column width in listbox or continuous form or continuous subform. It will be exactly like datasheet columns which are expandable, though I can use that datasheet but I dont want to use filter, the way datasheet works. I will use clickable header to sort...

www.access-programmers.co.uk

 

[arnelgp]

I put a "dummy" textbox on the datasheet form.
then on the Open event of the form, i make the column hidden.
on the Current event i set its value same as the value of PN field.
i added conditional formatting on the first three textbox.

 

[cosmarchy]

@MajP ultimately I'm trying to find an easy way of highlighting whole rows in a datasheet view which is dynamic in that if you add further columns you don't have to re-write the code to take them in to account.
The main reason for this, is that a datasheet view provides better flexibility in being able to adjust the column widths which is what I was trying to achieve with a listbox initially but gave up...

Good call on the numeric front - I hadn't noticed that it only effected numeric numbering, I'd just assumed it was the columns not applying the conditional formatting in the first place!!

I do like @arnelgp solution here as this is exactly what I want to achieve in the first place. The only downside I see is that I cannot get horizontal scroll bars to show.
Not sure whether this is due to the way it's implemented???

 

[MajP]

@MajP ultimately I'm trying to find an easy way of highlighting whole rows in a datasheet view which is dynamic in that if you add further columns you don't have to re-write the code to take them in to account

Highlighting a single row is a pretty well known solution as demonstrated by all three of us @CJ_London, @arnelgp and myself. Use a hidden textbox and update the primary key in the current event. Then use conditional formatting checking the primary key of the current row to the hidden textbox. All three examples are all basically the same approach. If you search on this forum I am sure you would find others.

However, I actually found what I mistakenly thought you were doing as interesting. This would allow you to select a row and see other records with similar values. A fast way to do comparisons.