Can you use a cmd button to change the backcolor of a textgbox? 1

[testkitt2]

Hello to all
I have a DB that I, and 2 other people use at the job. I'm trying to setup a command button with VB code that upon clicking that cmd button it will change the backcolor of that textbox to whatever I set the color too. The thing is I need the command button to be able to change the backcolor of just about any of one or more textboxes currently on the form. but only one at a time. The way it should work is this...If you need to change the color of a textbox just click into the textbox to set the focus then click the command button.
The reason I need this.....
The changing of the color will be used as a highlighter so that if another user seas a color on a record he or she knows that the information in that textbox may change or may need to be changed. I can't use conditonal formatting cause I dont know what will be typed into some of the textboxes.
Any help is appreciated.
Thanks
JZ

Testkitt2

 

[CaptainD]

Why not use something like this, or am I mising something in what you want to do?

Private Sub Text0_GotFocus()
Text0.BackColor = 8454016 'light green back color
End Sub

Private Sub Text0_LostFocus()
Text0.BackColor = 16777215 'normal back color
End Sub

 

[testkitt2]

Hey CaptainD

Thanks for your input..but the code you suggested below is not exactly what I want.

Private Sub Invoice_GotFocus()
Invoice.BackColor = 8454016 'light green back color
End Sub
_____________________________________________________
Private Sub Invoice_LostFocus()
Invoice.BackColor = 16777215 'normal back color
End Sub

I want the code to work off a command button. And the thing is this I want to click inside of any textbox on my form and with the command button change its color. this will serve as a reminder for the other users that...that field needs updating.
Lets says we buy a part for a truck and the description of that part is a "seal" and we type "Seal" by highlighting it with the procedure above..when the other user logs on he or she knows that that description is too broad and needs updating to something like " Right rear Seal with installation tool".

I hope I explained myself...sorry for being long winded..
JZ

Testkitt2

 

[CaptainD]

If I understand you correctly, You are in the database program and you mark a field to indicate it needs to be updated. When someone else logs into the database and open that record, the field you marked is colored indicating where you want the update to be done. is that correct?

 

[AccessGuruCarl]

Testkitt2

You can try something like this

If there is only the 1 field that would ever need updating

Add a Yes/No field to that table.

Add the checkbox to your form, make it invisible

Add a doubleclick event to your textbox to change the value of the checkbox

Private Sub YourTextBox_AfterUpdate()
    Me.chkboxname = 0
End Sub

Private Sub YourTextBox_DblClick(Cancel As Integer)
    Me.chkboxname = -1
End Sub

Private Sub Form_Current()
    If Me.chkboxname = -1 Then
        Me.YourTextBox.BackColor = vbRed
    Else
        Me.YourTextBox.BackColor = vbWhite
    End If
End Sub

Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SuryaF]

Hi testkitt.
I suppose you are using that form for more than one record.

If you highlight the texbox itself on the form design (change the background color), that will affect the form, doesn't matter what record you are showing. So for all records your textbox will be highlighted, even for the ones that don't need review.

You'll have to save the highlighted property in the record itself, as a field in the table.
If there are not too many fields that can be highlighted I'd suggest to add a Yes/No field for each of them (e.g. HLDescr, HLCode, HLAmount). Then work with conditional formatting. You can switch the HLField value on dblclick-ing the textbox, on a popup menu or a general button. A generic code could be:

Private Sub HLBtn_Click()
    FldName = Screen.PreviousControl.Name
    Me.Controls("HL" & FldName)=Not Me.Controls("HL" & FldName)
Exit Sub

If there are too many fields, instead of creating an extra field you can create another table that remembers the fields that are highlighted (a one-to-many relationship with your current table). Then the OnCurrent event reads froom this new table and formats all the fields. But again, this would only work if you work with Single Form. For Countinuous Form or Datasheet you can only use conditional formatting.

 

[Eupher]

SuryaF,

If there are too many fields, instead of creating an extra field you can create another table that remembers the fields that are highlighted (a one-to-many relationship with your current table). Then the OnCurrent event reads froom this new table and formats all the fields. But again, this would only work if you work with Single Form. For Countinuous Form or Datasheet you can only use conditional formatting.

