Saving txtbox names as values 1

[air1jcw]

I have a form that when a IID is entered into a txtbox - the "After update" runs a couple of MakeTable Queries and populates several txtboxes on the form - you can then click on a color coded legend (box) - and if the name of the label=the value in a table - the label changes to a predetermined color.
Is there a way to have code loop thru all the labels and if it sees a certain color to concatenate the label names and save them in a different table along with all the other txtboxes on the form? From a command button????

Something like red forecolor label names would be values in a field, green forecolor labels would be values in a field, blue forecolor would be a field, etc...
Here is an example of the code that loops thru the labels and if it finds a match to change the color.

Perhaps this can help??

Private Sub Box248_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 "qryAllocatedIID", _
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

If rstAllocatedIID.EOF Then
Exit Sub

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 = 16711935

End If

.MoveNext
Next I
End With
End If
Next
End Sub

 

[PeteJohnston]

Could you use a variation on your last bit of code?

Dim strRed As String, strGreen As String, strBlue As String
Dim ctrl As Control

For Each ctrl in Me.Controls
  If TypeOf ctrl Is Label Then
    Select Case ctrl.ForeColor
    Case Is = vbRed
      strRed = strRed & "' " & ctrl.Name
    Case Is = vbGreen
      strGreen = strGreen & "' " & ctrl.Name
    Case Is = vbBlue
      strBlue = strBlue & "' " & ctrl.Name
    End Select
  End If
Next

If Len(strRed) > 2 Then
  strRed = Mid(strRed, 3)
End If
If Len(strGreen) > 2 Then
  strGreen = Mid(strGreen, 3)
End If
If Len(strBlue) > 2 Then
  strBlue = Mid(strBlue, 3)
End If

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[MakeItSo]

To loop through all tables/forms and look for the color would not make sense.
The way I see it, the color is assigned during runtime and not stored in a table - and especially not in a form. Plus: You cannot get values from a form which is not open.

What is stored however, is the value that corresponds to the color from the legend table.

So loop throuh all tables to find these colors and then use tablename+RecordID as identifier to store in your table.
The following code is untested and might be buggy, but it should give you a hint.

DIm tbl as TableDef, rst as Recordset
For Each tbl In Access.CurrentDb.TableDefs
 Set rst = CurrentDb.OpenRecordset("SELECT FROM " & _ 
 tbl.Name & "WHERE your field='yourvalue'", dbOpenSnapshot)
 rst.moveFirst
 Do while not rst.EOF
    put your appending code here
 rst.MoveNext
Next

Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[air1jcw]

PeteJohnson,

I am not quite sure if I can use this example??? It looks like it will do what I am wanting... However - I need to be able to strRed, strGreen and strBlue and put them each into the same table in their own fields, along with several other values in txtboxes. Also, some of the labels background color, and border color change color as well, can get those labels names into a "str" as well, and concatenate??
I sure do appreciate your help!! Perhaps a couple of example??

Air

 

[PeteJohnston]

I'm not sure if I'm understanding exactly what you mean when you say "put them each into the same table in their own fields". Are you tryig to create records which store all of the label names which are red, green, blue.... ? If so, maybe you could create a table (tblColors) which holds the label name and the current back, fore, border colors? You could then do something like

Private Sub SetColor()

Dim rst As Recordset, strSQL As String
Dim ctrl As Control

For Each ctrl in Me.Controls
  If TypeOf ctrl Is Label Then
    strSQL = "SELECT * FROM tblColors " & _
             "WHERE Label = '" & ctrl.Name & "'"
    Set rst = CurrentDb.Openrecordset(strSQL)
    If rst.RecordCount > 0 Then
      ctrl.BackColor = rst.Back
      ctrl.ForeColor = rst.Fore
      ctrl.BorderColor = rst.Border
    End If
    rst.Close
  End If
Next

End Sub

If you put a call to this Sub into the places where the labels could change it would restore the previous stored colors.

Private Sub Form_Current()
  SetColor
End Sub

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[PeteJohnston]

Woops!

Sorry, but I've just noticed a little typo -

If rst.RecordCount > 0 Then
  ctrl.BackColor = [COLOR=red]rst!Back[/color]
  ctrl.ForeColor = [COLOR=red]rst!Fore[/color]
  ctrl.BorderColor = [COLOR=red]rst!Border[/color]
End If

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[air1jcw]

PeteJohnston,

I am trying to take the label names/captions, concatenate them, and transfer that into a table. Green labels will be transferred into a field, red labels will be transferred into a field. I am NOT looking to put the label names in color into a table. Just the names themselves. Along with several other text boxes as well. I would like each of these text boxes to go into their own field in the same table. All the different colored labels, and the text boxes will be updated as 1 new record into a table.
I hope this makes more sense!!! I sure do appreciate your help!!

Air

 

[PeteJohnston]

In that case, try the original code which I posted and add a few new lines at the end after the If Len(strBlue) part
[/code]
Dim rst As Recordset

. code from the first post
.
.
If Len(strBlue) > 2 Then
strBlue = Mid(strBlue, 3)
End If
[/code]
' New bit

Set rst = CurrentDb("tblColors")
With rst
  .AddNew
  .Red = strRed
  .Green = strGreen
  .Blue = strBlue
  .Update
  .Close
End With

This will add a record to your table with the names of the labels which are Red, Green and Blue.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[air1jcw]

PeteJohnston,

I appreciate your help!!!

Air

 

[MakeItSo]

Yo air!
Show Pete, how you appreciate it - he deserves it!
;-)

 

[jw5107]

PeteJohnston,

This is Air. There is something wrong with my password. My last reply DID NOT get posted properly.

I keep getting an error message (#438)- "object doesn't support this property or method". And on Debug - it highlights .Addnew - Any fixes to this?? Not quite sure how to make this work. Also for the unbound text boxes on the form - how do I add those to the example that you gave me? Under .Addnew??
Sorry for the confusion but, my post have not been posted properly!!!

Thanks,
Air

 

[PeteJohnston]

I think I must be getting dyslexic - there an amazing number of errors in my last couple of posts! I missed out a bit where I'm opening the table tblColors

Set rst = CurrentDb("tblColors&quot

should be

Set rst = CurrentDb.OpenRecordset("tblColors&quot

MSAccess gets confused and sets the recordset object (rst) to the current database. The database object doesn't support .AddNew so that is why you get the error. When you add the OpenRecordset method I think it should be Okay.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[jw5107]

PeteJohnston,

Awesome - thank you very much!! Everything works just fine. I really do appreciate it!!! I am cookin' with gas now!!!

air.

 

[PeteJohnston]

Ahhhhh!!! I missed some bits out because I didn't read all of your post before I answered .

Set rst = CurrentDb.

OpenRecordset

("tblColors")
With rst
  .AddNew
  .Fields("Red") = strRed
  .Fields("Green") = strGreen
  .Fields("Blue") = strBlue
  .Update
  .Close
End With

creates a new record in table tblColors and assigns the values you have put in the string fields to the fields called Red, Green and Blue. The code lines

rst.Fields("Red") = strRed

and

rst!Red = strRed

do exactly the same thing so you can use whichever method is best for you. File that one in the dictionary under Cats, skinning, ways thereof

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)