Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Search in access

    Hi All,

    I posted a similar question before, but no answer seemed to work.

    I have an access table called "claims tracking" and a form called "claims tracking" and I enter and store records in the form. I have a text box named "social security number" which I use to enter claim socials.

    Well, I am currently usinig find and replace to find records by SSN in my databse. It's slow and time consuming.

    Is there any way, someone can give me step by step information and code, on now to program and create a "search box or button that will allow me to search only by the social security number text box. I want to be able to enter a social and the record be displayed immediately that corresponds with the social i entered instead of using find and replace that takes forever.

    Thanks!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Sounds like you're going to need some SQL; for a step by step I would suggest starting with Martin Green's site. Else check out this Code Bank Post for a search screen example.
    Me.Geek = True

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If I recall your last post on this, you need to simply create an unbound text box on the form and set the criteria of the recordsource query of your form equal to the unbound text box value after it is updated.

    1. Create an unbound text box on your form (ie. call it FSS). Make sure it is formatted the same way as your data in your SS field (ie. if it has dashes then format it as 000-00-0000.) If no dashes, then format it as 000000000 (or no format). Make this box a yellow or other background (so the user knows it is a search field.)
    2. In the afterupdate event of this FSS field, add a line of code such as:
    if not isnull(me!FSS) then
    me.recordsource = "Select * from MyTable where MySSField = '" & Forms!MyFormName!FSS & "'"
    end if

    This is assuming the MySSField in your table is a TEXT data field type (which it probably should be.) I think your problem before though (if I recall) stemmed from you don't have the unbound text box field formatted the same as what is stored as data in that field (ie. you can't do a search on 386032221 if the data in the field is actually stored as 386-03-2221.) You must format the unbound text field as 000-00-0000 so it would be the same as what the values are in the field.

    And "Please" rename your "Social Security Number" to something without spaces (ie. FSS)! Otherwise your statement should look like this...
    me.recordsource = "Select * from MyTable where MySSField = '" & Forms!MyFormName![Social Security Number] & "'"

    Number 1 rule (that I personally like to go by) - Don't use spaces or non-alpha/numeric characters in any of your field names, text box names, table names, form names, etc...etc..etc...etc...(save yourself some headaches!)
    Last edited by pkstormy; 11-08-08 at 01:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Search2

    Nick -

    I wanted to show you something I do that's similar to your Search2. I used your search2 and put in the code I use to show you and attached it.

    It's a little different than your method but I thought you might want to see it. I modified it quickly so bare with any errors. It's pretty flexible if you want to do searches on new fields of data.
    Attached Files Attached Files
    Last edited by pkstormy; 11-08-08 at 05:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    It probably goes without saying, but you should also ensure that your SSN field is set to indexed w/o duplicates.

    -Dean

  6. #6
    Join Date
    Oct 2007
    Posts
    214
    Thanks all. After some troubleshooting, this works well.

    If a record is not found, is there any way to program a message box to simply state "SSN not found"

    Also, can this search be done by clicking a command button, and then a prompt appears prompting the user to enter the social?

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •