Results 1 to 5 of 5
  1. #1
    brycebba is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    3

    Enter search text into Combo Box and return results from Query into same Combo Box


    Here is my situation:

    I want to have an "instant search" in a form that I have. I have a working model of this that is the setup shown on this <a href=""http://www.access-programmers.co.uk/forums/showthread.php?t=188663"">site</a>

    The problem is that I want to make it better, what that means is that I want to be able to enter my search text into my combo box and have it run my query on change and produce my results in the combo box dropdown. I am not tied to this idea but I would like to see if its possible.

    My table contains two columns that I want values from in my results.

    Table Name = Controls
    Column 1 = Field_Name
    Column 2 = Template_Name

    qryControls =
    SELECT Controls.Template_Name, Controls.Field_Name
    FROM Controls
    WHERE (((Controls.Field_Name) Like "*" & [Forms]![Dummy Form]![txtSearch] & "*"))
    ORDER BY Controls.Field_Name;

    Is there any way to do this? I have tried countless efforts and cannot get it to work properly.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you post a sample database (just take out any sensitive information and leave dummy data in it) What you want to do is possible but hard to direct you without more information.

  3. #3
    brycebba is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    3
    So here is what I am ultimately trying to do.

    1. txtTemplate_Name in the form header should be a constant value even when going to a new record unless the btnClear button is clicked (I havent worked on this yet)

    2. txtSearchFor is the field where I key in text and it matches to a value in cmbResults which pulls data into the list from the qryControls Query. I would like to make this one combo box where the results populate into the combo dropdown after keying in text that has a partial or full match like googles instant search.

    3. Once a record is select that is found in the query I would like to be able to copy that rows data and save it with the different Template Name that is in the txtTemplate_Name field. (This I havent worked on but would love some pointers). I wouldnt be opposed to that selected record opening in a popup form or subform that I could create and then copy from the values from that and populate them back into my "Test" form so that a user could make any minor adjustments needed.

    Any help would be great!!! Thanks!

    Bryce

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok, first, you have a CONTROLS table, but you are repeating the control name over and over which is an invitation to disaster, particularly if someone misspells the name. What you should have is another table, say TEMPLATES, where you have a primary TID and the TEMPLATENAME, so for instance

    Code:
    TID TEMPLATENAME
    1   BryceTest1
    2   BryceTest2
    Then store the TID of the CONTROLNAME in your CONTROLS table.

    I'm enclosing an example of what I'm talking about, it's deleting the field name and I haven't tracked it down but this is the basic design I think you want.

  5. #5
    brycebba is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    3
    That is looking quite a bit better. I have made some improvements also but I still cant find why the field is getting cleared. I would love to have the search run in combo BUT I am not married to the idea if it is overly complicated. I basically just want to get a running model and I can tweak it here and there later on.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-05-2011, 01:24 PM
  2. Replies: 5
    Last Post: 07-04-2011, 12:53 PM
  3. Replies: 10
    Last Post: 07-02-2011, 11:51 AM
  4. Enter parameter value (subform combo)
    By AndycompanyZ in forum Queries
    Replies: 11
    Last Post: 06-22-2011, 08:33 AM
  5. Replies: 1
    Last Post: 03-09-2011, 02:04 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