Labels on a form 2

[air1jcw]

Below is the code I am working with. I don't have it quite right yet.

I am trying to get the results of a select query to show as lables changing color on a form. This is what I got so far. I would like for the labels to change color when the values in the query match the caption (of the label) name. Thank you in advance!!!

Private Function IID_AfterUpdate()

Dim rstgtiaiq As New ADODB.Recordset
Dim ctrl As Control

' Open a recordset based on the WWReviewhelper query.
rstgtiaiq.Open "WWReviewhelper", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

' If no allocatons previously set, display a message and exit.
If rstgtiaiq.RecordCount = 0 Then
DisplayMessage "There are NO allocations set on this part."
Exit Function
End If

For Each ctrl In Me.Controls

If TypeOf ctrl Is Label Then
Do Until rstgtiaiq.EOF
If ctrl.Caption = rstqtiaiq!GTWY Then
ctrl.ForeColor = vbBlue
rstgtiaiq.MoveNext

Loop


End Function

 

[nice95gle]

Do Until rstgtiaiq.EOF
    If ctrl.Caption = rstqtiaiq!GTWY Then
        ctrl.ForeColor = vbBlue
       rstgtiaiq.MoveNext
    End If
   
Loop
    End If
    
Next

looks like you are missing the 'End If' after rstgtiaiq.MoveNext and the 'End If' after Loop and the 'Next' statment

And what is 'DisplayMessage'? shouldn't it be

MsgBox "There are NO allocations set on this part."

 

[Zion7]

Hi air1jcw,
Have you considered "Conditional Formatting", from the Format "icon" on the menu bar?
Once you access it, you'll find it rather self explanatory.
Bear with me here, I didn't quite follow your code (lack of my experience), but I've used conditional formatting a lot.
When control formatting, changes on a form, It changes all forms, unless your code is Using the OnCurrent event of the form...or, conditional formatting.
Hope this helps, good luck!

 

[air1jcw]

I am still having problems with this code. I want to results of a query to show as "color changed" labels on a form. I would like for the code to open a query named "WWReviewhlpr". Then the label captions that match the query recordset to change color.

Any body out there with some skills?? Everybody has been "bailing" me out thus far!!
I thank you in advance!!!!!!!!!!

I keep getting this error:

Run-time error '-2147217900(80040e14)':
Invalid SQL statement; expected'DELETE','INSERT','PROCEDURE','SELECT', or 'UPDATE'.

When I click debug - rstgtiaiq.Open "WWReviewhlpr", _
CurrentProject.Connection, adOpenKeyset
is highlighed.

This is the code as of right now:

Private Sub IID_AfterUpdate()
Dim rstgtiaiq As New ADODB.Recordset
Dim ctrl As Control

' Open a recordset based on the WWReviewhelper query.
rstgtiaiq.Open "WWReviewhlpr", _
CurrentProject.Connection, adOpenKeyset

' If no allocatons previously set, display a message and exit.
If rstgtiaiq.RecordCount = 0 Then
DisplayMessage "There are NO allocations set on this part."

End If

For Each ctrl In Me.Controls

If TypeOf ctrl Is Label Then
Do Until rstgtiaiq.EOF
If ctrl.Caption = rstqtiaiq!GTWY Then
ctrl.ForeColor = vbBlue
rstgtiaiq.MoveNext
End If

Loop
End If

Next

End Sub

 

[RoyVidar]

When I get the same errormsg, it's either typo in the query name or the query is trying to collect a value from a form that's not open. Might of course be other reasons. (There is a difference in the query name from first to last post.)

If neither, try out with a less complicated query, just to get it open, and take it from there.

Roy-Vidar

 

[nice95gle]

Your code is fine, your problem is your query. We can't help with that because we don't know what it looks like.

I took your code and replaced your query with one of mines and it works great. The problem is with

"WWReviewhlpr"

Post the sql statement that builds your query.

Note: Your query has to be a plain old select query.

 

[air1jcw]

ATTN: nic95gle

Here is the sql statement for the "WWReviewhlpr" query

SELECT gtiaiq.GTWY
FROM gtiaiq LEFT JOIN itemmstr ON gtiaiq.IID = itemmstr.IID
WHERE (((gtiaiq.IID)="3230004650225&quot AND ((gtiaiq.MAX)>0) AND ((gtiaiq.[MATL TYPE])="SV&quot AND ((itemmstr.CLASS) Like "R*" Or (itemmstr.CLASS)="E&quot);

This is a plain ole select query. I originally had it set up with a parameter for the "IID" field. An unbound text box on a form gets the "IID" dumped into it, and then after update, the code runs. Perhaps this was the problem? I have since changed the parameter and just put a example "IID" for the criteria in the query.

Thanks for all the help!!!
air1jcw

 

[nice95gle]

Make sure you have

'Microsoft ActiveX Data Objects 2.7 Library'

checked

Also you might want to look at the Jet Engine Service Pack you are using. But before you do that try the following code.

If this doesn't work I'm sorry

Here you go:

Private Sub IID_AfterUpdate()
    Dim rstgtiaiq As New ADODB.Recordset
    Dim ctrl As Control
    Dim I As Integer
    
    

' Open a recordset based on the WWReviewhelper query.

rstgtiaiq.Open "WWReviewhlpr", _
 CurrentProject.Connection, adOpenKeyset

' If no allocatons previously set, display a message and exit.
If rstgtiaiq.RecordCount = 0 Then
    MsgBox "There are NO allocations set on this part."
End If
    
    'loop through form controls
    For Each ctrl In Me.Controls
    
If TypeOf ctrl Is Label Then
     With rstgtiaiq
        .MoveFirst
            For I = 1 To .RecordCount
                If ctrl.Caption = !GTWY Then
                    ctrl.ForeColor = vbBlue
                End If
            .MoveNext
        Next I
    End With
End If
Next

End Sub

 

[air1jcw]

ATTN: nice95gle

I have taken your "fix" and applied it. Everything works fine. This code does exactly what I want it to!! Thank you very much! One last question:
Can you give me an "error handling code"?

Here is the code as it works right now:

Private Sub Command94_Click()
Dim rstAllocatedIID As New ADODB.Recordset
Dim ctl As Control
Dim I As Integer


' Open a recordset based on the WWReviewhelper query.
rstAllocatedIID.Open "WWideGTWYAllocations", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

' If no allocations are set.
If rstAllocatedIID.RecordCount = 0 Then
MsgBox "There are no Allocations set on this part."
End If



'loop through form controls
For Each ctrl In Me.Controls

If TypeOf ctrl Is Label Then
With rstAllocatedIID
.MoveFirst
For I = 1 To .RecordCount
If ctrl.Caption = !GTWY Then
ctrl.ForeColor = vbRed

End If

.MoveNext
Next I
End With
End If
Next

End Sub

The error message is this :

Run-Time Error '3021'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I understand what this is saying, but don't know how to apply the code to ignore it. Whenever the query - "WWideGTWYAllocations" is ran, there may or maynot be any records. The MsBox lets the user know there are no records, but then the error pops up. I would like for the code to ignore the True part of the EOF or BOF.

Thank you in advanced!!!

air1jcw

 

[nice95gle]

In the part of your code below add an

Exit Sub

line.
So it would look like this.

' If no allocations are set.
If rstAllocatedIID.RecordCount = 0 Then
  MsgBox "There are no Allocations set on this part."
   Exit Sub
End If

This will fix the problem.
The reason you are getting the error is because you have no record but the code is continuing like it does. So what you need to do is stop the code once the RecordCount returns 0.
Error handling is a good thing to have but for this instance it will be more harm than good.