Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2004
    Posts
    10

    Unanswered: Combobox in query criteria??

    I am very newbie with access (learnign as I go)....I recently learned how to make a query ask the user for input. By putting [Text you want shown] in the criteria box of the query in the colum you want to search by.

    Thats neat and works great but leads me to wanting to make that a listbox or preferably a combobox instead of a text box. I have searched the net, and this wonderful site and have found a lot of information that I seem to be applying wrong.

    forms!yourForm!yourComboBox when placed in the criteria box dosent make a combobox it instead makes the same text box but gives forms!yourForm!yourComboBox as the prompt!

    I have a table that has 2 columns I want one of the columns to be the source of the choices on the combobox. I have also created a form that consists of only a combobox that could be used for this purpose...

    I just cant seem to get the combobox into the query criteria

    Please help!!

    Thanks in advance....
    Struggling newb

  2. #2
    Join Date
    Apr 2004
    Posts
    85
    Create a new ComboBox or right click your textbox and click change to ComboBox. thats a start

  3. #3
    Join Date
    Sep 2004
    Posts
    10
    Where?

    I am currently inside the query design view, trying to make/put a combo box in the criteria block. The only way to get a text box (that I know of) is to put some words in [] in the criteria block.

    I have no idea how to create a new combo box in the criteria block....

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    In the query the criteria would read [Forms]![namre of form you make that has the combo box]![name you gave the combox in it's properties]

    Please note if you search for "Create a form to enter report criteria" it list all the steps needed. (works for forms also.)

    Hope that's helpfull

    Darasen

  5. #5
    Join Date
    Sep 2004
    Posts
    10
    Thank you for your help but I must be missing something!

    I put [Forms]![formcombo1]![locationlistcombo](formcombo1 is my form name and locationlistcombo is the combobox) in the criteria box of the query under the location column.

    now when I run the query I get a pop up promt that has a text box and if I manualy type a location (what I am trying to get in the combobox) it will do a query for that location...above the text box is label text saying Forms]![formcombo1]![locationlistcombo

    obviously I am close but missing something probably with how I am entering the combobox code into the query?

    im running the search you mentioned now hopefully that will turn up something or you will be able to see where I am going wrong?

    Thanks again for you help

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by saw_Duuhst
    Where?

    I am currently inside the query design view, trying to make/put a combo box in the criteria block. The only way to get a text box (that I know of) is to put some words in [] in the criteria block.

    I have no idea how to create a new combo box in the criteria block....
    So you have your form with a combo box...What are you having trouble with? Getting the combo box to get the right information? So your table has two field (ex firstName, lastName) and you want the combo displays the last name. Open a new query, drag the fields down, and in the criteria space under lastName place Forms!(Your form name)!NameOfYourCombobox

    Close the query and save it as qryName or something. Now open your form in design mode, right click your combo box and click properties. on the data tab, in the record source field choose your query. And that should do it...
    Ryan
    My Blog

  7. #7
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by saw_Duuhst
    Thank you for your help but I must be missing something!

    I put [Forms]![formcombo1]![locationlistcombo](formcombo1 is my form name and locationlistcombo is the combobox) in the criteria box of the query under the location column.

    now when I run the query I get a pop up promt that has a text box and if I manualy type a location (what I am trying to get in the combobox) it will do a query for that location...above the text box is label text saying Forms]![formcombo1]![locationlistcombo
    Try and open the form not actual query...
    Ryan
    My Blog

  8. #8
    Join Date
    Sep 2004
    Posts
    10
    hmm the form was only created to hold the combobox which pulls its data from table (lastname if you will) it serves no funtion really other than holding the combobox.

    here is what I have....
    Table A - 2 colums, LocationID, Location Type
    Table B - which holds many columns including Location Type.
    Form 1 - which views all the data in table B
    Form 3 - which only has a combobox listing location type from Table A.

    I am trying to make a query that will "search" Table B and return only results that are of a certain location type.

    I now have the query created where, when I run the query it pops up a box that says enter location type and has a text box to type in the location. then it will open Table B showing only the locations that were typed into the prompt.

    I am trying to make it a combobox instead of a text enter prompt. I dont understand why I would be changing the form rather than the query?

  9. #9
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    OK The change needs to be to the form and to your macro. You have to have a macro.

    Create the form with the combo box and the desired bound column and tell the query to look for it. Sounds like you did that already.

    Create a new Macro.
    In the frist row name the macro "OK" .
    For Action select "open query"
    for the query name select your query
    for the view select datasheet and edit for the data mode.

    Save the Macro. Name it Formmacro (or whatever)

    Back on the form.

    Find the command button on the toolbox and make sure the Wizard is off.

    on the "Event" property tag for onClick From the drop down select the macro and macro name you want. In this case Fromamcro.ok

    Save and close the form.
    Launch the form (Has to be the form) and voila !

    WHY ??
    How is the Query supposed to know what the values are in the form?
    When you launch the form and select an item from the list THEN that value exist. The OK button and the macro tells Access to open the query and access sees the intended value.

    If you launch from the query the value does not yet exist SO the query puts up a text box.

    Make sense ?

    Hope that helps

    Darasen

  10. #10
    Join Date
    Aug 2004
    Location
    Steamboat Springs, CO
    Posts
    37
    I understand what you want to happen, but it just doesn't work that way. The prompt box that you have learned to initiate upon opening a query (as far as I know) cannot be initiated as a combo box. You will need to design your own prompt box, which will be a form, if you want it to contain a combo box.

    do as some of the previous posters have said and put Forms!(Your form name)!NameOfYourCombobox in the column of the query design for the item you are specifying in the combo box. Next add this line of code in the after update procedure of the combo box:

    stDocName = "qrynameLookup"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    This will open your query after you enter something in the combobox of your form, the query will now be generated base on what is in your combo box.

    This only piece that you will need to do is tie you combobox to some query or table to determine what the combo box "options" will be. Hope this helps.

  11. #11
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    The form with your combo box on must be open when you open your query.

    Think of it like this. Your combo box is simply a mechanism for choosing a value to put in a box on a form. You appear to have got the combo box to draw it's list of options from a table which is fine. The [Forms]![formcombo1]![locationlistcombo] is the way to reference the value in the box on the form. But the form must be open for it to be reference. As soon as you close the form the value in the box will be lost.

    If the above insn't the solution then I would think your reference is wrong. Forget the query for a moment and try putting a text box on the same form as your combo box. Then in the text box properties, put the following in the Control Source (ensure you include the = sign):
    =[Forms]![formcombo1]![locationlistcombo]

    Now when you make a selection in your combo box it should appear in the text box. This will prove to you that you're using the right expression to reference the combo box. Then you can have another stab at the query.

    HTH
    Chris

  12. #12
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I just read meremyb and Darensen's posts. Sorry, I was writing mine at the same time as theirs.

    If I understand your problem correctly, there's no need to use code or macros. Although they will enhance the useability though and worth doing once you've got your query working.

    Just keep your form open with your selection then navigate to your query and run it.

    Chris

  13. #13
    Join Date
    Sep 2004
    Posts
    10
    thank you all. no wonder I cant make it work! it dosent work that way lol. back to the drawing board will work with what you guys have provided I can almost picture it in my head so hopefully I can make it work.

    If not TTYL

    thanks again everyone.

  14. #14
    Join Date
    Sep 2004
    Posts
    10
    WOOOOHOOO got it working thanks to all of you, I belive I learned something from each post.

    From Darasen I learned the macro (thanks!)

    From meremyb I learned I couldent do what I was banging my head against the wall trying to accomplish!

    howey bless you! I never would have goten this if you hadent said to put the text box in, I did what the others said and it still wasent working. Your text box plan let me see I had bound to the wrong column and wasent sending the query the right data!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •