Lost - Please help with a query

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • deaconj999

    #1

    Lost - Please help with a query

    Hi,

    I have nearly finished my database and I would like to add a query
    that uses a combo box to get the results, not the usual paramater
    style input. I suppose it would need a form and a query and a combo
    box, but where to start !!!!!!!!

    Any kind soul out there that can give me an example of where to
    start.......... ?

    Many Thanks

    Joe

  • fredg

    #2
    Re: Lost - Please help with a query

    On 6 Apr 2007 14:47:34 -0700, deaconj999 wrote:
    Hi,
    >
    I have nearly finished my database and I would like to add a query
    that uses a combo box to get the results, not the usual paramater
    style input. I suppose it would need a form and a query and a combo
    box, but where to start !!!!!!!!
    >
    Any kind soul out there that can give me an example of where to
    start.......... ?
    >
    Many Thanks
    >
    Joe
    Is the query to be viewed by itself, or is it used as the recordsource
    for a report.
    Different methods.
    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail

    Comment

    • Don Leverton

      #3
      Re: Lost - Please help with a query

      Hi,

      What you really meant to say was "use a combo-box to specify criteria"
      instead of being prompted for it as per the usual (eg [Enter a nuber between
      1 and 6] )parameter query, right?

      My suggestion,( if this is what you want) is to design an unbound main form
      to put your combo-box on, and a datasheet style subform that will be used to
      display the results.

      Use the combo-boxes AfterUpdate to build your SQL statement (query) in code,
      and then use the resulting SQL as the RecordSource for the subform.
      Here is a code sample:
      =============== ========

      Public Sub sRequerySubform ()
      'What I want to do here is build the RecordSource for the subform
      'ON-THE-FLY...to allow for a variable WHERE statement.
      'IF the user wants to view:
      ' 1.) ALL records
      ' The combo-box also has an "All" option. Instructions at


      ' 2.) filtered by Line code (a 3-character brand abbreviation)

      Dim MySQL As String
      Dim RptSQL As String
      Dim whr As String

      Dim MyLine
      MyLine = Me![cboLine]

      'Build the SELECT portion of the SQL statement
      MySQL = ""
      MySQL = MySQL & "SELECT tblPartsInvento ry.* FROM tblPartsInvento ry "

      'Build the WHERE portion
      whr = "" 'initialize variable

      If Not IsNull(MyLine) And MyLine <"(All)" Then
      whr = whr & "(tblPartsInven tory.Line = '"
      whr = whr & MyLine
      whr = whr & "' )"
      MySQL = MySQL & "WHERE ("
      MySQL = MySQL & whr
      MySQL = MySQL & ") "
      End If

      'The report will use the same SQL except it needs this line added.
      RptSQL = MySQL & "And (tblPartsInvent ory.Selected = True)" & ";"

      MySQL = MySQL & "ORDER BY tblPartsInvento ry.Line,
      tblPartsInvento ry.PartNumber "
      MySQL = MySQL & ";"

      'Debug.Print "MySQL: " & MySQL
      'Debug.Print "-------------------------" & CR
      'Debug.Print "RptSQL: " & RptSQL
      Me![sbfBinLabelSele ction].Form.RecordSou rce = MySQL

      End Sub

      =============== ===

      --
      --
      HTH,
      Don
      =============== ==============
      E-Mail (if you must) My.Name@Telus.n et

      Disclaimer:
      Professional PartsPerson
      Amateur Database Programmer {:o)

      I'm an Access97 user, so all posted code samples are also Access97- based
      unless otherwise noted.

      =============== =============== =============== =============== ==============


      "deaconj999 " <deaconj999@bti nternet.comwrot e in message
      news:1175896054 .361055.117520@ y66g2000hsf.goo glegroups.com.. .
      Hi,
      >
      I have nearly finished my database and I would like to add a query
      that uses a combo box to get the results, not the usual paramater
      style input. I suppose it would need a form and a query and a combo
      box, but where to start !!!!!!!!
      >
      Any kind soul out there that can give me an example of where to
      start.......... ?
      >
      Many Thanks
      >
      Joe
      >

      Comment

      • scartnal@alltel.net

        #4
        Re: Lost - Please help with a query

        On Apr 6, 5:47 pm, "deaconj999 " <deaconj...@bti nternet.comwrot e:
        Hi,
        >
        I have nearly finished my database and I would like to add a query
        that uses a combo box to get the results, not the usual paramater
        style input. I suppose it would need a form and a query and a combo
        box, but where to start !!!!!!!!
        >
        Any kind soul out there that can give me an example of where to
        start.......... ?
        >
        Many Thanks
        >
        Joe
        Joe,

        1. Create a query to "feed" the combo box, including only the fields
        you want to appear in the combo box.

        2. Create an unbound form or use an existing form, depending on from
        where you want the user to conduct the search.

        3. Put a combo box on your form, using your query from step 1 as the
        row source. (I like to use the wizard for combo boxes. Click the
        wizard button on the tool bar before you place the combo box on your
        form, and then just answer the questions to set up the combo box.)
        Give the combo box a name or remember the one Access gave it.

        4. Decide how you want the user to initiate the search. I would
        suggest a couple of buttons on the form; one thats says "Search" and
        one that says "Cancel".

        5. Create another query for your search results. In the criteria blank
        for the field in this query that matches the combo box type: Like
        "[Forms]![Name of Your Form]![Name of your combo box]". (If the field
        is numeric or date/time, as opposed to text, use = instead of Like.)

        6. Create macrosnfor the buttons. If you choose "Macro Names" from the
        View menu, you can place both of the macros is the same macro group.
        For the "Search" macro, merely use an OpenQuery command to open the
        search result query. For the "Cancel" macro, use a Close command on
        the search form.

        7. If you would rather the user view the search results in a form or
        report instead of a query, create one, using the query as the control
        source.

        That's the simplest way I know of to do it and it doesn't involve any
        knowledge of VBA.

        Good luck.

        Steve

        Comment

        • deaconj999

          #5
          Re: Lost - Please help with a query

          On Apr 7, 4:53 am, scart...@alltel .net wrote:
          On Apr 6, 5:47 pm, "deaconj999 " <deaconj...@bti nternet.comwrot e:
          >
          Hi,
          >
          I have nearly finished my database and I would like to add a query
          that uses a combo box to get the results, not the usual paramater
          style input. I suppose it would need a form and a query and a combo
          box, but where to start !!!!!!!!
          >
          Any kind soul out there that can give me an example of where to
          start.......... ?
          >
          Many Thanks
          >
          Joe
          >
          Joe,
          >
          1. Create a query to "feed" the combo box, including only the fields
          you want to appear in the combo box.
          >
          2. Create an unbound form or use an existing form, depending on from
          where you want the user to conduct the search.
          >
          3. Put a combo box on your form, using your query from step 1 as the
          row source. (I like to use the wizard for combo boxes. Click the
          wizard button on the tool bar before you place the combo box on your
          form, and then just answer the questions to set up the combo box.)
          Give the combo box a name or remember the one Access gave it.
          >
          4. Decide how you want the user to initiate the search. I would
          suggest a couple of buttons on the form; one thats says "Search" and
          one that says "Cancel".
          >
          5. Create another query for your search results. In the criteria blank
          for the field in this query that matches the combo box type: Like
          "[Forms]![Name of Your Form]![Name of your combo box]". (If the field
          is numeric or date/time, as opposed to text, use = instead of Like.)
          >
          6. Create macrosnfor the buttons. If you choose "Macro Names" from the
          View menu, you can place both of the macros is the same macro group.
          For the "Search" macro, merely use an OpenQuery command to open the
          search result query. For the "Cancel" macro, use a Close command on
          the search form.
          >
          7. If you would rather the user view the search results in a form or
          report instead of a query, create one, using the query as the control
          source.
          >
          That's the simplest way I know of to do it and it doesn't involve any
          knowledge of VBA.
          >
          Good luck.
          >
          Steve
          Many thanks

          Comment

          • engles@ridesoft.com

            #6
            Re: Lost - Please help with a query

            On Apr 7, 1:47 am, "deaconj999 " <deaconj...@bti nternet.comwrot e:
            On Apr 7, 4:53 am, scart...@alltel .net wrote:
            >
            >
            >
            >
            >
            On Apr 6, 5:47 pm, "deaconj999 " <deaconj...@bti nternet.comwrot e:
            >
            Hi,
            >
            I have nearly finished my database and I would like to add a query
            that uses a combo box to get the results, not the usual paramater
            style input. I suppose it would need a form and a query and a combo
            box, but where to start !!!!!!!!
            >
            Any kind soul out there that can give me an example of where to
            start.......... ?
            >
            Many Thanks
            >
            Joe
            >
            Joe,
            >
            1. Create a query to "feed" the combo box, including only the fields
            you want to appear in the combo box.
            >
            2. Create an unbound form or use an existing form, depending on from
            where you want the user to conduct the search.
            >
            3. Put a combo box on your form, using your query from step 1 as the
            row source. (I like to use the wizard for combo boxes. Click the
            wizard button on the tool bar before you place the combo box on your
            form, and then just answer the questions to set up the combo box.)
            Give the combo box a name or remember the one Access gave it.
            >
            4. Decide how you want the user to initiate the search. I would
            suggest a couple of buttons on the form; one thats says "Search" and
            one that says "Cancel".
            >
            5. Create another query for your search results. In the criteria blank
            for the field in this query that matches the combo box type: Like
            "[Forms]![Name of Your Form]![Name of your combo box]". (If the field
            is numeric or date/time, as opposed to text, use = instead of Like.)
            >
            6. Create macrosnfor the buttons. If you choose "Macro Names" from the
            View menu, you can place both of the macros is the same macro group.
            For the "Search" macro, merely use an OpenQuery command to open the
            search result query. For the "Cancel" macro, use a Close command on
            the search form.
            >
            7. If you would rather the user view the search results in a form or
            report instead of a query, create one, using the query as the control
            source.
            >
            That's the simplest way I know of to do it and it doesn't involve any
            knowledge of VBA.
            >
            Good luck.
            >
            Steve
            >
            Many thanks- Hide quoted text -
            >
            - Show quoted text -
            I'll toss in one more approach. I'm assuming that all you need is a
            different filter on the report (the usual). If so, then use the combo
            box to get what it is to be reported on. You can then use the filter
            on the report open code to have the report show what is desired. For
            example

            docmd.openrepor t "MyReport", ,,, "Key = " & me.comboBox

            I don't remember off-hand how many commas are needed here, but VBA
            will show you the arguments once you start typing.

            -- Larry Engles

            Comment

            Working...