Searching for specific characters in a text box (1 Viewer)

[Simtech]

I have a text box on a form where a user can enter a customer control number. These numbers are formatted as follows RED12-HK001 or RED12-1Z001 where the characters HK or 1Z represent the account codes and other characters represent equipment (RED), the year (12) and the sequence number (001,002,,,).

What I would like to do is open a specific form depending on whether the user has entered a control number with either the account code HK or 1Z in it. Something like this:

If the control number contains the characters HK then open form a.
If the control number contains the characters 1Z then open form b.

I have no idea how write code that will search for specific characters in a larger string.

Thank you for any help you can provide.

 

[pbaldy]

Check out the InStr() function.

 

[ypma]

This may be the blind leading the blind but would a Predicate LIKE do the trick using wildcare character for string comparisons . ? = Any single character # any Single Number or *= zero or more characters. For your starter try the following code and if that works , instead of the msgbox insert your open form command.

Private Sub TextBox_AfterUpdate()
If Me.TextBox Like "RED12-HK***" Then
MsgBox "helloHK"
ElseIf Me.TextBox Like "RED12-IZ*" Then
MsgBox "Hello IZ "


End If

end Sub
Bob

 

[mtn]

Put the two functions below in a module:

Function OpenSpecialForms(strCustomerNbr As String)

    If IsNull(strCustomerNbr) = True Or strCustomerNbr = "" Then
        MsgBox "Please enter a customer number to continue"

    ElseIf CountStringOccurrence(strCustomerNbr, "HK") >= 1 Then
        DoCmd.OpenForm "frmA"   'change this to your form name

    ElseIf CountStringOccurrence(strCustomerNbr, "1Z") >= 1 Then
        DoCmd.OpenForm "frmB"    'change this to your form name

    Else
        MsgBox "Please enter a valid customer number to continue"
    End If

End Function

'Source: http://www.accessmvp.com/kdsnell/VBA_Functions.htm#CtStrOccur
Public Function CountStringOccurrence(ByVal strString As String, _
                                      ByVal strCharString As String, Optional ByVal blnCase As Boolean = True) As Long
    Dim lngPosition As Long, lngLen As Long, lngCount As Long

    lngCount = 0
    lngLen = Len(strCharString)

    If blnCase = False Then
        ' Search is to be case-insensitive, so convert both the character string
        '       and the string being searched to upper case characters
        strString = UCase(strString)
        strCharString = UCase(strCharString)
    End If

    For lngPosition = 1 To Len(strString) - lngLen + 1
        ' If the desired character string is found, increment the counter
        If StrComp(Mid(strString, lngPosition, lngLen), strCharString, vbBinaryCompare) = 0 _
           Then lngCount = lngCount + 1
    Next lngPosition

    CountStringOccurrence = lngCount

    Exit Function
End Function

Then from your button that opens the form or from the AfterUpdate Event of your text box put:

OpenSpecialForms Me.YourTextBoxName

That should definitely answer your question.

 

[Simtech]

Thanks to ypma for your suggestion. I was looking for a way to make the the InStr() function work for me when you posted your idea. I modified it for my database and it works perfectly. Thanks for you help.

 

[VilaRestal]

InStr would do exactly that without having to add your own function:

Function OpenSpecialForms(strCustomerNbr As String)
    If Nz(strCustomerNbr,"") = "" Then
        MsgBox "Please enter a customer number to continue"
    ElseIf InStr(strCustomerNbr, "HK") Then
        DoCmd.OpenForm "frmA"   'change this to your form name
    ElseIf InStr(strCustomerNbr, "1Z") Then
        DoCmd.OpenForm "frmB"    'change this to your form name
    Else
        MsgBox "Please enter a valid customer number to continue"
    End If
End Function