Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo's in queries (A2K ?)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo's in queries (A2K ?)

    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.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo's in queries (A2K ?)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo's in queries (A2K ?)

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

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo's in queries (A2K ?)

    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.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo's in queries (A2K ?)

    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
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo's in queries (A2K ?)

    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.)
    Attached Files Attached Files
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo's in queries (A2K ?)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo's in queries (A2K ?)

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

Posting Permissions

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