This is exactly the technique I have used before. I wrote a function that uses the double-click event to change a textbox to boldface. But of course, for the boldface to persist, gotta store that info somewhere, so as you suggest, a related table works well.


testkit2, I prefer the double-click event rather than a command button. It makes for more coding, because you have to call the function from every control to which you want to give this functionality. But I think your users might get tired of clicking in a field, then clicking a button to effect the change - potentially involves a lot more mouse movement. Let me know if you want to see the code I used.

HTH,

Ken S.

 

[testkitt2]

Hello to all
Eupher (MIS)Thanks and yes I would like to see your code.
But again remember...all the suggestions here does not seem to hit my target question.
Ok..The Main form has 10 fields all of which are textboxes. Yes some of the fields are restricted to what can go in them. But..here it goes again.. I can use the double click option to refer to that textbox and say turn it green. but there are at least 4 textboxes that can be highlighted. one at a time or all at once..(slight chance it will be all). And to answer a previous question ...this is specific to only those records that require a highlight. So instead of setting up the double click for each textbox seperately I was wondering if I could set up a command button that will change the color if the textbox has the focus. that's why I said to click inside the textbox first then the command button. Once the textbox is updated by myself or another user then that textbox should go back to its orignal color. The highlight is to alert the other users that that field needs some updating..a better description or a part number etc. Can this even be done...you see once in a record 1 out of the 5 textboxes may be a candidate..or maybe none. this is why the command button can not specify a particular textbox but when someone clicks inside of one of those textboxes and then clicks on the command button..the code needs to recognize that textbox as one that needs to be highlighted.
Thanks to all
JZ

Testkitt2

 

[CaptainD]

when someone clicks inside of one of those textboxes and then clicks on the command button..the code needs to recognize that textbox as one that needs to be highlighted.

The point most of the people here are trying to get across is, double clicking a textbox can achieve the same results as clicking in a textbox, passing which textbox has the focus and clicking a command button to change it's color, without the extra steps of your method.

A double click inside a textbox, gives that box the focus and can run code from changing the backcolor to updating a database, just like you can with the click of a command button. Both methods start an action that can run code.

 

[Eupher]

testkit2,

Okay, here ya go...

First, the sub that toggles the attributes of the textbox:

Private Sub ToggleColor(CurCtrl As Control)

Dim SQLStmt As String, CurDB As DAO.Database, Rs As DAO.Recordset

[COLOR=green]'cancel normal double-click behavior[/color]
DoCmd.CancelEvent

Set CurDB = CurrentDb()

[COLOR=green]'Toggles font to boldface/normal, forecolor to white/black and backcolor to red/white
'when user double-clicks anywhere in the field
'and writes to tblRememberBold so it will still be that way when form/record is opened later[/color]
If CurCtrl.FontWeight = 400 Then
    With CurCtrl
        .FontWeight = 700
        .BackColor = vbRed
        .ForeColor = vbWhite
    End With
    Set Rs = CurDB.OpenRecordset("tblRememberBold")
    Rs.Index = "AutoID"
    Rs.AddNew
    Rs!fldMyID = Me!AutoID
    Rs!fldFieldName = CurCtrl.Name
    Rs.Update
    Rs.Close
    Else
        With CurCtrl
            .FontWeight = 400
            .BackColor = vbWhite
            .ForeColor = vbBlack
        End With
        SQLStmt = "SELECT * FROM tblRememberBold WHERE fldMyID = " & Me!AutoID & " AND " _
        & "fldFieldName = '" & CurCtrl.Name & "'"
        Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
        Rs.Delete
        Rs.Close
End If

[COLOR=green]'release variables[/color]
Set Rs = Nothing
Set CurDB = Nothing

End Sub

Next, the form's OnCurrent sub which pulls the data from tblRememberBold and sets the form's textbox attributes as you navigate through the records:

Private Sub Form_Current()

Dim SQLStmt As String, CurDB As DAO.Database, Rs As DAO.Recordset, ctrl As Control

Set CurDB = CurrentDb()

[COLOR=green]'next line is optional[/color]
Me!AutoID.SetFocus

[COLOR=green]'Reset controls to normal[/color]
For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox Then
        With ctrl
            .FontWeight = 400
            .BackColor = vbWhite
            .ForeColor = vbBlack
        End With
    End If
Next ctrl

[COLOR=green]'check for new record, otherwise lack of AutoID will raise an error[/color]
If Not Me.NewRecord Then
    [COLOR=green]'change fontweight, backcolor, and forecolor based on tblRememberBold data[/color]
    SQLStmt = "SELECT * FROM tblRememberBold WHERE fldMyID = " & Me!AutoID & ";"
        Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
        Do While Not Rs.EOF
            With Me(Rs!fldFieldName)
                .FontWeight = 700
                .BackColor = vbRed
                .ForeColor = vbWhite
            End With
            Rs.MoveNext
        Loop
        Rs.Close
End If

[COLOR=green]'release variables[/color]
Set ctrl = Nothing
Set Rs = Nothing
Set CurDB = Nothing

End Sub

And finally, the code for the textbox double-click event (must be placed for every textbox you want to have this functionality):

Private Sub fldLastName_DblClick(Cancel As Integer)

Dim ctrl As Control
Set ctrl = Screen.ActiveControl
Call ToggleColor(ctrl)
Set ctrl = Nothing

End Sub

Let me know if you need any explanation on any of this.

HTH,

Ken S.

 

[AccessGuruCarl]

Testkitt2,

Here is what your looking for, for the button click

Private Sub Command5_Click()
Dim FieldName
  Screen.PreviousControl.SetFocus
'If you really need the controls name
  FieldName = Form.ActiveControl.Name
MsgBox FieldName
'Euphers Code would be here
'Instead of the double-click event.
Dim ctrl As Control
  Set ctrl = Screen.ActiveControl
  Call ToggleColor(ctrl)
  Set ctrl = Nothing
'Set focus back to the button
 Screen.PreviousControl.SetFocus
End Sub

Now all you need to do if decide how/where to store the data. If it's backcolor that will change only, I'd suggest a Yes/No for each field in the table but thats for you to decide.

Good Luck,
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[TheAceMan1]

How are ya testkitt2 . . .

[blue]I can't use conditonal formatting cause I dont know what will be typed into some of the textboxes . . . And to answer a previous question ...[purple]this is specific to only those records that require a highlight[/purple].[/blue]

[purple]Since your hilighting is specific to any record, I don't see how you'll accomplish this without Conditional Formatting![/purple] . . . Have no fear for it can be done. Its a method I've used in enough of my DB's.

The method uses a [blue]packed string[/blue] (one character position per field you want to control). [blue]Each field on the form is handled by an assigned index into this string.[/blue] Each position of the string is either "0" or "1", with one of course enabling hilighting. The packed string resides in an [blue]added text field[/blue] in the underlying table.

The method uses the [blue]DoubleClick[/blue] event of the fields and is a [blue]toggle[/blue]. DoubleClick to hilight . . . DoubleClick to remove hilight. All you have to do is:
[ol][li]Add a new field to the underlying table.[/li]
[li]Update the default of the new field for all previously saved records.[/li]
[li]Copy the code.[/li]
[li]Call a common routine from the fields you wish to control.[/li]
[li]Set the conditional formatting for the fields of interest.[/li][/ol]

So lets get on with it . . .

[ol][li]In the underlying table of the form add a [blue]new field[/blue] with the following properties:
[ol a][li]Name [purple]Flg[/purple][/li]
[li]Data Type [purple]Text[/purple][/li]
[li]Field Size: [purple]A number from 1 to 255 depicting the amount of fields you want to control. If you intend to add fields in the future use that total number instead. In any case its better to have a few more as no error checking is done for less.[/purple][/li]
[li]Default Value [purple]as many zeros [blue]"0000 . . ."[/blue] as the Field Size (this is a textfield so include the double quotations).[/purple][/li][/ol][/li]
[li]Save & close the table[/li]
[li]We have to sidw step here as all previous records don't have the zeros "0000 . . ." for control and will error. So in a module in the modules window, copy/paste the following:

[blue]Public Sub DefFlg()
   DoCmd.RunSQL "UPDATE [purple][b]YourTableName[/b][/purple] SET Flg = String([purple][b]FieldSize[/b][/purple],'0');"
End Sub[/blue]

[ol a][li]Don't forget to make the proper substitution for [purple]YourTableName[/purple].[/li]
[li]Use the [purple]Field Size[/purple] you used for the new field![/li]
[li]Open the Immediate window

and enter [blue]Call DefFlg[/blue] and hit enter.[/li][/ol][/li]
[li]With that done, open the form in [blue]design view[/blue] and be sure the new field is included the forms [blue]RecordSource[/blue]. Have a look at the Field List

for a quick check. It should appear there. If not . . . make it so. Remember the field doesn't have to be on the form, it just has to appear in the Field List.[/li]
[li]In the code module of the form, copy/paste the following:

[blue]Public Sub TglFlg(Idx As Integer)
    Dim txt As String
    
    txt = Me!Flg
   
   If Mid(txt, Idx, 1) = "1" Then
      Mid(txt, Idx, 1) = "0"
   Else
      Mid(txt, Idx, 1) = "1"
   End If
   
   Me!Flg = txt
   Me.Refresh

End Sub

Public Function SetHiLite(Idx As Integer) As Boolean
    Dim txt As String
    
    txt = Me!Flg
   
   If Mid(txt, Idx, 1) = "1" Then
      SetHiLite = True
   Else
      SetHiLite = False
   End If
   
End Function[/blue]

[ol a][li][blue]TglFlg(Idx As Integer)[/blue] is the common routine called by the DoubleClick events of your controls of interest.[/li]
[li][blue]SetHiLite(Idx As Integer)[/blue] is the function called by Conditional Formatting.[/li][/ol][/li]
[li]Now in the [blue]DoubleClick[/blue] event of the controls of interest, copy/paste the following, replacing [purple]Index[/purple] with a number from 1 to the fieldsize you used. Each field should have a unique number:

[blue]   Call TglFlg([purple][b]Index[/b][/purple])[/blue]

Write down the indexes you use for the controls to make it easier to setup conditional formatting which is next.[/li]
[li]Finally . . . [blue]Conditional Formatting.[/blue] For each field of interest conditional formatting should be:

[blue]Expression Is Mid([Flg],[purple][b]Index[/b][/purple],1)="1"[/blue]

[ol a][li]Set your colors and click OK.[/li][/ol][/li]
[li]Open your form and do your Testing![/li][/ol]
[blue]Let me know how ya make out! . . .[/blue]

See Ya! . . . . . .

 

[Eupher]

Hi, AceMan!

Very ingenious! Your solution deserves a star for sheer smartness. :-0

But... either your SetHiLite function is superfluous or the Conditional Formatting expression should be:

Expression Is SetHiLite(Index)

But either should work.

More goodies for my library... ;-)

Ken S.

 

[TheAceMan1]

Eupher . . .

I forgot to indicate that [blue]Expression Is[/blue] is selected from the [blue]combobox[/blue] in the conditional formatting window! . . .

See Ya! . . . . . .

 

[Eupher]

Yes, AceMan, I assumed that was your intent. I was pointing out that the expression doesn't actually call the SetHiLite function, but accomplishes the same action directly. Just nit-picking...

Ken S.

 

[TheAceMan1]

Eupher . . .

Woops . . . didn't read your post carefully.

Yes you were right . . . [blue]SetHiLite[/blue] is not needed. I copied the wrong expression.

I'll have a couple of amendments in the next post . . .

See Ya! . . . . . .

 

[TheAceMan1]

To All! . . .

Just a couple of amendments to my code.
[ol][li]The function [blue]SetHiLite[/blue] is not used and can be deleted.[/li]
[li]A performance issue arises when a field in the conditional formatting expression isn't physically on the form (I acutally prompt the reader not to bother). What happens is the [blue]formatting fails to trigger[/blue]. This is the reason for Me.Refresh as the last line in the routine [blue]TglFlg[/blue]. Refresh triggers formatting and cause a very discernable flicker as the records are scanned ([purple]very ugly[/purple]). To fix this:
[ol a][li]Put the Flg field on the form and set its [blue]Visible[/blue] property to [blue]No[/blue].[/li]
[li]Remove Me.Refresh from the routine [blue]TglFlg[/blue].[/li][/ol][/li][/ol]
Now conditional formatting should work very smooth, what I'm use to seeing . . .


[blue]Cheers All! . . .[/blue]

See Ya! . . . . . .

 

[Eupher]

Hi, again, AceMan,

First, I want to emphasize that I really admire the efficiency of your formatting solution. By comparison, mine seems bloated and cumbersome! I offer the following comments - not as criticism! - but only as observations in the spirit of a free exchange of ideas.

1) I was quite surprised to see a noticeable (although tiny) delay in formatting the fields as I scrolled through the records. I had placed the Flg field on the form and set .Visible to "no" and removed Me.Refresh from the code as you suggested. I compared your method side-by-side with my method - duplicate forms, duplicate data, etc., and could not see a similar effect with my method. This really took me by surprise, as I would have thought that opening a recordset would be far slower. Perhaps it's a question of the timing of the OnCurrent event vs. conditional formatting? I wonder if turning Echo off would change things?

2) One effect of your method - whether positive, negative, or neutral, I can't say - is that since your method involves writing data to the current record, other things are invoked. For instance, the double-click now makes the record dirty; also, if you double-click in a field in a new record, the field is formatted AND a new record is created.

Your thoughts?

Ken S.

 

[TheAceMan1]

Howdy Eupher . . .

In 1) The scrolling thingy can't be helped. Conditional Formatting has to change its indexing if your scrolling while its executing as it has to keep track of whats visible on the screen and and off . With a large record count if wait then scroll to the bottom, there's every chance you'll see additional updates there. In any case your code can only handle a single record at a time. [blue]Have you tried your method on a continuous form and hilight different fields in more than one record simultaneanously?[/blue] . . .I could be wrong but [blue]testkitt2's[/blue] info appears to indicate a continuous form. Conditional Formatting works in Single & Continuous View.

In 2) I know the record becomes dirty, but wether the user moves to a new record or closes the DB, it gets saved anyway. I've had no problem with saving this way since the birth of 2K. If the programmer feels shakey about this saving a [blue]DoCmd.RunCommand acCommandSaveRecord[/blue] should be very relaxing.

[blue]Good catch[/blue] on the [blue]NewRecord thingy[/blue]. I'll have to make another amendment to the code . . .

See Ya! . . . . . .

 

[TheAceMan1]

Howdy All!

Thanks to the sharp radar & testing by [blue]Eupher[/blue] another amendment is in order. The amendment involves the following:
[ol][li]When data is written to the [blue]Flg[/blue] field it puts the record in [blue]edit mode[/blue] (if not already). This may make some of you nervous as far as [blue]saving[/blue] is concerned, so a line of code was added to correct this.[/li]
[li]The same problem above occurs if you [blue]Double Click[/blue] in a [blue]new record[/blue]. So code was added for correction.[/li][/ol]

Replace the [blue]TglFlg[/blue] routine with the following (all additions are in [purple]purple[/purple]):

[blue]Public Sub TglFlg(Idx As Integer)
   Dim txt As String
    
   [purple][b]If Not Me.NewRecord Then[/b][/purple]
      txt = Me!Flg
      
      If Mid(txt, Idx, 1) = "1" Then
         Mid(txt, Idx, 1) = "0"
      Else
         Mid(txt, Idx, 1) = "1"
      End If
      
      Me!Flg = txt
      [purple][b]DoCmd.RunCommand acCmdSaveRecord[/b][/purple]
   [purple][b]End If[/b][/purple]

End Sub[/blue]

[blue]Cheers Again! . . .[/blue]

See Ya! . . . . . .