Link to home
Create AccountLog in
Avatar of peachymarie
peachymarieFlag for United States of America

asked on

Access 2010 form text input needs quotes to work

I have a form that I put a Search unbound text box and Command Button in.  It works but when entering the text - you need to add quotes around it because the text has a dash in it.  How can I "easily" fix it so the searcher does not have to enter the quotes?
I also would like to make it so the searcher does not have to enter the entire record field name to find it.
Full record field name:  Computer-WS23
Works with search as:  "Computer-WS-23"
Would also like the search to work with just:  WS-23

Current Code:
Private Sub cmdFind_Click()
    If IsNull(TextHere) = False Then
    Me.Recordset.FindFirst "[ComputerName]=" & TextHere
    Me!TextHere = Null
        If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
        Me!TextHere = Null
        End If
    End If
End Sub
Avatar of als315
als315
Flag of Russian Federation image

Change your string from:
Me.Recordset.FindFirst "[ComputerName]=" & TextHere
To
Me.Recordset.FindFirst "[ComputerName]=" & "'*" & Me.TextHere &"*'"
You can also check to see if the user has included the word "computer" like this:

If Len(YourText) > 8 Then
  If LCase(Left(YourText, 8)) <> "computer" Then
    YourText = YourText & "Computer-" & YourText
  End If
End If
 
"It works but when entering the text - you need to add quotes around it because the text has a dash in it"
Why?  Why do you need quotes just because there is a dash ?

mx
Avatar of peachymarie

ASKER

RE:  als315 -   I replaced the text mentioned above with the: Me.Recordset.FindFirst "[ComputerName]=’” &”*” & Me.TextHere &”*”’  and but keep getting the “No record found” popup, even if I enter text for data that I know is there.

RE: LSMConsulting - I added your portion separately from above change and got missing syntax error.  Maybe you need to tell me where exactly to place it.  I am not a scripter by any means.

Private Sub cmdFind_Click()
    If Len(TextHere) > 8 Then
    If LCase(Left(TextHere, 8)) <> "computer" Then
               TextHere = TextHere & "IGSBDLEWWS-" & TextHere
      End If
  End If
    If IsNull(TextHere) = False Then
    Me.Recordset.FindFirst "[ComputerName]=" & TextHere
    Me!TextHere = Null
        If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
        Me!TextHere = Null
        End If
    End If
End Sub

Also, the word Computer was a generic for our AD computer name and there can be many possible prefixes, like:  ComputLWS-, ComputLLT-, ComputLGS-, ComputLFS-, ComputLTB-, ComputGWS-
ComputGLT-, ComputGGS-, ComputGTB-, ComputEWS-, ComputELT-, ComputEGS-, ComputETB-

But if I were to enter LT-027, I would like it to when I click enter, it would bring up the first record to match that, click enter again and it would go to the next one that matches that, etc.


RE: DatabaseMX  It needs the quotes to work because if you do not put the quotes around the text in the Search box, it give an error:  For entering in the search box:   COMPUTER-027
Run-time eror ‘3070’
The Microsoft Access database engine does not recognize ‘COMPUTER’
as a valid field name or expression.
It's like it hits the dash and stops.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
It is text search, change also = to Like
Me.Recordset.FindFirst "[ComputerName] Like '*" & Me.TextHere &"*'"
<It is text search, change also = to Like>

Yes, exactly as I indicated in my post 6 hours earlier.