Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41

    Search By Status or other criteria

    Hi,



    I got a solution for a combo box search button the other day but this is not quite giving me the result I would like.

    If I open my clients form, I would like to have a search button that will open a pop up form allowing me to search the whole recordset (clients) but only by one field (for example "status"). I would like the pop up form to show the results in a list type format, and when I click on the result in the list that I am searching for, that this record be displayed in the main form which would still be open in the background.

    The problem I am having at the moment with a basic search is firstly that I can only see one result at a time, and also that if I search for all records showing "hot" then I get all of the clents with hotmail e-mail addresses as well.

    I hope this makes sense! The only problem is that I am a bit of a novice with this whole access lark and although I persevere until I get it right, in this case I don't know where to start!

    Can anyone point me on the right path?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Brilliant. I now have a search form and know how to do them. Thanks!

    How do I now display the results in a form instead of a table? I would like the pop up form to show the results in a list type format, and when I click on the result in the list that I am searching for, that this record be displayed in the main form which would still be open in the background.

  4. #4
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    ok, I have now dragged the form that I use to enter records across to the search from and it is now my subform. It looks exactly how I want it.

    The only thing I am missing is the code to show the query results in this subform. It should be simple but I can't seem to find it anywhere.

    This format means that my subform is bound to the clients table.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you'll find a solution at Martin Green's site. It may not be the exact format you're asking for, but Martin has so many worked out examples that you will find something, and learn a lot.
    http://www.fontstuff.com/access/index.htm

  6. #6
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Thanks. There are lots of useful bits on that site but I cannot find anything that helps with this particular problem.

    I have attached the database to give an idea of the problem. It is in the Client Search Form.

    Client DatabaseWEB1.zip

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I can not open your zipped file. It is .rar format
    Also, I have acc2003 and can not open an accdb database.

  8. #8
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Thanks for trying orange but I cannot save iin mdb format. A message pops up about new features blah blah and I do not have the option to ignore it.

    I will try uploading it anyway in zip just in case anyone knows a way.
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    For Client Search Form, form/subform not needed. Build form with the search boxes in the form's Header section and have the query be the RecordSource for the form. Put controls (textboxes, comboboxes) bound to fields of the RecordSource in the Detail section.

    Do you really want users to open Find/Replace dialog to do search for Vendors?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Hi June,

    Thanks. No I really don't want them to do a find/replace dialog to search for Vendors. Once I have a format that I can use to build a search by criteria form then I will also build this form for vendors.

    Will the method that you have suggested also allow the editing of the data in the same view? The form that I currently have was originally made for the input, editing and viewing of data. Essentially I am trying to do the same thing but add a search by criteria function to this form or rebuild the form from new in a similar with this function built in.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, will be able to edit the records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Quote Originally Posted by June7 View Post
    For Client Search Form, form/subform not needed. Build form with the search boxes in the form's Header section and have the query be the RecordSource for the form. Put controls (textboxes, comboboxes) bound to fields of the RecordSource in the Detail section.

    Do you really want users to open Find/Replace dialog to do search for Vendors?
    Hi June,

    I just cannot get this to work unfortunately. I have made a new form with the buttons in the header as you said, changed the control source of the form to be the query and then making the control source of the textboxes either [Tables]![ClientName] (or similar) and also to [ClientSearchQuery]![Client] as per the builders, but all I get is a #name error and the query still opens in another tab as a table.

    Could I trouble you to talk me through this a bit more step by step?

  13. #13
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    Try the code below. Persoanlly, I use text boxes and set the code to kick off under After Update, but you can add a button that will initialize. The '*' around the Text Box name will allow for broader seraches or searches where users might not know the whole name.

    Code:
        Me.FilterOn = False
        Me.Filter = "[YourFieldNameHere] Like '*' &[Forms]![YourFormNameHere]![YourTextBoxNameHere] & '*'"
        Me.FilterOn = True
        Me.Requery
    I also put a "Clear Filters" button with the code below on my forms

    Code:
        Me.TextBox1 = Null
        Me.TextBox2 = Null
        Me.FilterOn = False

    Hope that helps.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The textbox ControlSource should just be the field of the form RecordSource, like: Client.

    If the query is opening then there is code causing that. This other tutorial is in line with my suggested changes and may be closer to what you want http://datapigtechnologies.com/flash...tomfilter.html. Post your query and code for analysis.

    Setting the Filter and FilterOn properties as suggested by TG is an alternative to setting parameters in the query but the WHERE statement can get rather lengthy if you offer a lot of comboboxes for users to filter with. Using the AfterUpdate event is fine if only one criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    timbo is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    41
    Thanks. I will have a play around with this tomorrow and hopefully get it working!

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

Similar Threads

  1. Search form criteria
    By atom in forum Forms
    Replies: 3
    Last Post: 04-03-2012, 06:42 AM
  2. search criteria help
    By putadokta in forum Access
    Replies: 2
    Last Post: 01-27-2012, 10:45 PM
  3. Search Criteria Error
    By hitesh_asrani_j in forum Forms
    Replies: 25
    Last Post: 09-29-2011, 10:35 AM
  4. Search Criteria
    By Meccer in forum Forms
    Replies: 1
    Last Post: 04-01-2011, 12:53 PM
  5. Search By Criteria - Flexible Criteria Fields
    By lilanngel in forum Access
    Replies: 0
    Last Post: 03-16-2011, 06:25 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