Error 2458 -- need help

[Tom]

I have a crosstab query from which I want to pull 2 fields into a form.

In the crosstab query, these 2 fields contain numeric data. Their fields
name are "1" and "2" (for testing purposes right now).

In the form, I want to use VBA code to show a "color" (red, yellow, green)
of the textbox. The color is driven by the numeric value (pulled from the
Crosstab qry).

I use the following VBA code which throws me an error 2458.


*********************

Private Sub Form_Current()

Dim i1 As Integer

For i1 = 1 To 2
SetColors Me(i1)
Next i1

End Sub


*********************

Private Sub SetColors(c As Control)

Select Case c
Case Is = 0: c.BackColor = 16777215 'white
c.ForeColor = 16777215
Case Is <= 25: c.BackColor = 255 'red
c.ForeColor = 255
Case Is <= 50: c.BackColor = 33023 'orange
c.ForeColor = 33023
Case Is <= 75: c.BackColor = 65535 'yellow
c.ForeColor = 65535
Case Is <= 100: c.BackColor = 32768 'green
c.ForeColor = 32768
Case Else: c.BackColor = 8421504 'gray
c.ForeColor = 8421504
End Select

End Sub

*********************


This code works fine in another application (which does not use crosstab
queries though).

Right now, when I open the form, I get the following error:

"Run-time error '2458': The control number you specified is greater than
the number of controls".

Since I only have 2 controls (1 & 2) and i1= "1 to 2", I don't know what the
problem is.

Does anyone know how I can fix it?



Thanks,
Tom

 

[Cheval]

This has nothing to do with crostab queries, it's your
code that needs fixing.

Sub SetColors you're talking in a generic control object
and trying to use it in a switch statement?!?!? Wrong! The
contol could be anything. Change it to be either:

Faster code (I'd call it GetColours):
Function SetColors(iValue as integer, lngFColour as Long,
lngBColour) As Boolean
OR
If you must pass a control.
Function SetColors(tbCtrl as TextBox) As Boolean

 

[Tom]

Cheval:

Thanks for your reply... I truly appreciate it.

I still have some problems w/ the form...

As you suggested, I replaced the following (see code below &&&s):
- "SetColors" with "GetColours"
- "Private Sub SetColors(c As Control)" with "Function GetColours(tbCtrl As
TextBox) As Boolean"
- "c" with "tbCtrl"


Here's what I have on the form... plus the steps I'm following:
- 3 textboxes (their control names are: "1", "2", & "3")
- at this time, I still get the "Run-time error '2458'"
- if I ignore it and press "End", the form pops up and only textbox "2" &
"3" have
the proper colour. Textbox 1's color is white and its font is black.
That's wrong!
Its numeric value = 56; hence it shoud turn "yellow" (textbox 2's value =
100 and is "green"; textbox 3's value = 66 and "yellow")

I coincidentally found out that if I add an UNBOUND textbox, the error 2458
disappears (when opening the form).

However, that would mean I have 4 controls (of which only 3 of them are
really needed) make the error "The control number you specified is greater
than the number of controls" disappear.

I dont' understand that!

Also, even if I were to leave the UNBOUND text box on the form, textbox 1
still does not turn "yellow".


I hope you won't mind providing me addtional pointers on this.

To recap, my questions are the following:
1. How can I get rid of the error 2458 (once the unwanted UNBOUND textbox
has been removed?
2. How can I make sure that textbox 1 also gets updated?


Thanks so much in advance,
Tom




&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Option Compare Database

Private Sub Form_Current()

Dim i1 As Integer

For i1 = 1 To 3
GetColours Me(i1)
Next i1

End Sub

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Function GetColours(tbCtrl As TextBox) As Boolean

Select Case tbCtrl
Case Is = 0: tbCtrl.BackColor = 16777215 'white
tbCtrl.ForeColor = 16777215
Case Is <= 25: tbCtrl.BackColor = 255 'red
tbCtrl.ForeColor = 255
Case Is <= 50: tbCtrl.BackColor = 33023 'orange
tbCtrl.ForeColor = 33023
Case Is <= 75: tbCtrl.BackColor = 65535 'yellow
tbCtrl.ForeColor = 65535
Case Is <= 100: tbCtrl.BackColor = 32768 'green
tbCtrl.ForeColor = 32768
Case Else: tbCtrl.BackColor = 8421504 'gray
tbCtrl.ForeColor = 8421504
End Select

End Function


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

 

[Tom]

Cheval:

I have come up w/ the solution myself... your information helped me to
investigate this further.

Here's what the problem was... the numeric fields were autonumbers. I those
that data type in order to reference different levels of tasks
automatically.

I believe the form "choked" on that autonumber.

Although my solution may not be the smoothest process, I now use the
autonumber plus an ID number (via concatenation in a query). So,
autonumber "1" becomes e.g. "A1".

Based on A1, A2, A3, the form now updates the values.


Thanks for your feedback,
Tom