Solved Generate a random hex color string for EACH record (1 Viewer)

561414

Active member
Local time
Today, 03:16
Joined
May 28, 2021
Messages
280
Hello everyone!

I created this little function to generate random hex colors:
Code:
Public Function rndColorHex() As String
Dim r
Dim g
Dim b
Randomize
r = Hex(Int(255 * Rnd) + 1)
Randomize
g = Hex(Int(255 * Rnd) + 1)
Randomize
b = Hex(Int(255 * Rnd) + 1)

rndColorHex = "#" & r & g & b

End Function

I used it in my query intending to generate a random color string for each record, but my query returns the same color string for all records. How can I make it so that it returns a different color for each record?
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:16
Joined
Jan 23, 2006
Messages
15,364
Tell us about your query.

This will get random color values
Code:
Public Function rndColorHex() As String
Dim r
Dim g
Dim b
Randomize
r = Hex(Int(255 * Rnd) + 1)
g = Hex(Int(255 * Rnd) + 1)
b = Hex(Int(255 * Rnd) + 1)

rndColorHex = "#" & r & g & b

End Function

Test routine:
Code:
Sub testrndColorhex()
    Dim i As Integer
    For i = 1 To 5
        Debug.Print CStr(i), rndColorHex
    Next i
End Sub

Result:
1 #7715A8
2 #D7E439
3 #AA3FB9
4 #926211
5 #B2702D

How do you use this in your query??
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:16
Joined
Feb 28, 2001
Messages
27,001
1. You only need RANDOMIZE once per session, perhaps not even in that routine. Like, maybe if you had an Opening Form and that form had a Form_Open routine, you could put RANDOMIZE there and be done with it until that session exits Access.
2. Your formula should drop the "+1" because colors start at 0, not 1.
3. If you are in datasheet view, I'm not sure you can do fully randomized colors. You can make a list of rules and colors, but I don't know that you can easily set up each individual row with an explicit color. If this is NOT datasheet view, there might be a chance, but then scrolling would become a real beast.
4. Some of the possible colors will not be clearly visible depending on your background color. Some of the colors will be indistinguishable from one another at the high or low end of the scale.
 

561414

Active member
Local time
Today, 03:16
Joined
May 28, 2021
Messages
280
Thank you all.
Gasman: I'm generating a random value for each basic color, for instance, this is the value I'm generating right now: #766129.
jdraw: This is the SQL statement generated automatically by the query builder:
SQL:
SELECT TOP 20 tbPersonas.Nombre, Sum(tbExpedientes.Monto) AS SumaDeMonto, rndcolorhex() AS Expr1
FROM tbPersonas INNER JOIN (tbExpedientes INNER JOIN tbSolicitantes ON tbExpedientes.ExpedienteID = tbSolicitantes.ExpedienteID) ON tbPersonas.PersonaID = tbSolicitantes.PersonaID
GROUP BY tbPersonas.Nombre, rndcolorhex()
HAVING (((Sum(tbExpedientes.Monto)) Is Not Null))
ORDER BY Sum(tbExpedientes.Monto) DESC;
The_Doc_Man: I removed the extra randomize and the +1. Still generates only one random color for each record. As for the other points:

This is the big picture there's a charting solution for access on devhunt (can't post links), it's based on a webcontrol and javascript. I was briefly testing it and it's pretty complex for me, but I made it work using my data. Right now, it generates one color for everything, but if I make that query generate one different color for every record, then it'll look better. So I don't really care about contrast against backgrounds or text, I just need a different color. I had success with workarounds that involve the use of each record's name length or amount, but it's pretty dirty. I supposed there would be a better way.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 19, 2013
Messages
16,553
Suspect sql is effectively only running your function once as it sees it as a constant. Have seen this when using a function to generate row numbers

Try including a long parameter and passing the pk when you call it.

example code

Code:
SELECT EventPK, rndColorHex([eventPK]) AS HexCol
FROM tblEvents
WHERE rndColorHex()=""

and the function

Code:
Function rndColorHex(Optional p As Variant = -1) As String
Dim r
Dim g
Dim b

    If p = -1 Then
        Randomize
        rndColorHex = ""
    Else
        r = Hex(Int(255 * Rnd))
        g = Hex(Int(255 * Rnd))
        b = Hex(Int(255 * Rnd))
        rndColorHex = "#" & r & g & b
    End If
  
End Function

produces this result


