Highlight only current record row? 1

[1DMF]

I have a continuos form and seem unable to highlight just the current row?

Me.ACC_Text.BackColor = RGB(0, 0, 0)

This is affecting every row on the continuous form not the current row?

If I use conditional formatting it is applying it to all the controls as well not the current record?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

ok, just read that unbound controls on a continuous form are not unique for each record.... back to the drawing board.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

I can provide you the code to do this. Bottom line you build a function and pass the PK. If the PK is the current row it returns true. Then you use conditional formatting. I assume this is part of the listbox thing. I will provide you the demo showing how to build the listbox with checks, and I also highlight the checked rows. I also pulled up some old listview demos and they seem to still work.

 

[MajP]

I forgot there was an easier trick.

1)Put a hidden unbound textbox on the form. Call it "txtboxSelected"
2) In the forms "OnCurrent" event put
me.txtboxselected = me.yourPrimaryKeyValue
3)Now put an unbound textbox behind all of your controls and send to back. Span the length of the detail section. Set the default color. This is use for your highlighting
4)in conditional formatting for this textbox
expression is:
[textBoxSelected] = [studyID]

 

[MajP]

sorry studyID would be the yourPrimaryKeyValue

 

[1DMF]

Hey MajP,

Yes I have managed to do it with a single row, but try doing it with multiple select when an unbound field is the same for all records

If you try to apply a colour via code, it affects every row not the desired one (current record)

I'm working with the listbox currently, but will look into the listview, as their is a TT FAQ on the subject.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

Yes I have managed to do it with a single row, but try doing it with multiple select when an unbound field is the same for all records

Not sure what that means. The question was how to hiltie the current selected row. As already been explained there is no way to use an unbound control to select fields. The solution as I explained would be to add a boolean field in the table that you check and uncheck based on selections in another table.

If however there is a way to identify the records you want to hilte, such as the values are stored in another table you could hilte them. So assume PKs 1,3,5 you want to hilite are stored in a "selected table". Then you build a function that returns true if the PK is in the table. Then you can use conditional formatting using that function as an expression and pass in the field name holding the PK on your form. For each record on your form it returns true or false based on if it is in the selected table.

 

[1DMF]

Hey MajP,

There are no records in any table yet other than the lookup to power the content of the listbox.

It is the selections being made by the user that will create the records via a T-SQL SP later on.

I don't think this can be emulated via a continuous form

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

I don't think this can be emulated via a continuous form

It probably can, but again not sure what you are asking to emulate. This post was about hilighting the current row, so no longer sure what specifically trying to emulate. If you are referring to using a continuous subform to emulate a listbox with checks here is my example

So my main form is bound to the Customer table. The subform is bound to the lookup table Products. When you check or uncheck a Product it is added to my table Customer_Products. The results are immediately shown in the bottom subform. So here is the code to make this all work

Subform Products

Private Sub Selected_AfterUpdate()
  If Selected Then
    InsertSelected Me.Parent.CustomerID, Me.ProductID
  Else
    RemoveSelected Me.Parent.CustomerID, Me.ProductID
  End If
  Me.Parent.subFrmCustomersProducts.Requery
End Sub
Public Sub RemoveSelected(CustomerID As Long, ProductID As Long)
  Dim strSql As String
  strSql = "Delete * from tblCustomers_Products where Customer_ID_FK = " & CustomerID & " AND Product_ID_FK = " & ProductID
  CurrentDb.Execute strSql
End Sub
Public Sub InsertSelected(CustomerID, ProductID)
  Dim strSql As String
  strSql = "INSERT INTO tblCustomers_Products (Customer_ID_FK, Product_ID_FK) "
  strSql = strSql & " Values( " & CustomerID & " , " & ProductID & ")"
  CurrentDb.Execute strSql
End Sub

This controls adding and removing records from the join table Customner_Products

MainForm

Private Sub Form_Current()
  ClearSelections
  LoadSelections Me.CustomerID
  Me.Refresh
