Solved Specefic record set action in continuous form based on condition in vba (1 Viewer)

Ihk

Member
Local time
Today, 09:11
Joined
Apr 7, 2020
Messages
280
I want when I click on specefic field I want the action that sepecfic record set only but not on the whole column.
Let me say briefly, but dont ask why all this.
I have set of colours in a table (ColorsSetT)
ID from this table will be saved as foreign ID in different tables like categories, for report headings, for forms, application setting table etc.
User will be able to change this (foriegn id) and can save it with user sepecefic ID, so it might be different for different user. (But this is later stage).
At the moment question is very simple. With the following code, on field click it changes the whole column background colour, rather than it should change where I clicked (specefic field).
Code:
Private Sub txtColorCodeDec_Click()
Dim ColorSet As Variant
        ColorSet = DLookup("ColorCodeDec", "ColorSetQRY", "ID_ColorsSet =" & Me.txtID_ColorsSet.Value)
        Me.txtColorCodeDec.BackColor = ColorSet
    
End Sub
Demo form looks like this, forget content of form. I am concerned with code at the moment. thank.
Three screen shots of form, (No click, clicked on 65280, and 0)
In image 1 was not clicked, Image 2 was clicked on highlighted (65280 color code) , Image 3 was clicked on 1st record (color code 0)
1659446053848.png
1659446367459.png
1659446475472.png

I can see colors codes are working and being picked up, but only problem is why is it applied to whole coloumn? why not to that clicked fieled only?
I also want to apply this on "form on load" event, based on ID of the colour.
Any suggestion, how can I change this for a continuous form. On a single form this is not a problem.
I also have tried sql record set but that also did not work either.
Thank you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:11
Joined
May 7, 2009
Messages
19,169
it's continuous so there is only one ColorCode textbox on the form.
you might consider using the Paint event of the detail section to change the color
on each row.
 
  • Like
Reactions: Ihk

Ihk

Member
Local time
Today, 09:11
Joined
Apr 7, 2020
Messages
280
it's continuous so there is only one ColorCode textbox on the form.
you might consider using the Paint event of the detail section to change the color
on each row.
Okay I understood. I will try that. Thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:11
Joined
Feb 19, 2002
Messages
42,981
You need to use conditional format in order to achieve what you want. The problem is that Conditional Formatting doesn't give you the option of specifying a color on the fly.

Maybe arnelgp's suggestion will work. I know it will work with a report, but then the code you are using would also work on a report in the Format() event. Let us know how you make out.
 
Last edited:

Ihk

Member
Local time
Today, 09:11
Joined
Apr 7, 2020
Messages
280
Just wanted to update, problem is solved.
Thank you @arnelgp
Paint event on detail section has worked. continuous form, after it opens looks like this now...
1659525987684.png

The only very minute issue is, 1st click on any color changes to another color (rather than to hold its own color). Though there is no click event. eg, there is 4 times cayan color. If click 1st time any of this, that specefic control will change to another color (other than cyan) unless focus is lost and clicked to another different color. If I stay within cyan, then color becomes stable and stays cyan. Meaning, it happens only 1st click + 1st time in each color. But it is not a problem, I just shared.
These boxes are also basically unbound text boxes.
Instead of this I also tried button here, this was not successfull. All buttons showed one color belonging to ID =1 (Row1), meaning all buttons were black.
-----------------------------------
Also just to share, but not urgent or important.
Next will try this for listbox, so far have no success.
Listbox name is colorlist
I tried this code (below) on form detail section on pain, there was no change. But when I tried this on form load, it changed whole background color of list box to a same color. (not difference as it should be as per ID of the record as above).
Code:
Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset

Dim strSQL As String

strSQL = "SELECT ProductGroupT.*, ColorCodeDec FROM ProductGroupT " & _
        "INNER JOIN ColorsSetT ON ProductGroupT.ID_ColorsSet = ColorsSetT.ID_ColorsSet"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rst.EOF And Not rst.BOF

    Me.ColorList.BackColor = rst!ColorCodeDec
    rst.MoveNext
Loop

rst.Close
End Sub
Just shared if someone has suggestion, but a the moment I can use subform instead of listbox, so problem solved.
Thank you. Kind regards,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:11
Joined
Feb 19, 2002
Messages
42,981
I'm pretty sure that if the paint event for the section worked for this, MS would not have bothered with adding conditional formatting a few versions ago.

Go back to trying the Conditional formatting event. You won't be able to use your embedded color code but you can use an if statement.

If(ColorName = 1234")

You'll need 1 condition for each color option and you'll need to be able to pick it from the color choices offered by the Conditional Formatting dialog.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:11
Joined
May 7, 2009
Messages
19,169
you can add code on the GotFocus event of ColorCode:
Code:
Private Sub ColorCode_GotFocus()
Me!ColorCode.BackColor = Me!ColorCode
End Sub
 
  • Love
Reactions: Ihk

Users who are viewing this thread

Top Bottom