2nd Try: Access Data in Subform

[Thelma Lubkin]

I tried to ask this a few days ago: I was then thinking in terms of
using a parameter query--I've since decided that that was not a good
approach.

I have a form that displays information on 'colorsets': setname,
number of colors (limited to >=3, <=11) in set, settype. The form
displays this for one set at a time, but the user can scroll through
all the available colorsets.
The form is in a one-to-many relationship with a subform that
displays, for each color in the colorset:

setname sequenceNo Red Green Blue

(I think that this is a datasheet view: each field is defined as a
separate text box; the parent form contains a subform whose space
contains the whole table of colors)

Under this table of colors sit 11 rectangles. The first n=number in
set,
should show the colors defined by the table's red,green,blue values w/
the rest defaulting to 'white'. I can see on the screen the red,green,
blue values for each color in the set, but I don't know how to access
them in code from what the subform already knows. I posted a few days
ago thinking to use a parameter query for this, but I've been unable
to get that going and I've decided that it has to be easier than that
anyway--after all the subform has already found all those numbers--so
how do I pick them up for use in my own code?
I want to say

ClrRect(j).background = RGB(red,green,blue)

so I need red,green,blue for each color in the set
whenever the colorset in the parent form changes. How can I find these
values?
thanks, --thelma

[Lyle Fairfield]

I'm not sure if this answers your question. This converts Access long
colours to an RGB !!!string!!! (so to speak). Bear in mind that it's an
old function written by an old man late at night!

Public Function RGBString(ByVal colour As Long) As String
RGBString = CStr(colour And 2 ^ 8 - 1) & "," _
& CStr(colour And 2 ^ 16 - 1) \ (2 ^ 8) & "," _
& CStr(colour And 2 ^ 24 - 1) \ (2 ^ 16)
End Function

Sub test()
Debug.Print RGBString(13408767)
' shows 255,153,204
End Sub

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: I'm not sure if this answers your question. This converts Access long
: colours to an RGB !!!string!!! (so to speak). Bear in mind that it's an
: old function written by an old man late at night!

: Public Function RGBString(ByVal colour As Long) As String
: RGBString = CStr(colour And 2 ^ 8 - 1) & "," _
: & CStr(colour And 2 ^ 16 - 1) \ (2 ^ 8) & "," _
: & CStr(colour And 2 ^ 24 - 1) \ (2 ^ 16)
: End Function

: Sub test()
: Debug.Print RGBString(13408767)
: ' shows 255,153,204
: End Sub

I'm afraid that you've just confirmed what my husband keeps telling me:
I can't explain *anything*.

No, I'm not looking for an algorithm to do the color conversion.
What I can't figure out is how I can programmatically access the
the red,green,blue *numbers* shown for each color.

Example:

Form shows:

COLORSETNAME NUMBERofCOLORSinSET SETTYPE
thelma6 6 neutral

Subform shows:

COLORSETNAME SEQUENCENO RED GREEN BLUE
thelma6 1 111 43 2
thelma6 2 44 16 79
thelma6 3 222 100 4
thelma6 4 9 10 11
thelma6 5 99 27 112
thelma6 6 71 14 143

I have an RGB function; what I don't know how to
get in code are the red, green, blue values shown for
a SEQUENCENO e.g.

ClrRect(4).background = RGB(red4,green4,blue4) i.e.
ClrRect(4).background = RGB( 9, 10, 11)

The table's red, green, blue cells are shown in the design
view of the subform as textboxes named RED, GREEN, BLUE
so I can say:

ClrRect(1).background = RGB(RED.value,GREEN.value,BLUE.value)

and it colors rectangle 1. But I don't know how to get to
the numbers beyond row 1 of that table.

thanks, --thelma

[lylefair]

Have you considered walking the recordsetclone of your subform
....something like:
With Me.RecordsetClone
b = .Bookmark
.MoveFirst
Do While Not .EOF
Debug.Print .Fields("Red").Value & "," _
& .Fields("Green").Value & "," _
& .Fields("Blue").Value
.MoveNext
Loop
.Bookmark = b
End With

Of course, if calling from main form one would need an appropriate
replacement for Me.

[Thelma Lubkin]

lylefair <ly***********@aim.com> wrote:
: Have you considered walking the recordsetclone of your subform
: ...something like:
: With Me.RecordsetClone
: b = .Bookmark
: .MoveFirst
: Do While Not .EOF
: Debug.Print .Fields("Red").Value & "," _
: & .Fields("Green").Value & "," _
: & .Fields("Blue").Value
: .MoveNext
: Loop
: .Bookmark = b
: End With

Thank you. That's perfect. I cut it and pasted it into my
subroutine, changed the field names to all capitals, replaced
the debug.print with my code for rectangle coloring and it
works.

Now, not to appear too perfect, my next problem is that I'm
trying to put my 11 rectangles into an array, but whatever I
define it as seems to annoy VBA. I've tried to dimension it
access object, Rectangle, variant, but when I say e.g.

RAray(1).backcolor = 111 it complains

Unfortunately, I don't remember its specific complaints.

--thelma

: Of course, if calling from main form one would need an appropriate
: replacement for Me.

[lylefair]

Air Code:
' modular scope
Dim rArray(2) As Control

Private Sub Command12_Click()
rArray(1).BackColor = RGB(255, 0, 0)
End Sub

Private Sub Form_Open(Cancel As Integer)
Set rArray(0) = Me.Box13
Set rArray(1) = Me.Box14
End Sub

[Thelma Lubkin]

lylefair <ly***********@aim.com> wrote:
: Air Code:
: ' modular scope
: Dim rArray(2) As Control

: Private Sub Command12_Click()
: rArray(1).BackColor = RGB(255, 0, 0)
: End Sub

: Private Sub Form_Open(Cancel As Integer)
: Set rArray(0) = Me.Box13
: Set rArray(1) = Me.Box14

ah, it's this word 'Set' that stopped me. I finally found this
way of 'Set'ting the Control variable via Google yesterday.
Microsoft's own documentation produced a dizzying selection of
articles, none of the first 20 or so relevant. But I've been
unable to find documentation for the Set command itself.

Why do I need to
Set controlVar = someControl
instead of what I tried to do
controlVar = someControl

thank you again for your tireless help.
--thelma
: End Sub