Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Virginia
    Posts
    16

    Question Unanswered: How do I select a variable from a list in a query?

    I have a list of names, approx. 2000. Right now I have a variable field [holder] that has to be populated with a name so the query can sort the records using the name entered into [holder]. Because it is very important that the name be EXACTLY entered as it is in the table I'm using as a lookup table containing all 200 names, is there anyway I can turn that pop up box asking for variable [holder] into a combo box tied to the field containing all of the names. Upon selecting the name it populates [holder] with the selected name.

    I hope this all makes sense.

    I greatly appreciate any help.

  2. #2
    Join Date
    Jan 2003
    Posts
    46

    combo box answer

    Tinlemon

    THe short answer is yes.

    You have created a form for the user interface (if your head is not nodding up and down, you need to do so). Essentailly you use your own form in place of the pop-up. Place the combo box on the form and then reference it in the query as the query's criteria.

    Open the query in design view and in the appropriate field add something similiar to the following as the criteria

    Forms!YourFormName!YourComboBoxName

    Cheers,
    zambezibill

  3. #3
    Join Date
    Oct 2002
    Location
    Virginia
    Posts
    16
    Perfect!! Short answer was all I needed!!! You da bomb!

  4. #4
    Join Date
    Feb 2004
    Posts
    42

    Re: combo box answer

    I think I get it, but its not working for me.

    I have a form with nothing on it, but a list box (I'm using a list box instead of a combo box). This list box goes and gets all the possible records for me, so I can just select the values I want the query to run by. I then have my query set up with:
    [Forms]![Select_Status]![StatusList]
    in the criteria. How do I get the form to pop up automatically?

    I suppose I'll be able to put a close button on it to close it when I'm done selecting the values.

    Thanks

  5. #5
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Well in general the form containing the criteria stays open the whole time (before, during, and after) the query is run.

    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown. And to counter that you might consider creating the query's SQL statement dynamically so that the criteria on a particular field isn't used unless needed.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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