add/remove text to a text box

[moishy]

All that and more (for example; when it returns 0) I read in the help files.

My current problem is I can't figure out how to use the InStr() function to determine if the text exists in txtColor.

I got something to work but it is not the right way of doing it.

Here's my code in all its warped glory.

Private Sub chkRed_AfterUpdate()

    If Me.chkRed= True Then
        If InStr(txtColor, "Red") = False Or IsNull(txtColor) = True Then
            txtColor = txtColor & "Red, "
        End If
    ElseIf Me.chkRed = False Then
        If InStr(txtColor, "Red") = True Then
            ' do nothing
        Else
            txtColor = Replace(txtColor, "Red, ", "")
        End If
    End If

End Sub

 

[vbaInet]

My current problem is I can't figure out how to use the InStr() function to determine if the text exists in txtColor.

Seriosly!! Everything I explained plus all the examples on the link makes no sense?

 

[moishy]

I guess I'm just dense...

 

[vbaInet]

You're not dense. You just need to re-read what you were given and look at the examples in the link. It's very clear and it can't be explained any clearer.

 

[Mr. B]

@moishy,

Not to disagree with anything that vbaInet has said, but I would like propose another way to accomplish what you are wanting to do.

Instead of testing the value of your textbox each time, why not just completely rebuild the string value and then assign it to be the new value of the text box.

If you have a form with the six check boxes and the text box you have described and named with the names you provided, try the code below: The code below is the entire code needed for the entire form to work.

Option Compare Database
Option Explicit

Function ManageColorText()
'declare a string type variable to hold any required text
Dim strColor As String

'check the "Red" check box
If Me.chkRed = True Then
    'only if the check box is true,
    'add the word "Red" to the string
    strColor = "Red"
End If

'check the "Blue" check box
If Me.chkBlue = True Then
    'only if the check box is true,
    'check to see if a value exists in the string
    If strColor > "" Then
        'only if the check box is true,
        'concatenate a comma, a space and
        'the word "Blue" to the string
        strColor = strColor & ", Blue"
    Else
        'otherwise just add the word "Blue" to the string
        strColor = "Blue"
    End If
End If

'check the "Green" check box
If Me.chkGreen = True Then
    'only if the check box is true,
    'check to see if a value exists in the string
    If strColor > "" Then
        'only if the check box is true,
        'concatenate a comma, a space and
        'the word "Green" to the string
        strColor = strColor & ", Green"
    Else
        'otherwise just add the word "Green" to the string
        strColor = "Green"
    End If
End If

If Me.chkYellow = True Then
    'only if the check box is true,
    'check to see if a value exists in the string
    If strColor > "" Then
        strColor = strColor & ", Yellow"
    Else
        strColor = "Yellow"
    End If
End If
If Me.chkBlack = True Then
    If strColor > "" Then
        strColor = strColor & ", Black"
    Else
        strColor = "Black"
    End If
End If
If Me.chkPurple = True Then
    If strColor > "" Then
        strColor = strColor & ", Purple"
    Else
        strColor = "Purple"
    End If
End If
With Me.txtColor
    .SetFocus
    .Value = strColor
End With
End Function

Private Sub chkBlack_AfterUpdate()
'call the function to create the text string
Call ManageColorText
'reset the focus back to the checkbox
Me.chkBlack.SetFocus
End Sub

Private Sub chkBlue_AfterUpdate()
'call the function to create the text string
Call ManageColorText
'reset the focus back to the checkbox
Me.chkBlue.SetFocus
End Sub

Private Sub chkGreen_AfterUpdate()
'call the function to create the text string
Call ManageColorText
'reset the focus back to the checkbox
Me.chkGreen.SetFocus
End Sub

Private Sub chkPurple_AfterUpdate()
'call the function to create the text string
Call ManageColorText
'reset the focus back to the checkbox
Me.chkPurple.SetFocus
End Sub

Private Sub chkRed_AfterUpdate()
'call the function to create the text string
Call ManageColorText
'reset the focus back to the checkbox
Me.chkRed.SetFocus
End Sub

Private Sub chkYellow_AfterUpdate()
'call the function to create the text string
Call ManageColorText
'reset the focus back to the checkbox
Me.chkYellow.SetFocus
End Sub

This code first defines a function that does the heavy work. It checks each check box and based on the value of the check box, it creates the string value and then at the end of the function, it sets focus to the text box (this is required if the text box is unbound, and I am assuming that your is because the string value is is not a value you would normally store in the database) and then assigns the new string value to the text box.

There is code for the After Update event of each of the check boxes that calls the function and then returns focus to the check box that was checked or unchecked.

My goal here was to provide some code that you could get your head around and see just how the string value can be created.

 

[moishy]

Dr. Access,

Thank you, I will give it a try.


vbaInet,

Is this what you had in mind:

Private Sub chkRed_AfterUpdate()

    If Me.chkRed = True Then
        If InStr(txtColor, "Red") < 0 Or IsNull(txtColor) = True Or txtColor = "" Then
           txtColor = txtColor & "Red, "
        End If
    ElseIf Me.chkRed = False Then
        If InStr(txtColor, "Red") > 0 Then
            txtColor = Replace(txtColor, "Red, ", "")
        End If
    End If

End Sub

 

[vbaInet]

We got there finally ...well almost.

Instr() can never return any number less than 0. Is there any instance where a letter can appear in the -1th position? Instr() can return any number from 0 and above or Null. I've explained when it returns Null in post #20.

To catch the Null situation, use Nz(Instr(), 0)

 

[moishy]

Ok, here we go:

Private Sub chkRed_AfterUpdate()

    If Me.chkRed = True Then
        If Nz(InStr(txtColor, "Red"), 0) = 0 Or txtColor = "" Then
            txtColor = txtColor & "Red, "
        End If
    ElseIf Me.chkRed = False Then
        If InStr(txtColor, "Red") > 0 Then
            txtColor = Replace(txtColor, "Red, ", "")
        End If
    End If

End Sub

 

[vbaInet]

Better.

In the ELSE block, you don't need to search for "Red, " anymore, just Replace() it.

Besides that, I've already foreseen problems with Replace() but I want you to realise it during your tests.

 

[moishy]

So far it works as expected, but I'll keep my eyes open.

 

[vbaInet]

Alright, I might as well tell you where it could possibly fail. If the order of the colours are random, then "Red, " will not be found if it was the last colour in the string, e.g. "Green, Red". If the ordering is fixed and the number of colours is also constant then you're fine.

 

[moishy]

So I'll try this:

Private Sub chkRed_AfterUpdate()

    If Me.chkRed = True Then
        If Nz(InStr(txtColor, "Red"), 0) = 0 Or txtColor = "" Then
            If IsNull(txtColor) Or txtColor = "" Then
                txtColor = txtColor & "Red"
            Else
                txtColor = txtColor & ", Red"
            End If
        End If
    ElseIf Me.chkRed = False Then
        If Not IsNull(txtColor) Then
            txtColor = Replace(txtColor, "Red, ", "")
            txtColor = Replace(txtColor, "Red", "")
        End If
    End If

End Sub

 

[vbaInet]

Good thinking!

 

[Mr. B]

@moishy,
Did you try implementing my function and calling it. It does not rely on any specific order of the checkboxes. It will work no matter which of the checkboxes are checked or unchecked at any one time or in which order they are modified.

 

[moishy]

Mr. B.,

You solution works well (as far as I can see, Thank you very much.