Results 1 to 8 of 8
  1. #1
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32

    Tring to build a Multi-Table Search Form

    I have a database with my contact names in 1 table and the addresses in another. I want to have a search form where I can search on name or address. Trying to do this via a query and having some issues. The main issue is when I open the search form I get a dialog box asking for the "first name" when I close that box another opens asking for "last name" and again it does it on the city. Once I close each of these boxes the actual search form opens where i can type in the contacts I'm trying to search.

    I'd prefer to do this via VBA code but not to strong on writing a SQL search. Any thoughts?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Having the dialog boxes pop up indicates there are controls where the control source is not found: "first name", "last name". (BTW, shouldn't use spaces in object names)
    All of the controls for searching should be unbound.

    VBA code would be like the example at Allen Browne's site:
    Search Criteria article: http://www.allenbrowne.com/ser-62.html and
    Listing of the code: http://www.allenbrowne.com/ser-62code.html

  3. #3
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32
    I mentioned VBA but i guess the better question is what is the best way to do this?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I used Allen Browne's code, but modified it to suit my requirements.
    There is the Filter button in the ribbon or the search box in the status bar.

    I used the code because I want to control everything........

  5. #5
    warmslime is offline Advanced Beginner
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    49
    Quote Originally Posted by N7925Y View Post
    I mentioned VBA but i guess the better question is what is the best way to do this?
    Like ssanfu said your problem is most likey that you've made a bound form. Make all the controls unbound. And in the query as where clauses add something like [forms]![form1]!controlname.value and it'll work without prompts.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Just to be sure that you're not making this more difficult than it needs to be...are there multiple addresses for a given name...or multiple names for a given address...or both?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32
    Each contact can have multiple addresses:

    Home
    Work
    Vacation Home
    P.O. Box

    and each mailing address can have multiple contacts such as family memebers.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you should have a junction table if that's the case

    one table for contacts
    one table for addresses
    one table linking contacts to addresses if you do that then it's pretty simple to build a query with the criteria you want otherwise you will likely have to build a custom SQL string every time you run your search especially if it's a mix of name and address information.

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

Similar Threads

  1. Help With Multi Field Search Form
    By icaines9517 in forum Forms
    Replies: 6
    Last Post: 08-02-2016, 10:01 AM
  2. Multi-field search form
    By rebeldolphin69 in forum Forms
    Replies: 2
    Last Post: 06-15-2016, 05:28 AM
  3. Multi Use Search Form
    By CharlesWilliams in forum Forms
    Replies: 4
    Last Post: 03-13-2015, 01:09 PM
  4. How to Search Fields from Multi-Tables Form?
    By Yeisha2008 in forum Queries
    Replies: 9
    Last Post: 07-19-2012, 11:41 AM
  5. Multi Field Combo search Form
    By Andyjones in forum Access
    Replies: 3
    Last Post: 03-12-2012, 02:13 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