End Sub
Public Sub ClearSelections()
  'Clear the selections from the Products Table
  Dim strSql As String
  strSql = "UPDATE Products SET Products.Selected = False WHERE Products.Selected = True"
  CurrentDb.Execute "qryClear_Selections"
End Sub


Public Sub LoadSelections(CustomerID As Long)
  'Select the products in the product table for that customer
  Dim strSql As String
  strSql = "UPDATE Products INNER JOIN tblCustomers_Products ON Products.ProductID = tblCustomers_Products.Product_ID_FK "
  strSql = strSql & " SET Products.Selected = True WHERE tblCustomers_Products.Customer_ID_FK = " & CustomerID
  Debug.Print strSql
  CurrentDb.Execute strSql
End Sub

This code loads the simulated listbox with the selected products for a given customer. The queries could get more complicated, but the approach should be the same.

 

[MajP]

Here is a real listview. I am running AC 2013 on a Win7 64bit machine. It seems to work fine, but I may not be able to develop one anymore, have not tried. The listview is pretty powerful in that you can do a lot of row and column formatting. A lot more powerful than a listbox, but you have to control all the record operations. It is not a bound control

 

[1DMF]

Thanks MajP,

Didn't mean to confuse the issue as it was related to emulating the listbox multi-select.

I can't use your logic, unless I create a temp table to store the selection in as there is a transaction SP that updates multiple tables and must rollback if not successful.

The selection process needs to be front end GUI only no records should be added to anything at this stage.

I think listview or listbox will give what I want, I already have a working refactored transactional stored procedure working with the additions... (this is an extract for the new record insertion)

-- Insert History if required
IF @Audit = 1 AND @RAG IS NOT NULL
    BEGIN
	INSERT INTO Case_Checker_Grades (Case_ID,Grade,Officer,ACC) VALUES (@Case_ID,@RAG,@Officer,@ACC);
	SET @GRADE_ID = SCOPE_IDENTITY();
	IF @ACC = 'Yes' [COLOR=#EF2929]AND @Reasons IS NOT NULL[/color]			
	    [COLOR=#EF2929]SET @SQL = 'INSERT INTO ACC_Reason SELECT ACC_ID,' + CAST(@GRADE_ID AS varchar(50)) + ' AS Grade_ID FROM ACC_Charge_Type WHERE ACC_ID IN (' + @Reasons + ')';
	    EXEC(@SQL);[/color]
    END

And I have a collection JOIN function I wrote to turn a collection of selections into a delimited string... so I'm 90% there with the functionality.

Public Function JoinC(ByVal cColl As Collection, Optional ByVal sDilem As String = ",") As String

    Dim vItem As Variant
    JoinC = ""
    
    For Each vItem In cColl
        If JoinC <> "" Then
            JoinC = JoinC & sDilem
        End If
        JoinC = JoinC & vItem
    Next
    
End Function

So I can produce the desired @Reasons SQL 'IN' clause data from the collection when items are selected...

   If Me.ACC.ItemsSelected.Count > 0 Then
        Dim vList As Variant
        [COLOR=#EF2929]For Each vList In Me.ACC.ItemsSelected[/color]
            sBod = sBod & "<li>" & Me.ACC.Column(1, vList) & "<li>"
            [COLOR=#EF2929]cACC.Add (Me.ACC.Column(0, vList))[/color]
        Next
    End If

It also adds the reasons text to the email being sent

So the functionality of it is there it's just the GUI I'll work on to make a better UX - well that's the plan.

Really appreciate your input.


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

I can't use your logic, unless I create a temp table to store the selection in as there is a transaction SP that updates multiple tables and must rollback if not successful.

You could still do it with some simple modifications. The basic logic would stay the same but like you said you would need a local table. I would not call it a Temp table because it is a permanent table on the front end that you would update with all the choices. Then if you want to use your collection idea you just populate the collection by looping the recordset instead of looping a control.
Yes the listbox will work, but I thought the point was that the listbox does not provide a nice interface. The listview works too and has much better features, but I would caution you on portability. I would develop a simple listview and see if it runs on all target machines.