EventPKHexCol
1​
#69DA11
2​
#7D73DB
3​
#B1321D
4​
#C38E1E

edit: because P is a variant you can pass any unique value from your row - could be a date, a customer or product name for example - so long as it is not -1 :)
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:16
Joined
May 21, 2018
Messages
8,463
I have written about this many times. The solution is not applying a seed or randomizing

1. As @CJ_London said the function is only running once because there is no parameter to make it change for each record. This is the same if you put Now() in a query and it took a long time. Each record would get the same value
You need not ANY seed but a Unique seed per record.
normally you pass in the Primary key if it is a number

2. Randomize is only needed if every time you open the DB you do not want to get the same sequence. The numbers are in a long random sequence for each side, but the sequence will start again on a new opening of the database if the seeds stay constant.

I discuss this in detail here. For some reason I always get an argument on this topic, and I am always correct.

FYI,
If you are wrapping your random function inside another function then you actually do not need a unique seed as long as you pass in a unique parameter causing the function to fire on each record.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:16
Joined
May 21, 2018
Messages
8,463
Here is a demo that may help explain.
Code:
SELECT employees.employeeid,
       employees.lastname,
       Rnd()                AS RandSort1,
       Rnd([employeeid])    AS RandSort2,
       Now()                AS TS,
       Randsort([lastname]) AS RandSort3,
       Randsort("dog")      AS RandSort4,
       Randsort([lastname]) AS RandSort5
FROM   employees
ORDER  BY employees.lastname;

I do not know how access figures this out, but if a function only needs to get called once it only calls it once.
Query2 Query2

Employee IDLast NameRandSort1RandSort2TSRandSort3RandSort4RandSort5
5​
Buchanan
0.54​
0.12​
6/6/2021 6:30:58 PM​
0.40​
0.26​
0.81​
8​
Callahan
0.54​
0.65​
6/6/2021 6:30:58 PM​
0.74​
0.26​
0.44​
1​
Davolio
0.54​
0.35​
6/6/2021 6:30:58 PM​
0.08​
0.26​
0.41​
9​
Dodsworth
0.54​
0.10​
6/6/2021 6:30:58 PM​
0.34​
0.26​
0.71​
2​
Fuller
0.54​
0.19​
6/6/2021 6:30:58 PM​
0.31​
0.26​
0.80​
7​
King
0.54​
0.08​
6/6/2021 6:30:58 PM​
0.15​
0.26​
0.59​
3​
Leverling
0.54​
0.43​
6/6/2021 6:30:58 PM​
0.96​
0.26​
0.24​
4​
Peacock
0.54​
0.96​
6/6/2021 6:30:58 PM​
0.94​
0.26​
0.11​
6​
Suyama
0.54​
0.54​
6/6/2021 6:30:58 PM​
0.98​
0.26​
0.63​
RandSort1 uses Rnd() with no argument and only calls it once
RandSort2 uses a unique value so rnd is called each time
TS use Now and shows this is not unique to rnd. In fact any function will behave this way.
RandSort3 uses a UDF
Code:
Public Function RandSort(dummyParameter As String) As Double
  RandSort = Rnd()
End Function
What is important here is that the parameter has nothing to do with seeding or randomize, but it forces the function to recalculate on each record
RandSort4 shows even a constant argument does not cause the function to recalculate.

So bottom line none of this really has anything to do with the seed or randomize. The trick is to force your function to calculate on each record. This is done by passing a unique (at least changing) argument. As shown you do not have to do anything with that argument.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:16
Joined
May 21, 2018
Messages
8,463
Here is an example of what happens if you do not randomize. Rnd give you a random number but that random number is in a sequence. If you want to start in a different place in the sequence you need to randomize. This only has to happen once when you open the database such as in aan autoexec. People will put it in a function which is overkill but still works.

Randomize.jpg

Open the database and pick a seed (1,2,3). Start generating the random numbers and they will come out in the sequences as shown as long as you do not randomize. You need to close the database and reopen for each seed. This is not a bug, but a purposeful design. For many types of experiments you want random draws, but want the ability to repeat the experiment if necessary.
 

Attachments

  • CompareRandomizeSimple.accdb
    432 KB · Views: 306

561414

Active member
Local time
Today, 03:16
Joined
May 28, 2021
Messages
280
Cool. Now I know why Gasman said I needed to specify the seed. I'm using the code that CJ_London modified and it's returning different colors now. They're very similar though, it always returns a predominant color. In this case, it was green, I think.
chart with colors.jpg

