Results 1 to 6 of 6
  1. #1
    Ally1205 is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jul 2013
    Posts
    68

    Search tool per "Does anyone know these search controls I remember from the 1990's?"


    Attached, is the search device I was inquiring about in my thread "Does anyone know these search controls I remember from the 1990's?"

    There is a lot of redundant code in the form, which you can ignore.

    Many thanks

    Ally1205
    Attached Files Attached Files

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    1. The way you are defining your primary key is extremely bad. A primary key should NEVER be reliant on data entry. What happens if one of these people moves to a new location, does their pk field change? if it doesn't the PK will no longer match your described setup. You would be far better served by having an autonumber PK, you can still retain an 'internal' identifier but the way you're doing it is extremely risky, particularly if there are more tables involved in your database than this one table.
    2. You are attempting to do a search based on a field that is bound to your data source, another extremely bad and risky thing to do.

    I've modified your form with a simple search function, currently it will find any record that STARTS with whatever you type in that same box when you hit the ENTER or TAB key (on exit property) of the field. So if you type in AB, you'll get both records, if you type in ABB or ABR you'll get one record

    if you want it to type in, say B and find both these records you'll want to change the criteria in the query now driving the form to

    like * & forms![formname]![searchfieldname] & *

    where formname and searchfieldname are your form and field names.

    TESTER-SEARCHBOX-Modified.zip

  3. #3
    Ally1205 is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jul 2013
    Posts
    68
    rpeare, Thank you for the suggested modification. Before I saw your post, I adapted the original search device I posted, to suit my current application, and now have it searching on the LastName field. The search device works exactly like the one you posted.

    What I'd really like for the new application is to be able to search any of 3 fields: FirstName, LastName, and Address1. So there would be three search-sting input boxes. Alternatively, a single string-input box tht searched three fieds, and a dropdown list that would show all records containing the typed search string - whereupon, clicking on that record in the dropdown list would cause that record to appear as the current record in the form.
    Regards,
    Ally1205
    Last edited by Ally1205; 12-11-2013 at 06:06 PM. Reason: accessforump05

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    put the same search criteria in all of them

    like * & forms![formname]![searchfieldname] & *

    it will search any of the three fields for a value matching whatever's typed in the text box.

    make sure the critera for the additional fields are in OR rows, not the AND row (in other words each criteria should appear in a different row in the criteria section)

  5. #5
    Ally1205 is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jul 2013
    Posts
    68
    rpeare,
    Understood, and crystal clear. Thank you! I'm looking forward to trying this out within the next day or two.
    Regards,
    Ally1205

  6. #6
    Ally1205 is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Jul 2013
    Posts
    68
    What would work best for me is to have 4 search fields on my main form: FirstName, LastName, City and State. A command button runs the query that has the same 4 filelds. In the Criteria row for each field in the query, respectively, I have, I have:
    Like "*" & [Forms]![MainF]![FirstName] & "*"
    Like "*" & [Forms]![MainF]![LastName] & "*"
    Like "*" & [Forms]![MainF]![City] & "*"
    Like "*" & [Forms]![MainF]![State] & "*"

    This works great, because I can search for say, a Joe from Miami or a Mary From NY, etc.
    I have placed the four search textboxes in a corner of my main form, with a rectangle around them to separate them from the other textboxes on the form, which display the fields from the current record. When I enter text into the search textboxes and hit the RunQuery button, I want the query results to appear in a sub-form in the bottom half of the form. I then want to be able to click on any of those search results showing in the subform, whereupon the selected record becomes the current record showing in the top half of the form).

    I have set up the form with a subform to display the query results. When I open the form, my subform shows all the records, because the search boxes are empty. This where I need help: If I enter text into a search box and run the query, the data in the subform remains unchanged - (unless I switch the form to Design View and then back to Form View, whereupon the subform shows the search results). How do I correct this, so that the sub form shows the search results as soon as I hit the Run Query button - and how do I code it so that when I click on a record in the subform, that record immediately becomes the current record in the top half of the form?

    Many thanks
    Ally1205
    Last edited by Ally1205; 12-14-2013 at 04:54 PM.

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

Similar Threads

  1. Replies: 34
    Last Post: 12-13-2013, 04:35 PM
  2. Replies: 2
    Last Post: 11-12-2013, 07:06 PM
  3. Creating a "search" field in the header
    By MissLotus in forum Forms
    Replies: 9
    Last Post: 07-24-2013, 03:52 AM
  4. Replies: 11
    Last Post: 06-18-2013, 07:48 AM
  5. Replies: 8
    Last Post: 03-05-2013, 01:20 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