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

    Parameter query search causes search form automatically fills form out

    I have a multi-query parameter query that has the following criteria for each field:



    LIKE "*" & [Forms]![Voter Lookup Field]![First Name] & "*"
    LIKE "*" & [Forms]![Voter Lookup Field]![Last Name] & "*"
    LIKE "*" & [Forms]![Voter Lookup Field]![City] & "*"

    I have a look up form that i have text fields where I can enter the names and city i'm looking for with a button that runs the search. When I open the form up to run the search it already has the field populated. I don't know where it is finding these names to populate and how to stop it. Any ideas?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show readers the sql of you query. Or post a zip copy of your database.
    Need more info.

  3. #3
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32
    Below is the SQL:

    SELECT Voters.[First Name], Voters.[Last Name]
    FROM Voters
    WHERE (((Voters.[First Name]) Like "*" & Forms![Voter Lookup Form]![First Name] & "*") And ((Voters.[Last Name]) Like "*" & Forms![Voter Lookup Form]![Last Name] & "*"));

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In plain English WHAT do you want to retrieve/find? No forms, no queries, no jargon---simple, plain English.

  5. #5
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32
    I have a table of all the voters in the district. I want to have a search form where i can type in the fields i'm looking for. Sometimes i'll search by first and last name. Other times i'll search by just last name or some other combination of fields such as street or city, etc. Once I have run the search I'm going to put these voters into a report. Don't have a problem with the report, just a problem with the query and form.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I would consider using a form with subform. In the form header, depending on your search needs, at least 1 text box(and possibly several), and a combobox for Districts.
    When you/the user enters something in the text box(es) and/or select a district from the combo, you modify a query behind the scenes that acts as the record source of a report.

    For testing you could have it display
    1: the sql of the resulting query
    2: the sample data destined for the report.

    Updated:

    I created a sample data base with mock data -- 200 voters and with various names and cities etc spread across 6 districts.
    It is a form and subform, not a report, but shows the general approach I was mentioning.

    I hope it is useful to you.
    Attached Files Attached Files
    Last edited by orange; 05-04-2016 at 07:42 PM.

  7. #7
    RobOtowski is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    May 2014
    Posts
    16
    Orange, I posted a thanks in the other thread where you directed me here, but I wanted to post here as well. Your form is simple to use and the code is pretty easy to understand. I appreciate the work you put into it and for sharing it with those of us just learning.

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

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Update to voters database.
    I added 3 records to the Voters table (sep 7 2016)
    There are 2 copies of the database in the zip.
    --the original with 200 voter records
    --a new database with 203 records.

    Adjustments in response to a PM.
    Attached Files Attached Files

  10. #10
    N7925Y is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2016
    Posts
    32

    Search issues

    Zip File for Jennings Voter Database.zip

    I believe I was able to attach the file. Not the easiest thing to do. Couldn't find the "attach" button. Turns out you have to go to advanced settings.

    But in this file, you will se the last two contact in the voter file (tommy Rogers and James Jennings) don't come up when you run the search.

    Thanks for all your help.

    J.B.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have looked at your database. I do not understand why your last 2 records do not get found via search.
    I notice you have another table and other forms and that you are using macros.

    Your voters table is not normalized. You have several subjects in this 1 table. Your Category 1,2 and 3 should be a separate table.
    I don't understand why the autonumber PK values for field ID for these records is 3831021 and 3831022. The original 200 records are 1 -200.

    I'm not sure why, when opening the search form on start up it shows only the record with ID 200??
    Sometimes it opens with only the first record Angela Hortert???

    I deleted the ID field, then recreated it, autonumber, PK so now Tommy Rogers is ID 201, and James Jennings 202

    The records are definitely in the voters table. But they are not being found via search???

    I don't use macros so can not debug them. I don't know what else, if anything, you may have done (even unknowingly) to cause the type of activity. It could be some corruption; could be some properties or code interfering with something???

    I type "jam" in first name and can see a count of 4, but there are no records displayed???
    If I leave all criteria blank and click search, I get the first record only and a count of 200.

    Seems to be same code as in the sample database I submitted previously --it works, even with added records as discussed in the PM -- so I don't know what may lurk beneath the surface of your database.

    I just tried taking the SQL (recordsource)
    and modified it to
    Code:
    SELECT *
    FROM Voters
    WHERE VoterFirstname Like 'james*' 
    ORDER BY VoterLastname;
    I get 2 records, one of which is James Jennings

    If I run a query
    Code:
    SELECT *
    FROM Voters
    WHERE VoterFirstname Like 'jam*' 
    ORDER BY VoterLastname;
    I get 5 records, including James Jennings.

    I found an issue with the combo . The records for Jennings and Rogers did not have Districts . I entered MadeUp for them.

    However, when I code specific query
    Code:
    SELECT * from Voters where VoterFirstname Like '*' AND VoterLastname  Like '*' AND VoterDistrict Like '*' ORDER By VoterLastname;
    I can see all records???

    Soomething is interfering with what is being displayed/ I don't know what. The count is correct but the displayed records???

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

Similar Threads

  1. Replies: 1
    Last Post: 05-04-2016, 02:34 PM
  2. Replies: 3
    Last Post: 03-18-2016, 05:02 PM
  3. Replies: 1
    Last Post: 05-06-2015, 01:23 PM
  4. Replies: 5
    Last Post: 02-10-2014, 04:46 PM
  5. Replies: 1
    Last Post: 04-20-2012, 03:16 AM

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