And in this case, there were many grays and looks purple-ish.
chart with colors2.jpg


So I modified the code to look like this, taking into consideration the necessity of a unique parameter:
Code:
Function rndColorRGB(Optional p As Variant = -1) As String
Dim r
Dim g
Dim b

    If p = -1 Then
        Randomize
        rndColorRGB = ""
    Else
        r = Int(255 * Rnd)
        g = Int(255 * Rnd)
        b = Int(255 * Rnd)
        rndColorRGB = "RGBA(" & r & "," & g & "," & b & ",1" & ")"
    End If

End Function

Then I used it in my query like this:
SQL:
SELECT TOP 20 tbPersonas.PersonaID, tbPersonas.Nombre, Sum(tbExpedientes.Monto) AS SumaDeMonto, rndcolorrgb([tbPersonas].[PersonaID]) AS Color
FROM tbPersonas INNER JOIN (tbExpedientes INNER JOIN tbSolicitantes ON tbExpedientes.ExpedienteID = tbSolicitantes.ExpedienteID) ON tbPersonas.PersonaID = tbSolicitantes.PersonaID
GROUP BY tbPersonas.PersonaID, tbPersonas.Nombre, rndcolorrgb([tbPersonas].[PersonaID])
HAVING (((Sum(tbExpedientes.Monto)) Is Not Null))
ORDER BY Sum(tbExpedientes.Monto) DESC;

Now I think I see more variation in the colors now. I also modified it to return RGBA instead of Hexadecimals. Here's the result:

chart with colors3.jpg



So it's been very educational today, guys. Thank you all very much. MajP, all your replies had great amounts of knowledge that I take with me. That database you posted also helped me fix a few codes of other projects that I've done.

Thank you so much, guys.

-Edgar
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:16
Joined
May 21, 2018
Messages
8,463
Cool. Now I know why Gasman said I needed to specify the seed.
To be exact you did not need to specify the seed, you need to pass a changing argument to the rnd function for each record. I know this is confusing and a fine point and beating a dead horse. But it is not really the act of seeding that is the issue in a query. In truth that is just a means to an ends. It is the act of providing a changing argument in a query forcing it to calculate on each record. This is not a rnd function issue at all but a query issue. Only reason I reiterate this is because so many people get this wrong and provide bad answers on the topic. Randomizing has even less to do with it. So if you understand this you will be ahead of the game.

So the below works fine giving changing values in each row without any change in seed and not even using the argument in the code. just forcing a recalculation.

Code:
public Function MyRnd(someChangeValue as variant)as double
  myRnd = rnd()
end function

And this can be shown with other functions like Now().

One final example if you use @jdraw function in a query as written it will return the same value in each record. But if you do it in code as demonstrated no problem,
If however you modify it and have an argument it will depend on what you pass in.

if you pass in a constant
RndColorHex(1)
will return the same value in each record since the argument does not change

if you pass in a changing value
rndColorHex([ID])
will give you a different color for each row in the query since the function recalculates.

Code:
Public Function rndColorHex(Optional SomeValue As Variant = 0) As String
Dim r
Dim g
Dim b
Randomize
r = Hex(Int(255 * Rnd) + 1)
g = Hex(Int(255 * Rnd) + 1)
b = Hex(Int(255 * Rnd) + 1)

rndColorHex = "#" & r & g & b

End Function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 19, 2013
Messages
16,553
If you were to have a table of colour codes to specify the range of colours you actually want, you could modify your function to randomise the required id - just make sure the function can’t generate an id that doesn’t exist
 

561414

Active member
Local time
Today, 03:16
Joined
May 28, 2021
Messages
280
That's a great idea, CJ_London, random items from a list of pre-defined colors could be much better. Thank you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 19, 2013
Messages
16,553
If you go this route, rather than using code to fetch the colour based on the random Id, return the random Id. Then add your colour table to your query, no join (so a cartesian query) and use your function as a criteria for which record to return.

where tblcolours.Id=rtnrandomid(myid)

or create a second query to link your original query on the colour id

Would expect it to be faster than doing disk io for each record

also take heed of majp re randomise. Not tested but it should be possible to generate the same random values in the same order. If so by setting your id’s in the right order and ordering your query you can assign say green to the largest value, blue to the next largest etc - that is one if the things I do using a row number function so perhaps that would be an easier way to go
 
Last edited:

Users who are viewing this thread

Top Bottom