Results 1 to 10 of 10
  1. #1
    jakeao is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16

    Search field is not working correctly

    I'm try to setup a very basic part look up at work. I have a field on a form that has part numbers called "National_Stock_Number" That I want to search through. When I set it all up and run the search I get this:




    run time error '3070' The Microsoft Access database engine does not recognize HMCP100R3C as a valid field name or expression.

    If I change the search to use the ID number to find things it works fine.

    Here is the coding for it all.

    Private Sub Search_Click()
    If IsNull(Text42) = False Then
    Me.Recordset.FindFirst "[National_Stock_Number]=" & Text42
    Me!Text42 = Null
    If Me.Recordset.NoMatch Then
    MsgBox "Error please try again.", vbOKOnly + vbInformation, "Error."
    Me!Text42 = Null
    End If
    End If
    End Sub

    Any Ideas would be great. Or if someome could point me in the right direction for a super easy tutorial on it, that would also be helpful.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You are searching for text so you need to surround the search value with quotes:
    Me.Recordset.FindFirst "[National_Stock_Number]='" & Me.Text42 & "'"

  3. #3
    jakeao is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16
    Thank you, thank you, thank you

    I was ripping my hair out lastnight trying to figure it out. I guess that's waht I get for coding when I'm falling a sleep. Thanks again from a very grateful newb.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're welcome. Do you know that you have Me!Text42 = Null in there twice?

  5. #5
    jakeao is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16
    Thanks I didn't even notince that. One more quesiton. How do I get it to ignore special characters. Lets say a person puts in a space, or a dash when they don't need one. How do I get it to only react to letters and numbers?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You would need to edit the input as it is entered in the KeyDown or KeyPress event.

  7. #7
    jakeao is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16
    I'm sorry but I'm very new to this VB stuff. What exactly do the KeyDown, and KeyPress events do? Let me try to clarify what I'm looking for.

    Lets say I have a part number that is "ASR134-7R" and a person types in
    "ASR134 7R" because they cant read the little dash on the part. I want the search to ignore any special charaters or spaces that get typed in, and only search using the alpha numeric information in the part number. I'm also trying to figure out a way to search for like items. I have a "keyword" field for things like conduit. That way I could look up every part for 1/2 inch conduit. I hope that this makes sense. Thanks again for all the help.

    On a related note, I figured out how to get me search box to search multiple fields all on my own!!!!

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What you are asking requires modifying what the user keys in *and* modifying what you see in the field so you can get a match. I'm not sure you want to go there.

  9. #9
    jakeao is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16
    Yeah, I tried some things, and talked to some people. It does'nt seem to be much of an issue, so i'll skip it. Thanks again for the help.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Good. Feel free to start a new thread any time you have an issue that you would like help with.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Why isn't this working?
    By adiecidue in forum Queries
    Replies: 4
    Last Post: 04-27-2009, 10:29 AM
  2. Search any field and part thereof
    By Johan in forum Programming
    Replies: 0
    Last Post: 09-08-2008, 02:18 AM
  3. Search any field and part thereof
    By Johan in forum Forms
    Replies: 0
    Last Post: 09-03-2008, 08:01 AM
  4. Search All Field
    By robbiebrown34 in forum Access
    Replies: 0
    Last Post: 05-17-2007, 09:40 AM
  5. Can't display fields correctly....
    By benjamin in forum Database Design
    Replies: 0
    Last Post: 05-17-2006, 03:43 PM

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