• Combo’s in queries (A2K ?)

    Author
    Topic
    #359814

    Hi,

    I have written a simple query for a tariff, the product names are quite long winded and it is easy to miss type the names.

    Is it possible to have the question box as a combo so that the user can select from a list rather than typing.

    Appreciate any advice.

    Best regards

    Steve

    Viewing 0 reply threads
    Author
    Replies
    • #540371

      Yes, it is. The basics are as follows:
      1 – Create a form with the combo that displays the various choices.
      2 – In your query, reference the combo choice as the criteria to select the correct tariff.
      3 – Create a button on the form that runs the query.
      We frequently use this approach for dealing with complex choices.
      Good luck.

      • #540443

        Hi

        Thanks for the response, but probably due to my inexperience I am having trouble with this, I raised the form and simply called it frmGoods with a combo box cboGoods. In the SQL query however I cannot seem to get it to reference to this combo, I have tried several things, and the latest is below

        SELECT frmGoods.cboGoods, tblRates.Origin, tblRates.Destination, tblRates.Rate
        FROM frmGoods, tblRates
        WHERE ((((frmGoods.cboGoods)=[Please Select Goods]) AND (((tblRates.Origin)=[Origin of Goods]) AND ((tblRates.Destination)=[Destination of Goods]));

        Any pointers greatly appreciated

        TIA

        Steve

        • #540445

          why not do a select query and set the field criteria to the forms combobox

        • #540446

          If the form is named MyForm, and the combo is named MyCombo, all you need to do is click in the criteria box of the tarrif name field and type the following:

          Forms!MyForm!MyCombo

          That will set the criteria equal to the value you have selected in the combo box.

          The SQL you sent me suggests you are trying to run a query against the form and that doesn’t work. In addition, your [Please Select Goods] makes the query a parameter query and causes a prompt to be displayed. What you want to do is let the form and the combo box serve as your prompt. If you are a novice at this, I would strongly suggest using the Query Tool. The grid is pretty intuitive, and you don’t get tangled up in SQL at all. The thing to remember is that queries can only pull data from tables or other queries.

          • #540877

            Thanks for your help so far, I have spent the last fee days trying everything to get this to work but can’t seem to grasp it, if you have a moment would you kindly look at the attachment and tell me where I have gone wrong.

            TIA

            Steve

            • #540882

              You were pretty close – but your combo box contained the number associated with the item rather than the name, while you query contained the item name. I changed the combo box to just display the Item Name, and made it single column, though you want to do the reverse, and change the query to include the item number. Actually, it’s your table for tariffs that contains the Item Name, and you may want to change it to include the item number, and then do a join to the Item table when you want to display the name.

              I added a button to run the query, and made it look something like a dialog box. This should get you going.
              (The modified file is attached as a ZIP file.)

            • #541115

              Thanks a milion Wendell

              I had missed the point about the command button, I assumed that just making the box a combo would trigger the query when I selected on of the items.

              Cheers

              Steve

            • #541208

              you could put code in the onclick of the combo box
              to do the same as the command button
              it would save a click

    Viewing 0 reply threads
    Reply To: Combo’s in queries (A2K ?)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: