Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51

    Search for specific words in textbox

    Greetings,

    I have a textbox where the user is required to enter either one of two words after placing a check in a checkbox. Those two words are Blue or Brown.

    When the user clicks the Save command button, it validates if there is a check in the checkbox and if either one of the two words are in the textbox. I can figure out how to check for one of two required words. Below is the code I've tried. No matter what is entered in the textbox (blue, brown or anything else), the message box appears.

    If ((Me.chkBox.Value = -1) And (InStr(1, (Me.txtBox.Text), "blue") = 0)) Or ((Me.chkBox.Value = -1) And (InStr(1, (Me.txtBox.Text), "brown") = 0)) Then
    MsgBox "Make sure to include box color in notes (Blue or Brown).", vbExclamation, "Blue/Brown Box"


    Else
    Do something
    EndIf

    Any suggestions is appreciated.
    Cheers

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please try:
    Code:
    If Me.chkBox = -1 Then    
        If Instr(Me.txtbox,"blue")=0 And Instr(Me.txtbox,"brown")=0 Then
            MsgBox "Make sure to include box color in notes (Blue or Brown).", vbExclamation, "Blue/Brown Box"
        End IF
    End If
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Click image for larger version. 

Name:	Error.jpg 
Views:	15 
Size:	13.2 KB 
ID:	49443Greetings Gicu,

    Thank you for your reply. I tried your suggestion and received the following alert.

    Last edited by Macallan60; 01-06-2023 at 10:01 AM. Reason: Add pic

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Why not use a combo box which would allow you to easily restrict what is entered rather than use a textbox.
    Do you even need the checkbox? You could just test for a null value in the combo.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Does it highlight any line? Can you show the entire procedure please?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    That was my mistake. I did not copy your code correctly.

    I re-tried your code. When all the criteria are met (checkbox is checked and "blue" is in the textbox), it doesn't want to proceed to the commands after Else. No errors given, it just stops.

    If Me.chkBox = -1 Then
    If InStr(Me.txtBox, "blue") = 0 And InStr(Me.txtBox, "brown") = 0 Then
    MsgBox "Make sure to include box color in notes (Blue or Brown).", vbExclamation, "Blue/Brown Box"
    End
    End If
    Else
    Do something
    End If

  7. #7
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thanks for your suggestion Bob. That is my backup plan. I'm holding off on this because it will take a lot of work and re-training the staff.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Macallan60 View Post
    Thanks for your suggestion Bob. That is my backup plan. I'm holding off on this because it will take a lot of work and re-training the staff.
    I can't see changing one textbox to a combo box as "a lot of work". Nor can I see any retraining for users would amount to much. If the combo drops down when it gets the focus and there are only two options to choose from what training is actually required. Anyway, it's your project so its your choice.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by Bob Fitz View Post
    ... Anyway, it's your project so its your choice.
    Often the choice is out of the hands of a developer. Those with the gold make the rules.

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry but you have it wrong again, try to use the code tags when posting code to preserve the indenting (which helps you visualize the errors in the If blocks):
    Code:
    'If Me.chkBox = -1 Then
    'If InStr(Me.txtBox, "blue") = 0 And InStr(Me.txtBox, "brown") = 0 Then
    'MsgBox "Make sure to include box color in notes (Blue or Brown).", vbExclamation, "Blue/Brown Box"
    'End 'Miising IF
    'End If 'you do not use this if you want an ELSE
    'Else
    'Do something
    'End If
    
    
    'two versions:
    '1: no Else just let the program flow after the If statement
    If Me.chkBox = -1 Then
    	If InStr(Me.txtBox, "blue") = 0 And InStr(Me.txtBox, "brown") = 0 Then
    		MsgBox "Make sure to include box color in notes (Blue or Brown).", vbExclamation, "Blue/Brown Box"
    	End If
    End If
    
    
    Do something
    End If
    
    
    '2: If statement with Else
    If Me.chkBox = -1 Then
    	If InStr(Me.txtBox, "blue") = 0 And InStr(Me.txtBox, "brown") = 0 Then
    		MsgBox "Make sure to include box color in notes (Blue or Brown).", vbExclamation, "Blue/Brown Box"
    	End If
    Else
    	'Do something
    End If
    
    
    'Do some more
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Quote Originally Posted by Bob Fitz View Post
    I can't see changing one textbox to a combo box as "a lot of work". Nor can I see any retraining for users would amount to much. If the combo drops down when it gets the focus and there are only two options to choose from what training is actually required. Anyway, it's your project so its your choice.
    Hi Bob, there is a lot of work on a policy end with my company. The Access end shouldn't take that long. But per company policy I will need to do the following:
    1. Submit a test version to our technology committee for approval. The approval usually takes a couple weeks.
    2. Once approved by the technology committee, I need to send a updated version of our Standard Operating Procedures to our operations committee for approval. This takes another couple weeks.
    3. Then I need to hold a virtual meeting with all users (about 40) to explain the new process.
    4. Then I need to get signed verification that they all understand why we needed the change and how to use it.
    5. Then I can set this up in a production environment.

    All of this for one combo box. Crazy huh? It's nuts that they make us go through this! But that's how my company wants things done. So I'm hoping to avoid all that by using Gicu's suggestion.
    I also volunteer my time on user forums for our products. And the one thing I learned is never assume until you've walked a mile in the other person's shoes. What you may think is not a lot of work can be very different for someone else.

  12. #12
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    .accdb to show code

    Quickly modified a .accdb from another thread to do what I think you want.
    The code from Vlad is included with a minor change.
    The form isn't pretty, derived from a form wizard to keep things standard and simple.
    The else clause seems to run.
    There is a validation left over from a prior project in MyTable1, MyText. Of course, you could modify that for more of what you want.
    Then at save time, it's just a matter of validating again of you wish. But realize, Access won't run your validation if there weren't any changes to the record.
    That's one of those little Gotchas in Access. You'll have to create your own save button and validate in Form_Beforeupdate to handle old data that isn't blue or brown.
    Note: with Gicu's code you can enter "Blues" or "browner". I'm not sure if you wanted that based on his understanding and your description of the problem.
    Attached Files Attached Files
    Last edited by twgonder; 01-06-2023 at 12:35 PM. Reason: fix

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Good point about the "Blues" or "browner" luckily the solution is simple enough:

    Code:
    If InStr(" " & Trim(Me.txtBox) & " ", " blue ") = 0 And InStr(" " & Trim(Me.txtBox) & " ", " brown ") = 0 Then
    EDITED:
    I think you also need to check for when the two words are at the beginning or end of the textbox text. I would use the updated line above.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Assuming the OP only wanted "blue" or "brown" then I would change the line to this:
    Code:
        If MyText <> "blue" And MyText <> "brown" Then
    In my example .accdb anyways.

    From the OP: "...where the user is required to enter either one of two words..."


  15. #15
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    True, but we don't know if anything else is to be entered in there, because the message box mentions "notes" which would imply more is expected not just "box color".

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 07-07-2017, 05:11 PM
  2. Replies: 3
    Last Post: 04-04-2016, 09:21 AM
  3. Replies: 9
    Last Post: 10-01-2013, 08:48 AM
  4. Bold only certain words in textbox?
    By NateHaze in forum Reports
    Replies: 1
    Last Post: 06-04-2011, 11:47 AM
  5. Textbox, remove certain words.
    By dgrzalja in forum Forms
    Replies: 0
    Last Post: 11-03-2009, 09:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums